PO Induction - CORESVC_PO.PROCESS_ORE Cursor C_SVC_ORE Performance (Doc ID 2970075.1)

Last updated on AUGUST 24, 2023

Applies to:

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

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