PO Induction - CORESVC_PO.PROCESS_ORE Cursor C_SVC_ORE Performance
(Doc ID 2970075.1)
Last updated on AUGUST 24, 2023
Applies to:
Oracle Retail Merchandising System - Version 16.0.3 and laterInformation in this document applies to any platform.
Symptoms
ACTUAL BEHAVIOR :
---------------
PO Induction CORESVC_PO.PROCESS_ORE cursor C_SVC_ORE is having performance related issues. The perfromance issue seems to be related with the join between svc_ordloc_exp and ordloc_exp.
" and (st.pack_item = uk_ordloc_exp.pack_item or st.pack_item is NULL and uk_ordloc_exp.pack_item is NULL)" --- it is resulting in 123 thousand buffer gets per execution in average.
We did some explain plans comparisons with the following change to the same line:
" and nvl(st.pack_item, '-999') = nvl(uk_ordloc_exp.pack_item, '-999')"
and we got a significant reduction on buffer gets, i'm sharing both explain plans for better analysis.
EXPECTED BEHAVIOR :
-----------------------
PO Induction CORESVC_PO.PROCESS_ORE cursor C_SVC_ORE should not face performance related issues.
STEPS TO REPRODUCE :
-----------------------
The issue can be reproduced at will with the following steps:
1. Proceed with the PO induction process.
2. Observe 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 |