Performance issue with the Oracle seeded FND_PROFILE Function Call (Doc ID 2728966.1)

Last updated on JULY 04, 2021

Applies to:

Symptoms

Query with function call FND_PROFILE always appears in the top consuming / expensive  SQL queries


STEPS:
-----------------------
The issue can be reproduced at will with the following steps:

While running the WORKLOAD REPOSITORY report , observed the below SQL is highly consuming the time.

SELECT COUNT(*) FROM PER_TIME_PERIODS PTP WHERE PTP.PAYROLL_ID = :B2 AND DECODE(FND_PROFILE.VALUE('HR_CALCULATE_NUMBER_OF_PAY_PERIODS_BASED_ON'), 'REGULAR_PAYMENT_DATE', REGULAR_PAYMENT_DATE, END_DATE) BETWEEN TO_DATE('01/01/'||TO_CHAR(:B1 , 'YYYY'), 'DD/MM/YYYY') AND TO_DATE('31/12/' || TO_CHAR(:B1 , 'YYYY'), 'DD/MM/YYYY')

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.