Need Performance Improvement For The High Resource Consuming Query In The Batch Salesprocess.ksh
(Doc ID 2970076.1)
Last updated on AUGUST 24, 2023
Applies to:
Oracle Retail Merchandising System - Version 14.1.3 and laterInformation in this document applies to any platform.
Symptoms
ACTUAL BEHAVIOR :
---------------
Need performance improvement for the high resource consuming query in the batch salesprocess.ksh.
Our DBA has identified a query consuming high resource in PRD. The query is :
SELECT 'x' FROM INVC_DETAIL ID WHERE EXISTS (SELECT 1 FROM
SVC_POSUPLD_CONS_HELPER_GTT PCH WHERE PCH.SALES_PROCESS_ID = :B2 AND
PCH.CHUNK_ID = :B1 AND PCH.EXISTING_INVC_IND = 'Y' AND PCH.INVC_ID =
ID.INVC_ID AND PCH.IM_ITEM = ID.ITEM AND PCH.TOTAL_UNIT_COST_LOC /
PCH.TOTAL_WASTAGE_QTY = ID.INVC_UNIT_COST AND ROWNUM = 1) FOR UPDATE OF
ID.INVC_QTY NOWAIT
This sql accounts for 10% or more of the daily CPU used on the RMS 14 system, whenever invoked.
The DBA has suggested to update this query to get rid of the “AND ROWNUM -1” in the end of sub query - with EXISTS clause, as oracle will automatically exit subquery when the first row is read, restricting it explicitly only confuses the optimizer.
Or alternatively re-write the the query as a 'IN' statement.
The query is referred in the base package -CORESVC_SALES_UPLOAD_PRST_SQL.LOCK_CONSIGNMENT_TABLES.
EXPECTED BEHAVIOR :
-----------------------
Batch salesprocess.ksh should not face performance related issues.
Changes
Cause
To view full details, sign in with your My Oracle Support account. |
|
Don't have a My Oracle Support account? Click to get started! |
In this Document
Symptoms |
Changes |
Cause |
Solution |
References |