URGENT: WFBG Perf, 2 sqls causing issue - SELECT tab from WF_Deferred
SR#: 3-1915220381
Customer: Starbucks Corporation
URGENT: PROD Critical, cannot send invoices to customers
Oracle Apps Version: 11.5.10.2
Problem Description/Question:
POOR WORKFLOW BACKGROUND PERFORMANCE. WFBG is not able to process defferred
activities fast enought to catch up with the daily volume.
Customer processes approx 1,200,000 order lines per day. Workflow background is not able to close
all these lines from deferred queue in a day because of poor performance and hence queue keep
building. As lines are not closed AR invoices are not issues to customers and hence loss of revenue.
Did some analysis and found two sqls causing the issue
1. SELECT tab.ROWID,
tab.msgid,
tab.corrid,
tab.priority,
tab.delay,
tab.expiration,
tab.retry_count,
tab.exception_qschema,
tab.exception_queue,
tab.chain_no,
tab.local_order_no,
tab.enq_time,
tab.time_manager_info,
tab.state,
tab.enq_tid,
tab.step_no,
tab.sender_name,
tab.sender_address,
tab.sender_protocol,
tab.dequeue_msgid,
tab.user_prop,
tab.user_data
FROM "APPLSYS"."WF_DEFERRED_TABLE_M" tab
WHERE q_name = :1 AND msgid = :2
This sql is doing full scan of queue table and as there is huge unprocessed data in queue this sql takes very
long to execute.
We create a temporary index on msgid and this sigficantly improved the perofrmance.
2.
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 = TO_CHAR (:B1)
AND SALES_ORDER_LINE IS NOT NULL
This sql is doing full scan and same issue here as volume is huge this takes a while to execute.
Created a temp index on sales_order and it drastically improved the performance.
With the above two changes we are able to process approx 70K records per hour instead of just few hundred.
Want oracle to review these changes and suggest if we can get a patch from oracle. If not are can we just c
reate these two indexes in production???
Observe: Customer is running WFBG process by item type and only for deferred activities as a separate request.
Please advise,
Suzzi Hurd