Workflow Background Engine Taking Lots Of System Resources

(Doc ID 1337496.1)

Last updated on JULY 13, 2016

Applies to:

Oracle Order Management - Version 12.0.6 and later
Information in this document applies to any platform.
***Checked for relevance on 14-Oct-2014***

Symptoms


The performance of the Workflow Background Process is unacceptable.

It is taking hours to complete and the business requirement is that this
should complete in minutes.

Tkprof provided shows the following costliest queries :

SELECT COUNT(1)
FROM
RA_CUSTOMER_TRX_LINES_ALL RCTL WHERE RCTL.INTERFACE_LINE_CONTEXT = 'ORDER
ENTRY' AND RCTL.LINE_TYPE = 'LINE' AND RCTL.INTERFACE_LINE_ATTRIBUTE6 =
TO_CHAR(:B2 ) AND RCTL.SALES_ORDER = :B1 AND RCTL.SALES_ORDER_LINE IS NOT
NULL


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 953 0.06 0.07 0 777 0
0
Fetch 953 3570.01 7310.27 140621857 142379153 0
953
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1907 3570.08 7310.35 140621857 142379930 0
953

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 SORT (AGGREGATE)
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'RA_CUSTOMER_TRX_LINES_ALL' (TABLE)
0 INDEX MODE: ANALYZED (RANGE SCAN) OF
'RA_CUSTOMER_TRX_LINES_N9' (INDEX)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 156963 0.02 2.37
db file scattered read 17922637 0.59
4757.14
latch: cache buffers chains 10 0.00 0.00
latch free 14 0.05 0.05
latch: object queue header operation 10 0.00 0.00
*****************************************************************************

SELECT COUNT(1)
FROM
RA_INTERFACE_LINES_ALL WHERE LINE_TYPE = 'LINE' AND INTERFACE_LINE_CONTEXT =
'ORDER ENTRY' AND INTERFACE_LINE_ATTRIBUTE6 = TO_CHAR(:B2 ) AND SALES_ORDER
= :B1 AND SALES_ORDER_LINE IS NOT NULL


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 1 0.00 0.00 0 0 0
0
Execute 954 0.03 0.04 0 576 0
0
Fetch 954 2127.76 2089.19 71029464 484847363 0
953
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1909 2127.80 2089.23 71029464 484847939 0
953

Misses in library cache during parse: 1
Misses in library cache during execute: 3
Optimizer mode: ALL_ROWS
Parsing user id: 173 (APPS) (recursive depth: 2)

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 SORT (AGGREGATE)
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'RA_INTERFACE_LINES_ALL' (TABLE)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file scattered read 9354784 0.26 484.60
db file sequential read 1124902 0.07 12.50
latch free 20 0.01 0.02
latch: object queue header operation 6 0.00 0.00
read by other session 18382 0.00 0.56
latch: cache buffers chains 17 0.06 0.08
*****************************************************************************

begin OE_INVOICE_WF.INVOICE_INTERFACE (:v1, :v2, :v3, :v4, :v5); end;


call count cpu elapsed disk query current
rows
------- ------ -------- ---------- ---------- ---------- ----------
----------
Parse 954 0.01 0.01 0 0 0
0
Execute 954 25.41 555.86 1564861 21257231 297
953
Fetch 0 0.00 0.00 0 0 0
0
------- ------ -------- ---------- ---------- ---------- ----------
----------
total 1908 25.43 555.87 1564861 21257231 297
953

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