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:

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

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.