"Validate For BEE" Performance Issues After 12.1 HRMS RUP8 (Doc ID 2064774.1)

Last updated on JULY 15, 2016

Applies to:

Oracle Time and Labor - Version 12.1 HRMS RUP8 and later
Information in this document applies to any platform.

Symptoms

On : 12.1.3 version,

ACTUAL BEHAVIOR
On peaclcal.pkb 120.6.12010000.10.

Poor performance with 'Validate For BEE' after applying RUP 8.

Top problem sqls:

****************************************************************************
SQL ID: 1hfnkr719tvn4 Plan Hash: 1342796398
SELECT /*+ index(pee PAY_ELEMENT_ENTRIES_F_N53,iv PAY_INPUT_VALUES_F_N50 )*/
FND_NUMBER.CANONICAL_TO_NUMBER(PEV.SCREEN_ENTRY_VALUE) AMOUNT,
NCR.ADD_OR_SUBTRACT ADD_OR_SUBTRACT
FROM
PAY_ACCRUAL_PLANS PAP, PAY_NET_CALCULATION_RULES NCR, PAY_ELEMENT_ENTRIES_F
PEE, PAY_ELEMENT_ENTRY_VALUES_F PEV, PAY_INPUT_VALUES_F IV WHERE
PAP.ACCRUAL_PLAN_ID = :B4 AND PEE.ASSIGNMENT_ID = :B3 AND
PEE.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID AND PEV.INPUT_VALUE_ID =
NCR.INPUT_VALUE_ID AND PAP.ACCRUAL_PLAN_ID = NCR.ACCRUAL_PLAN_ID AND
NCR.INPUT_VALUE_ID NOT IN (PAP.CO_INPUT_VALUE_ID,PAP.PTO_INPUT_VALUE_ID)
AND PEV.SCREEN_ENTRY_VALUE IS NOT NULL AND ((:B2 IS NOT NULL AND :B2 =
NCR.INPUT_VALUE_ID) OR :B2 IS NULL) AND PEV.EFFECTIVE_START_DATE =
PEE.EFFECTIVE_START_DATE AND PEV.EFFECTIVE_END_DATE =
PEE.EFFECTIVE_END_DATE AND IV.INPUT_VALUE_ID = NCR.INPUT_VALUE_ID AND :B1
BETWEEN IV.EFFECTIVE_START_DATE AND IV.EFFECTIVE_END_DATE AND
PEE.ELEMENT_TYPE_ID = IV.ELEMENT_TYPE_ID AND EXISTS (SELECT /*+ index(piv2
PAY_INPUT_VALUES_F_N50)*/ NULL FROM PAY_ELEMENT_ENTRY_VALUES_F PEV1,
PAY_INPUT_VALUES_F PIV2 WHERE PEV1.ELEMENT_ENTRY_ID = PEV.ELEMENT_ENTRY_ID
AND PEV1.INPUT_VALUE_ID = NCR.DATE_INPUT_VALUE_ID AND
PEV1.EFFECTIVE_START_DATE = PEV.EFFECTIVE_START_DATE AND
PEV1.EFFECTIVE_END_DATE = PEV.EFFECTIVE_END_DATE AND
NCR.DATE_INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID AND PEE.ELEMENT_TYPE_ID =
PIV2.ELEMENT_TYPE_ID AND :B1 BETWEEN PIV2.EFFECTIVE_START_DATE AND
PIV2.EFFECTIVE_END_DATE AND
FND_DATE.CANONICAL_TO_DATE(DECODE(SUBSTR(PIV2.UOM, 1, 1),'D',
PEV1.SCREEN_ENTRY_VALUE, NULL)) BETWEEN :B5 AND :B1 )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- -------- ---------- -------- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 11734 0.45 0.44 0 0 0 0
Fetch 11766 220.02 4452.51 1697232 14282481 0 150451
------- ------ -------- ---------- -------- ---------- -------- ------
total 23501 220.47 4452.95 1697232 14282481 0 150451
***************************************************************************
SQL ID: awb9r5bkw5dx2 Plan Hash: 3651357498
SELECT PEE.ASSIGNMENT_ID, PAP.ACCRUAL_PLAN_ID, PAP.ACCRUAL_PLAN_NAME,
PAP.ACCRUAL_CATEGORY, PAP.BUSINESS_GROUP_ID
FROM
PAY_ELEMENT_ENTRIES_F PEE, PAY_ELEMENT_LINKS_F PEL, PAY_ELEMENT_TYPES_F PET,
PAY_ACCRUAL_PLANS PAP WHERE PEE.ASSIGNMENT_ID = :B2 AND :B1 BETWEEN
PEE.EFFECTIVE_START_DATE AND PEE.EFFECTIVE_END_DATE AND PEL.ELEMENT_LINK_ID
= PEE.ELEMENT_LINK_ID AND :B1 BETWEEN PEL.EFFECTIVE_START_DATE AND
PEL.EFFECTIVE_END_DATE AND PEL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID AND
:B1 BETWEEN PET.EFFECTIVE_START_DATE AND PET.EFFECTIVE_END_DATE AND
PET.ELEMENT_TYPE_ID = PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------ ---------- ---------- ------
Parse 1 0.00 0.00 0 0 0 0
Execute 9113 0.25 0.25 0 0 0 0
Fetch 26794 14.98 624.56 187739 493983 0 17685
------- ------ -------- ---------- ------ ---------- ---------- ------
total 35908 15.23 624.82 187739 493983 0 17685
****************************************************************************
SQL ID: gqgjap52u7mmg Plan Hash: 1251054560
SELECT EL.ELEMENT_LINK_ID
FROM
PER_ASSIGNMENTS_F ASG, PAY_ELEMENT_LINKS_F EL WHERE ASG.ASSIGNMENT_ID = :B3
AND EL.BUSINESS_GROUP_ID + 0 = ASG.BUSINESS_GROUP_ID + 0 AND
EL.ELEMENT_TYPE_ID = :B2 AND :B1 BETWEEN ASG.EFFECTIVE_START_DATE AND
ASG.EFFECTIVE_END_DATE AND :B1 BETWEEN EL.EFFECTIVE_START_DATE AND
EL.EFFECTIVE_END_DATE AND ( ( EL.PAYROLL_ID IS NOT NULL AND EL.PAYROLL_ID =
ASG.PAYROLL_ID ) OR ( EL.LINK_TO_ALL_PAYROLLS_FLAG = 'Y' AND ASG.PAYROLL_ID
IS NOT NULL ) OR ( EL.PAYROLL_ID IS NULL AND EL.LINK_TO_ALL_PAYROLLS_FLAG =
'N' ) ) AND ( EL.JOB_ID IS NULL OR EL.JOB_ID = ASG.JOB_ID ) AND (
EL.GRADE_ID IS NULL OR EL.GRADE_ID = ASG.GRADE_ID ) AND ( EL.POSITION_ID IS
NULL OR EL.POSITION_ID = ASG.POSITION_ID ) AND ( EL.ORGANIZATION_ID IS NULL
OR EL.ORGANIZATION_ID = ASG.ORGANIZATION_ID ) AND ( EL.LOCATION_ID IS NULL
OR EL.LOCATION_ID = ASG.LOCATION_ID ) AND ( EL.PAY_BASIS_ID IS NULL OR
EL.PAY_BASIS_ID = ASG.PAY_BASIS_ID ) AND ( EL.EMPLOYMENT_CATEGORY IS NULL
OR EL.EMPLOYMENT_CATEGORY = ASG.EMPLOYMENT_CATEGORY ) AND (
EL.PEOPLE_GROUP_ID IS NULL OR EXISTS ( SELECT NULL FROM
PAY_ASSIGNMENT_LINK_USAGES_F ALU WHERE ALU.ASSIGNMENT_ID =
ASG.ASSIGNMENT_ID AND ALU.ELEMENT_LINK_ID = EL.ELEMENT_LINK_ID AND :B1
BETWEEN ALU.EFFECTIVE_START_DATE AND ALU.EFFECTIVE_END_DATE) )
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ------- ---------- -------- -------
Parse 1 0.00 0.00 0 0 0 0
Execute 125721 2.97 2.97 0 0 0 0
Fetch 125721 139.96 205.36 29614 14219296 0 125721
------- ------ -------- ---------- ------- ---------- -------- -------
total 251443 142.93 208.34 29614 14219296 0 125721

****************************************************************************


EXPECTED BEHAVIOR
Expect "Validate For BEE" to completes in a reasonable time.


STEPS
The issue can be reproduced at will with the following steps:
1. Run 'Validate for BEE'

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