Performance issue with the Oracle seeded FND_PROFILE Function Call
(Doc ID 2728966.1)
Last updated on JULY 04, 2021
Applies to:Oracle Time and Labor - Version 12.2.8 and later
Information in this document applies to any platform.
Query with function call FND_PROFILE always appears in the top consuming / expensive SQL queries
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')
To view full details, sign in with your My Oracle Support account.
Don't have a My Oracle Support account? Click to get started!
In this Document