Switching Responsibility Is Taking a Long Time On Production

(Doc ID 1567566.1)

Last updated on JUNE 21, 2016

Applies to:

Oracle Application Object Library - Version 12.2.4 to 12.2.4 [Release 12.2]
Oracle Applications Technology Stack - Version 12.1.3 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

Switching Responsibility is taking 2 to 5 Minute Time....

TKProf output shows the following:

SELECT R.APPLICATION_ID, R.RESPONSIBILITY_ID, S.SECURITY_GROUP_ID,
 R.RESPONSIBILITY_NAME, S.SECURITY_GROUP_NAME
FROM
FND_RESPONSIBILITY_VL R, FND_USER_RESP_GROUPS U, FND_SECURITY_GROUPS_VL S
 WHERE U.USER_ID = :A00 AND SYSDATE BETWEEN U.START_DATE AND NVL(U.END_DATE,
 SYSDATE) AND R.VERSION IN ('4', 'W')  AND R.APPLICATION_ID =
 U.RESPONSIBILITY_APPLICATION_ID AND R.RESPONSIBILITY_ID =
 U.RESPONSIBILITY_ID AND SYSDATE BETWEEN R.START_DATE AND NVL(R.END_DATE,
 SYSDATE) AND U.SECURITY_GROUP_ID = S.SECURITY_GROUP_ID AND
 S.SECURITY_GROUP_ID >= 0 AND S.SECURITY_GROUP_KEY = NVL(:A01,
 S.SECURITY_GROUP_KEY) AND R.RESPONSIBILITY_KEY = NVL(:A02,
 R.RESPONSIBILITY_KEY) AND R.APPLICATION_ID = NVL(:A03, R.APPLICATION_ID)
 AND NVL(FND_PROFILE.VALUE('NODE_TRUST_LEVEL'), 1) <=  
 NVL(FND_PROFILE.VALUE_SPECIFIC('APPL_SERVER_TRUST_LEVEL', U.USER_ID,
 R.RESPONSIBILITY_ID,R.APPLICATION_ID),1)   ORDER BY RESPONSIBILITY_NAME,
 SECURITY_GROUP_NAME


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      2      0.20       0.57          6         41          0           0
Fetch        2     23.98      23.48          0    1855093          0         130
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        6     24.18      24.05          6    1855134          0         130

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 194  (APPS)


Cause

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms