EPY: Budget Retro Distribution (HP_BGTRETDST ) performance issue (Doc ID 1560132.1)

Last updated on JULY 21, 2023

Applies to:

Goal

Issue #1:
Performance Issue when running the Budget Retro job (HP_BGTRETDST) for 200-400 positions over multiple fiscal years. The process ran for 12 days.

Issue #2:
There is no where clause in the Insert statement -

INSERT INTO PS_HP_BGTACTUAL_WK (HP_KK_DOC_ID ,HP_KK_DOC_LINE_NBR ,FISCAL_YEAR ,HP_BUDGET_YEAR ,EMPLID ,EMPL_RCD ,POSITION_NBR ,HRS_JOB_OPENING_ID ,POSITION_POOL_ID ,SETID_JOBCODE ,JOBCODE ,SETID ,DEPTID ,ENCUMB_PROC_TYPE ,COMPANY ,PAYGROUP ,BUSINESS_UNIT_GL ,ACCT_CD ,ACCOUNT ,POSTED_ACTUAL_AMT ,POSTED_NE_ACT_AMT ,POSTED_ENCUMB_AMT ,ANNUAL_ENCUMB_AMT ,WORK_ACTUAL_AMT ,WORK_ENCUMB_AMT ,HP_ENC_DATE) SELECT HP_KK_DOC_ID ,HP_KK_DOC_LINE_NBR ,FISCAL_YEAR ,HP_BUDGET_YEAR ,EMPLID ,EMPL_RCD ,POSITION_NBR ,HRS_JOB_OPENING_ID ,POSITION_POOL_ID ,SETID_JOBCODE ,JOBCODE ,SETID ,DEPTID ,ENCUMB_PROC_TYPE ,COMPANY ,PAYGROUP ,BUSINESS_UNIT_GL ,ACCT_CD ,ACCOUNT ,POSTED_ACTUAL_AMT ,POSTED_NE_ACT_AMT ,POSTED_ENCUMB_AMT ,ANNUAL_ENCUMB_AMT ,WORK_ACTUAL_AMT ,WORK_ENCUMB_AMT ,HP_ENC_DATE FROM PS_BUDGET_ACTUALS 

The initial insert is all rows from PS_BUDGET_ACTUALS, 3.6 million rows.

Why doesn't this insert statement use a WHERE clause for filtering based on Run Control parameters?
The UPDATE statement does use the Run Control parameters such as Dept ID and Fiscal Year.

The Insert and Delete statements take over 100 sec.

Solution

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