EAP: Pay Cycle Performance Issue at Step AP_WTHDCALC.PRE_PROC.PRE_P3A2 (Doc ID 2891980.1)

Last updated on NOVEMBER 18, 2022

Applies to:

Symptoms

Pay Cycle is encountering performance issue at step AP_WTHDCALC.PRE_PROC.PRE_P3A2.

AWR provided from customer shows as below:

Finding 1: Top SQL Statements
Impact is .39 active sessions, 47.84% of total activity.
--------------------------------------------------------
SQL statements consuming significant database time were found. These statements offer a good opportunity for performance improvement.

   Recommendation 1: SQL Tuning
   Estimated benefit is .21 active sessions, 25.58% of total activity.
   -------------------------------------------------------------------
   Action
      Run SQL Tuning Advisor on the INSERT statement with SQL_ID
      "696d7tq9ktay8".
      Related Object
         SQL statement with SQL_ID 696d7tq9ktay8.
         INSERT INTO PS_WTHD_CALC3_TAO (PROCESS_INSTANCE, BUSINESS_UNIT,
         VOUCHER_ID, PYMNT_CNT, VOUCHER_LINE_NUM, WTHD_ENTITY, WTHD_TYPE,
         WTHD_JUR_CD, WTHD_CLASS, OVERRIDE_FLAG, PYMNT_GROSS_AMT,
         WTHD_ADDTL_GROSS, GROSS_AMT_BSE, DUE_DT, DSCNT_DUE_DT,
         SCHEDULED_PAY_DT, CURRENCY_PYMNT, PYMNT_SELCT_STATUS, REMIT_SETID,
         REMIT_VENDOR, VNDR_LOC, REMIT_ADDR_SEQ_NUM, BANK_SETID, BANK_CD,
         BANK_ACCT_KEY, PYMNT_ACTION, PYMNT_METHOD, PYMNT_SEPARATE,
         PYMNT_HANDLING_CD, PYMNT_HOLD, PYMNT_HOLD_REASON, REMIT_BANK_SETID,
         REMIT_BANK_CD, REMIT_BNK_ACCT_KEY, PAY_CYCLE, PAY_CYCLE_SEQ_NUM,
         ORIG_PROC_INSTANCE, TXN_CURRENCY_CD, DECIMAL_POS_TXN, HOL_PROC_OPT,
         HOL_PROC_DAYS, HOL_PROC_OVERFL, PREPAID_STATUS, BUSINESS_UNIT_GL,
         GROSS_AMT, RT_TYPE, INVOICE_DT, VENDOR_SETID, VENDOR_ID,
         VCHR_VNDR_LOC, VCHR_ADDR_SEQ_NUM, WTHD_BASIS_AMT, WTHD_RULE,
         VAT_RECALC_FLG, BSE_RATE_MULT, BSE_RATE_DIV, BASE_CURRENCY,
         DECIMAL_POS_BSE, LEDGER, LEDGER_GROUP, SETID, CUR_RT_TYPE,
         CURRENCY_CD, DECIMAL_POS_ENT, ENT_VENDOR_SETID, ENT_VENDOR_ID,
         ENT_VNDR_LOC, ENT_ADDR_SEQ_NUM, ENTITY_HDR_EFFDT, APPLY_BALANCE,
         NETAMT_APPL_FLG, FREIGHT_APPL_FLG, MISC_CHG_APPL_FLG,
         WHTD_DSCNT_APPL, WTHD_SUT_APPL, WTHD_VAT_FLG, EXCISE_APPL_FLG,
         ENT_RATE_MULT, ENT_RATE_DIV, WTHD_ROUND_OPT, PP_GROSS_AMT,
         WTHD_CONT_REF, ACCOUNTING_DT, WTHD_APPL_FLG, PAID_RATE_MULT,
         PAID_RATE_DIV) SELECT T.PROCESS_INSTANCE, T.BUSINESS_UNIT,
         T.VOUCHER_ID, T.PYMNT_CNT, T.VOUCHER_LINE_NUM, T.WTHD_ENTITY,
         T.WTHD_TYPE, T.WTHD_JUR_CD, T.WTHD_CLASS, T.OVERRIDE_FLAG,
         T.PYMNT_GROSS_AMT, T.WTHD_ADDTL_GROSS, T.GROSS_AMT_BSE, T.DUE_DT,
         T.DSCNT_DUE_DT, T.SCHEDULED_PAY_DT, T.CURRENCY_PYMNT,
         T.PYMNT_SELCT_STATUS, T.REMIT_SETID, T.REMIT_VENDOR, T.VNDR_LOC,
         T.REMIT_ADDR_SEQ_NUM, T.BANK_SETID, T.BANK_CD, T.BANK_ACCT_KEY,
         T.PYMNT_ACTION, T.PYMNT_METHOD, T.PYMNT_SEPARATE,
         T.PYMNT_HANDLING_CD, T.PYMNT_HOLD, T.PYMNT_HOLD_REASON,
         T.REMIT_BANK_SETID, T.REMIT_BANK_CD, T.REMIT_BNK_ACCT_KEY,
         T.PAY_CYCLE, T.PAY_CYCLE_SEQ_NUM, T.ORIG_PROC_INSTANCE,
         T.TXN_CURRENCY_CD, T.DECIMAL_POS_TXN, T.HOL_PROC_OPT,
         T.HOL_PROC_DAYS, T.HOL_PROC_OVERFL, T.PREPAID_STATUS,
         T.BUSINESS_UNIT_GL, T.GROSS_AMT, T.RT_TYPE, T.INVOICE_DT,
         T.VENDOR_SETID, T.VENDOR_ID, T.VCHR_VNDR_LOC, T.VCHR_ADDR_SEQ_NUM,
         T.WTHD_BASIS_AMT, T.WTHD_RULE, T.VAT_RECALC_FLG, T.BSE_RATE_MULT,
         T.BSE_RATE_DIV, T.BASE_CURRENCY, T.DECIMAL_POS_BSE, T.LEDGER,
         T.LEDGER_GROUP, T.SETID, HDR.CUR_RT_TYPE, HDR.CURRENCY_CD,
         C.DECIMAL_POSITIONS, VND.VENDOR_SETID, VND.VENDOR_ID, VND.VNDR_LOC,
         VND.ADDRESS_SEQ_NUM, VND.EFFDT, DTL.APPLY_BALANCE,
         DTL.NETAMT_APPL_FLG, DTL.FREIGHT_APPL_FLG, DTL.MISC_CHG_APPL_FLG,
         DTL.WHTD_DSCNT_APPL, DTL.WTHD_SUT_APPL, DTL.WTHD_VAT_FLG,
         DTL.EXCISE_APPL_FLG, RTTE.RATE_MULT, RTTE.RATE_DIV,
         HDR.WTHD_ROUND_OPT, T.PP_GROSS_AMT, T.WTHD_CONT_REF, T.ACCOUNTING_DT,
         T.WTHD_APPL_FLG, RTTE2.RATE_MULT, RTTE2.RATE_DIV FROM
         PS_WTHD_CALC2_TAO T, PS_WTHD_ENTITY_HDR HDR, PS_WTHD_ENTITY_DTL DTL,
         PS_RT_DFLT_VW RTTE, PS_RT_DFLT_VW RTTE2, PS_CURRENCY_CD_TBL C,
         PS_WTHD_ENTITY_VND VND WHERE T.PROCESS_INSTANCE = 2636217 AND
         T.WTHD_APPL_FLG = 'P' AND ((T.CURRENCY_PYMNT <> T.BASE_CURRENCY) OR
         (T.BASE_CURRENCY <> HDR.CURRENCY_CD) OR (T.TXN_CURRENCY_CD <>
         HDR.CURRENCY_CD)) AND T.PAID_RATE_DIV = 0 AND HDR.WTHD_ENTITY =
         T.WTHD_ENTITY AND HDR.EFF_STATUS = 'A' AND HDR.EFFDT = ( SELECT
         MAX(H.EFFDT) FROM PS_WTHD_ENTITY_HDR H WHERE H.WTHD_ENTITY =
         T.WTHD_ENTITY AND H.EFFDT <= T.ACCOUNTING_DT) AND DTL.WTHD_ENTITY =
         HDR.WTHD_ENTITY AND DTL.EFFDT = HDR.EFFDT AND DTL.WTHD_TYPE =
         T.WTHD_TYPE AND DTL.WTHD_JUR_CD = T.WTHD_JUR_CD AND DTL.WTHD_CLASS =
         T.WTHD_CLASS AND RTTE.FROM_CUR = T.TXN_CURRENCY_CD AND RTTE.TO_CUR =
         HDR.CURRENCY_CD AND RTTE.RT_TYPE = HDR.CUR_RT_TYPE AND RTTE.EFFDT = (
         SELECT MAX(R.EFFDT) FROM PS_RT_DFLT_VW R WHERE R.FROM_CUR =
         RTTE.FROM_CUR AND R.TO_CUR = RTTE.TO_CUR AND R.RT_TYPE = RTTE.RT_TYPE
         AND ((' ' <> 'Y' AND R.EFFDT <= TO_DATE('2022-07-26','YYYY-MM-DD'))
         OR (' ' = 'Y' AND R.EFFDT <= T.ACCOUNTING_DT))) AND RTTE2.FROM_CUR =
         T.TXN_CURRENCY_CD AND RTTE2.TO_CUR = T.CURRENCY_PYMNT AND
         RTTE2.RT_TYPE = HDR.CUR_RT_TYPE AND RTTE2.EFFDT = ( SELECT
         MAX(R1.EFFDT) FROM PS_RT_DFLT_VW R1 WHERE R1.FROM_CUR =
         RTTE2.FROM_CUR AND R1.TO_CUR = RTTE2.TO_CUR AND R1.RT_TYPE =
         RTTE2.RT_TYPE AND ((' ' <> 'Y' AND R1.EFFDT <=
         TO_DATE('2022-07-26','YYYY-MM-DD')) OR (' ' = 'Y' AND R1.EFFDT <=
         T.ACCOUNTING_DT))) AND C.CURRENCY_CD = HDR.CURRENCY_CD AND
         C.EFF_STATUS = 'A' AND C.EFFDT = ( SELECT MAX(S.EFFDT) FROM
         PS_CURRENCY_CD_TBL S WHERE S.CURRENCY_CD = C.CURRENCY_CD AND ((' ' <>
         'Y' AND S.EFFDT <= TO_DATE('2022-07-26','YYYY-MM-DD')) OR (' ' = 'Y'
         AND S.EFFDT <= T.ACCOUNTING_DT))) AND VND.WTHD_ENTITY = T.WTHD_ENTITY
         AND VND.EFFDT = ( SELECT MAX(VND2.EFFDT) FROM PS_WTHD_ENTITY_VND VND2
         WHERE VND2.WTHD_ENTITY = VND.WTHD_ENTITY AND ((' ' <> 'Y' AND
         VND2.EFFDT <= TO_DATE('2022-07-26','YYYY-MM-DD')) OR (' ' = 'Y' AND
         VND2.EFFDT <= T.ACCOUNTING_DT))) AND VND.VENDOR_SETID =
         T.VENDOR_SETID
   Rationale
      The SQL spent 98% of its database time on CPU, I/O and Cluster waits.
      This part of database time may be improved by the SQL Tuning Advisor.
   Rationale
      Database time for this SQL was divided as follows: 99% for SQL
      execution, 1% for parsing, 0% for PL/SQL execution and 0% for Java execution.

Step:
------------------------
1. Run Pay Cycle including withholding related vouchers.
    Navigation: Accounts Payable > Payments > Pay Cycle Processing > Pay Cycle Manager

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.