ETL92: PY_PULL_COST Performance Issue - TL_PUM_TM1.PUBLISH.Step030 (Doc ID 2815095.1)

Last updated on JULY 01, 2024

Applies to:

Symptoms

PY_PULL_COST AE program processing for a long time.
Long running SQL in first run.

INSERT INTO PS_WRK_PROJ2_TAO4 (PROCESS_INSTANCE, EMPLID, EMPL_RC
D, DUR, SEQ_NBR, TASKGROUP, SHIFT_ID, TRC, TL_QUANTITY, CURRENCY
_CD, COUNTRY, STATE, LOCALITY, COMP_RATECD, BILLABLE_IND, OVERRI
DE_RATE, EST_GROSS, LBR_DIST_AMT, DILUTED_GROSS, CURRENCY_CD2, P
AYABLE_STATUS, PAY_SYSTEM, PAYROLL_REQ_NUM, FROZEN_SW, FROZEN_DA
TE, RECORD_ONLY_ADJ, PUBLISH_SW, ACTUAL_PUB_DATE, OPRID, APPRV_P
RCS_DTTM, SOURCE_IND, ORIG_SEQ_NBR, COMPANY, BUSINESS_UNIT, SETI
D_LOCATION, LOCATION, SETID_DEPT, DEPTID, SETID_JOBCODE, JOBCODE
, POSITION_NBR, PRODUCT, CUSTOMER, ACCT_CD, BUSINESS_UNIT_PC, BU
SINESS_UNIT_PF, PROJECT_ID, SETID_ACTIVITY, ACTIVITY_ID, RESOURC
E_TYPE, SETID_RESOURCE, RESOURCE_CATEGORY, RESOURCE_SUB_CAT, TAS
K, USER_FIELD_1, USER_FIELD_2, USER_FIELD_3, USER_FIELD_4, USER_
FIELD_5, INITIAL_SEQ_NBR, DEPTID_CF, ACCOUNT, FUND_CODE, PROGRAM
_CODE, CLASS_FLD, AFFILIATE, OPERATING_UNIT, ALTACCT, BUDGET_REF
, CHARTFIELD1, CHARTFIELD2, CHARTFIELD3, AFFILIATE_INTRA1, AFFIL
IATE_INTRA2, TRC_TYPE, UNIT_OF_MEASURE, BUSINESS_UNIT_GL, TL_TIM
E_TO_PAY, MSGNODENAME) SELECT A.PROCESS_INSTANCE, A.EMPLID, A.EM
PL_RCD, A.DUR, A.SEQ_NBR, A.TASKGROUP, A.SHIFT_ID, A.TRC, A.TL_Q
UANTITY, A.CURRENCY_CD, A.COUNTRY, A.STATE, A.LOCALITY, A.COMP_R
ATECD, A.BILLABLE_IND, A.OVERRIDE_RATE, A.EST_GROSS, A.LBR_DIST_
AMT, A.DILUTED_GROSS, A.CURRENCY_CD2, A.PAYABLE_STATUS, A.PAY_SY
STEM, A.PAYROLL_REQ_NUM, A.FROZEN_SW, A.FROZEN_DATE, A.RECORD_ON
LY_ADJ, A.PUBLISH_SW, A.ACTUAL_PUB_DATE, A.OPRID, A.APPRV_PRCS_D
TTM, A.SOURCE_IND, A.ORIG_SEQ_NBR, A.COMPANY, A.BUSINESS_UNIT, A
.SETID_LOCATION, A.LOCATION, A.SETID_DEPT, A.DEPTID, A.SETID_JOB
CODE, A.JOBCODE, A.POSITION_NBR, B.PRODUCT, A.CUSTOMER, A.ACCT_C
D, A.BUSINESS_UNIT_PC, A.BUSINESS_UNIT_PF, B.PROJECT_ID, A.SETID
_ACTIVITY, A.ACTIVITY_ID, A.RESOURCE_TYPE, A.SETID_RESOURCE, A.R
ESOURCE_CATEGORY, A.RESOURCE_SUB_CAT, A.TASK, A.USER_FIELD_1, A.
USER_FIELD_2, A.USER_FIELD_3, A.USER_FIELD_4, A.USER_FIELD_5, A.
INITIAL_SEQ_NBR, B.DEPTID_CF, B.ACCOUNT, B.FUND_CODE, B.PROGRAM_
CODE, B.CLASS_FLD, B.AFFILIATE, B.OPERATING_UNIT, B.ALTACCT, B.B
UDGET_REF, B.CHARTFIELD1, B.CHARTFIELD2, B.CHARTFIELD3, B.AFFILI
ATE_INTRA1, B.AFFILIATE_INTRA2, A.TRC_TYPE, A.UNIT_OF_MEASURE, A
.BUSINESS_UNIT_GL, A.TL_TIME_TO_PAY, A.MSGNODENAME FROM PS_WRK_P
ROJ6_TAO4 A , PS_ACCT_CD_TBL B , PS_TL_TASKGRP_TBL C WHERE B.ACC
T_CD = A.ACCT_CD AND C.TASKGROUP = A.TASKGROUP AND C.COMMIT_ACCT
G_FLG = 'Y' AND A.PROCESS_INSTANCE=1185128 AND C.EFFDT = ( SELEC
T MAX(C1.EFFDT) FROM PS_TL_TASKGRP_TBL C1 WHERE C1.TASKGROUP = C
.TASKGROUP AND C1.EFFDT <= A.DUR) AND C.EFF_STATUS = 'A'

Steps to Recreate:
The issue can be reproduced at will with the following steps:
1. Run PY_PULL_COST

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.