Invoice print requests not completing

[VinodN]

Hello Erman! In our EBS 12.2.10 instance (19c on RHEL8), custom AR invoice print requests for US are just running on and on, while those for UK completed, although they took slightly longer. The load average on the database server is high - 60 on 32 cpu server. Most of the request database sessions are running following sql -

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

OR

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

Not getting what is the root cause here..

[VinodN]

It turned out that the sql plan had changed and that is causing the issue.

[ErmanArslansOracleBlog]

I already answered this .. This is a duplicate issue.

My response:

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.

Duplicate issue:

http://erman-arslan-s-oracle-forum.124.s1.nabble.com/Custom-concurrent-program-querying-FND-tables-td13023.html