Workflow Worklist Access Performance Issue (Doc ID 1578337.1)

Last updated on NOVEMBER 04, 2016

Applies to:

Oracle Workflow Cartridge - Version 11.5.10.2 to 12.1.3 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.

Symptoms

It takes a long time to access the personal workflow worklist for some specific users.

The issue can be reproduced at will with the following steps:
1. The user logs into Applications.
2. Navigates to Workflow > Worklist.
3. It took long time to open the worklist page.

The following is found from 10046 level 12 trace file:

select distinct menu_id,ctx_resp_id, ctx_resp_appl_id,       ctx_org_id,
 ctx_secgrp_id  
from
fnd_grants where grantee_type = :1   and instance_type = 'GLOBAL'   and
 object_id = -1   and start_date < sysdate   and ((end_date is null)    or  
 (end_date > sysdate)) and grantee_key = :2


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute 165728     54.19     126.04          0          0          0           0
Fetch   165728     10.39      23.21          0     331456          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total   331457     64.58     149.26          0     331456          0           0

Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 183  (APPS)

Rows     Execution Plan
-------  ---------------------------------------------------
     0  SELECT STATEMENT   MODE: ALL_ROWS
     0   HASH (UNIQUE)
     0    TABLE ACCESS   MODE: ANALYZED (BY INDEX ROWID) OF 'FND_GRANTS'
              (TABLE)
     0     INDEX   MODE: ANALYZED (RANGE SCAN) OF 'FND_GRANTS_N9'
               (INDEX)


Elapsed times include waiting on following events:
 Event waited on                             Times   Max. Wait  Total Waited
 ----------------------------------------   Waited  ----------  ------------
 SQL*Net message to client                  331456        0.23          1.59
 SQL*Net message from client                331456        0.94        109.45
 latch: cache buffers chains                     1        0.00          0.00
 latch: row cache objects                        1        0.00          0.00
 latch free                                      1        0.06          0.06
********************************************************************************


 

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