Custom concurrent program querying FND tables

[VinodN]

Hello Erman,

Our EBS 12.2.10, 19c db instance is suffering from a custom report issue. The report, when submitted by users, results in high load averages on the server. The database session shows that it is executing queries such as

SELECT PROFILE_OPTION_NAME FROM FND_PROFILE_OPTIONS WHERE PROFILE_OPTION_ID = :B2 AND APPLICATION_ID = :B1


SELECT PROFILE_OPTION_VALUE FROM FND_PROFILE_VALUES_SITE_V WHERE PROFILE_OPTION_ID = :B2 AND APPLICATION_ID = :B1 AND PROFILE_OPTION_VALUE IS NOT NULL

The same report, when run on a non-prod instance is not running these queries. We tried running the report as the same user and with the same responsibility on both environments.
What could be the reason? I am attaching ASH report and execution plan from prod. 774_session_ASH.pdfAR_INVOICE_PRINT_PROGRAM_long_running_sql.xlsx

[ErmanArslansOracleBlog]

It may not be cuased by that fnd_profile_options query. The real cause of that performance difference between PROD and Non-PROD, may be the data size difference between PROD and non-PROD environments. So, check data size.. Data size affect resource consumption + execution plan(s), enable trace concurrent program trace to check sql query execution details and find the real cause.