Login taking long time into an instance with hundreds of thousands of users (Doc ID 1560769.1)

Last updated on MARCH 08, 2017

Applies to:

Oracle EBS Applications Performance - Version 12.1.1 to 12.1.3 [Release 12.1]
Information in this document applies to any platform.

Symptoms

 Login into an instance with hundreds of thousands of users in fnd_users may take time when running the following query:


SELECT * FROM ((SELECT m.menu_name as name,
     m.user_menu_name as display_name
FROM fnd_menus_vl m, fnd_grants g
WHERE m.menu_id = g.menu_id
    AND m.menu_name LIKE 'SESG%'
    AND g.start_date <= SYSDATE
    AND NVL(g.end_date, SYSDATE) >= SYSDATE
    AND (g.grantee_type='GLOBAL' OR
         g.grantee_key IN
           (SELECT WUR.role_name
              FROM WF_USER_ROLES WUR
               WHERE WUR.user_name = :1
                  AND WUR.user_orig_system = :2
                  AND WUR.user_orig_system_id= :3) OR
         g.grantee_orig_system_id IN
           (select jrgv.group_id
              from jtf_rs_groups_vl jrgv,
              jtf_rs_resource_extns_vl jrrev,
              jtf_rs_group_members_vl jrgmv
                where jrgmv.resource_id=jrrev.resource_id
                  and jrgmv.group_id=jrgv.group_id
                  and jrrev.user_name = :4)))union (select 'All','All' from dual)) QRSLT  ORDER BY display_name

call     count       cpu    elapsed       disk      query  current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0  0           0
Execute      4      0.00       0.00          0          0  0           0
Fetch        4     57.63      57.71          0   24536240  0          24
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        8     57.63      57.71          0   24536240  0          24

Changes

 This may happen in R12.1 when the SES interface is enabled on the top of responsibility screen

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