Performance Issue With CSF_DEBRIEF_PUB.CREATE_DEBRIEF API (Doc ID 1372370.1)

Last updated on SEPTEMBER 06, 2016

Applies to:

Oracle Field Service - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version, Debrief / Field Service Report - there is a performance issue with the CSF_DEBRIEF_PUB.CREATE_DEBRIEF API.
Whenever this API is called, it consumes 100 % resource and whole system becomes very slow.
Its taking close to 2 minutes just to create 1 debrief record.

Recent Changes
--------------------
Upgrade from 11i to R12.1.3

The trace displays the following :

SELECT MIN(LABOR_START_DATE),
              MAX(LABOR_END_DATE), MIN(DEBRIEF_LINE_ID)
FROM    CSF_DEBRIEF_LAB_LINES_V CDL,
              CSF_DEBRIEF_HEADERS CDH, JTF_TASK_ASSIGNMENTS  JTA,                    
              CS_TRANSACTION_TYPES CTT
WHERE JTA.TASK_ID = :B1 AND
              JTA.TASK_ASSIGNMENT_ID = CDH.TASK_ASSIGNMENT_ID AND               
              CDH.DEBRIEF_HEADER_ID = CDL.DEBRIEF_HEADER_ID AND 
              CDL.TRANSACTION_TYPE_ID = CTT.TRANSACTION_TYPE_ID AND
              CTT.LINE_ORDER_CATEGORY_CODE = 'ORDER'

call    count   cpu     elapsed     disk    query   current  rows
------- ------ -------- -------- ------- ---------- -------- ------
Parse   1       0.00     0.00       0        0         0      0
Execute 4       0.07     0.07       0        0         0      0
Fetch   4       212.78   243.78     912140   1286482   96     4
------- ------ -------- ---------- ------- -------- -------- ------
total   9       212.85   243.85     912140   1286482   96     4

 

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms