Performance Issue With PO_RECVPUSH After Oracle12C Upgrade (Doc ID 2160764.1)

Last updated on OCTOBER 25, 2016

Applies to:

PeopleSoft Enterprise SCM Purchasing - Version 9.1 to 9.1 [Release 9]
Information in this document applies to any platform.

Symptoms

After upgrade from Oracle11g to 12C and facing performance issue with PO_RECVPUSH process. Users are executing RECV_00 Job and PO_RECVPUSH is not completed, it is failing below sql:

SELECT 'X'
FROM PS_INSTALLATION
WHERE EXISTS
(SELECT 'X'
FROM PS_PO_LINE_DISTRIB PD,
PS_RECV_LN_DISTRIB D,
PS_RECV_LN_SHIP S,
PS_RECV_PUSH_T1A A
WHERE A.PROCESS_INSTANCE = 4775772 AND A.RECEIVER_ID = S.RECEIVER_ID
AND A.RECV_LN_NBR = S.RECV_LN_NBR AND A.RECV_SHIP_SEQ_NBR = S.RECV_SHIP_SEQ_NBR AND S.RECV_SHIP_STATUS NOT IN ('C', 'X')
AND S.PROCESS_COMPLETE = 'N' AND A.BUSINESS_UNIT = D.BUSINESS_UNIT AND A.RECEIVER_ID = D.RECEIVER_ID
AND A.RECV_LN_NBR = D.RECV_LN_NBR AND A.RECV_SHIP_SEQ_NBR = D.RECV_SHIP_SEQ_NBR
AND D.RECV_DS_STATUS NOT IN ('C', 'X') AND D.QTY_DS_ACCPT_VUOM <> 0
AND D.BUSINESS_UNIT_PO = PD.BUSINESS_UNIT AND D.PO_ID = PD.PO_ID
AND D.LINE_NBR = PD.LINE_NBR AND D.SCHED_NBR = PD.SCHED_NBR
AND PD.DST_ACCT_TYPE = 'DST'
AND D.PO_DIST_LINE_NUM = PD.DISTRIB_LINE_NUM
AND PD.MERCHANDISE_AMT <> 0
AND PD.USETAX_AMT_BSE
+ PD.SALETX_AMT_BSE
+ ( PD.VAT_AMT_BASE
- PD.VAT_RCVRY_AMT_BSE
- PD.VAT_REBATE_AMT_BSE) <> 0)
Error :
File: /vob/peopletools/src/psappeng/aedebug.hSQL error. Stmt #: 1620 Error Position: 16 Return: 15 55 - ORA-01555: snapshot too old: rollback segment number 37 with name "_SYSSMU37_623530737$" too sm all Failed SQL stmt:SELECT 'X' FROM PS_INSTALLATION WHERE EXISTS ( SELECT 'X' FROM PS_PO_LIN

Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms