The MerchExtractKickOffBatch.sh Takes a Long Time to Finish Execution (Doc ID 2478225.1)

Last updated on MARCH 05, 2019

Applies to:

Symptoms

 
MERCHEXTRACTKICKOFFBATCH.SH takes long to finish execution. It usually takes more than 4 hours. As soon as the job is started it starts 900+ Database sessions with query

756qqaz4fwnq1 (INSERT /*+ APPEND* / INTO RPM_PRE_ME_ITEM_LOC (ME_SEQUENCE_ID, ME_AREA_DIFF_SEQ_ID, ITEM, LOC, LOC_TYPE, STATUS, RPM_IND, STOCK_ON_HAND, PACK_COMP_SOH, IN_TRANSIT_QTY, PACK_COMP_INTRAN, FIRST_RECEIVED, LAST_RECEIVED, FIRST_SOLD, PRIMARY_SUPP, PRIMARY_CNTRY, CURRENCY_CODE, DEPT, ITEM_PARENT, DIFF_ID, ZONE_ID) SELECT T.ME_SEQUENCE_ID, T.ME_AREA_DIFF_SEQ_ID, T.ITEM, T.LOC, T.LOC_TYPE,T.STATUS, T.RPM_IND, T.STOCK_ON_HAND, T.PACK_COMP_SOH, T.IN_TRANSIT_QTY, T.PACK_COMP_INTRAN, T.FIRST_RECEIVED, T.LAST_RECEIVED,T.FIRST_SOLD, T.PRIMARY_SUPP, T.PRIMARY_CNTRY, T.CURRENCY_CODE,T.DEPT, T.ITEM_PARENT, T.DIFF_1, FR.ZONE_ID FROM (SELECT /*+ PARALLEL(ils) PARALLEL(il) ORDERED USE_HASH(il) USE_HASH(ils) USE_HASH(im) */ STR.ME_SEQUENCE_ID, STR.ME_AREA_DIFF_SEQ_ID, ILS.ITEM, ILS.LOC, ILS.LOC_TYPE, IL.STATUS, IL.RPM_IND, ILS.STOCK_ON_HAND, ILS.PACK_COMP_SOH, ILS.IN_TRANSIT_QTY, ILS.PACK_COMP_INTRAN,ILS.FIRST_RECEIVED, ILS.LAST_RECEIVED, ILS.FIRST_SOLD, ILS.PRIMARY_SUPP, ILS.PRIMARY_CNTRY, NVL(S.CURRENCY_CODE, Z.CURRENCY_COD)

Furthermore, it hangs there.
Database session wait event : Execution Msg sleep time/senderid

Steps to Reproduce
-----------------------
The issue can be reproduced at will with the following steps:
1. Run the MERCHEXTRACTKICKOFFBATCH.SH.

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.