12.2 - SQL Statements Accessing Tables FND_PROFILE_OPTIONS, FND_PROFILE_VALUES_RESP_V, and FND_PROFILE_VALUES_APPL_V Are Showing Multiple Executions (Doc ID 2099303.1)

Last updated on FEBRUARY 22, 2023

Applies to:

Symptoms

 SQL statements accessing tables FND_PROFILE_OPTIONS, FND_PROFILE_VALUES_RESP_V, and FND_PROFILE_VALUES_APPL_V show millions of executions in 12.2.

Multiple executions of the same statement cause performance impacts including OAF page timeouts, slow Form processes, and slow query performance on FND_PROFILE_OPTIONS and FND_PROFILE_VALUES_RESP_V.

The sql statement below is an example showing multiple executions:

SELECT DECODE(:B2 , 'USER', USER_ENABLED_FLAG, 'RESP', RESP_ENABLED_FLAG,
'APPL', APP_ENABLED_FLAG, 'SERVER', SERVER_ENABLED_FLAG, 'ORG',
ORG_ENABLED_FLAG, 'SERVRESP', SERVERRESP_ENABLED_FLAG, 'SITE',
SITE_ENABLED_FLAG)
FROM
FND_PROFILE_OPTIONS WHERE ROWID = :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 1006200 75.22 168.22 0 0 0 0
Fetch 1006200 4.52 10.32 0 1006200 0 1006200
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2012400 79.75 178.54 0 1006200 0 1006200

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

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 0 0.00 0.00 0 0 0 0
Execute 574963 45.24 103.79 0 0 0 0
Fetch 574963 3.41 7.70 0 1724889 0 574963
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1149926 48.65 111.50 0 1724889 0 574963

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.