Performance Issues While Accessing Advanced Worklist (Doc ID 1122403.1)

Last updated on OCTOBER 04, 2016

Applies to:

Oracle Workflow Cartridge - Version 11.5.10.2 to 12.1.2 [Release 11.5.10 to 12.1]
Information in this document applies to any platform.
Checked for relevance on 09-MAR-2013

Symptoms

When accessing Advanced Worklist to open some notifications the page is hanging or is taking very long time to open.

Enabling trace show the top consuming SQL being:

SELECT * FROM (select WN.NOTIFICATION_ID,
WN.FROM_USER,
DECODE(WN.MORE_INFO_ROLE, NULL, WN.TO_USER, wf_directory.GetRoleDisplayName(WN.MORE_INFO_ROLE)) AS TO_USER,
DECODE(WN.MORE_INFO_ROLE, NULL, WN.SUBJECT, FND_MESSAGE.GET_STRING('FND','FND_MORE_INFO_REQUESTED')||' '||WN.SUBJECT) AS SUBJECT,
WN.LANGUAGE,
WN.BEGIN_DATE,
WN.DUE_DATE,
WN.STATUS,
WN.PRIORITY,
'P' AS PRIORITY_F,
WN.RECIPIENT_ROLE,
WN.END_DATE,
WIT.DISPLAY_NAME AS TYPE,
WN.MORE_INFO_ROLE,
WN.FROM_ROLE,
WN.MESSAGE_TYPE,
WN.MESSAGE_NAME,
WN.MAIL_STATUS,
WN.ORIGINAL_RECIPIENT
from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_TL WIT, WF_LOOKUPS_TL WL
where WN.STATUS = 'OPEN'
and WN.message_type = WIT.name
and WIT.language = userenv('LANG')
and WL.lookup_type = 'WF_NOTIFICATION_STATUS'
and WN.status = WL.lookup_code
and WL.language = userenv('LANG')
and WN.recipient_role 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)
and more_info_role is null
union all
select /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N6)*/ WN.NOTIFICATION_ID,
WN.FROM_USER,
DECODE(WN.MORE_INFO_ROLE, NULL, WN.TO_USER, wf_directory.GetRoleDisplayName(WN.MORE_INFO_ROLE)) AS TO_USER,
DECODE(WN.MORE_INFO_ROLE, NULL, WN.SUBJECT, FND_MESSAGE.GET_STRING('FND','FND_MORE_INFO_REQUESTED')||' '||WN.SUBJECT) AS SUBJECT,
WN.LANGUAGE,
WN.BEGIN_DATE,
WN.DUE_DATE,
WN.STATUS,
WN.PRIORITY,
'P' AS PRIORITY_F,
WN.RECIPIENT_ROLE,
WN.END_DATE,
WIT.DISPLAY_NAME AS TYPE,
WN.MORE_INFO_ROLE,
WN.FROM_ROLE,
WN.MESSAGE_TYPE,
WN.MESSAGE_NAME,
WN.MAIL_STATUS,
WN.ORIGINAL_RECIPIENT
from WF_NOTIFICATIONS WN, WF_ITEM_TYPES_TL WIT, WF_LOOKUPS_TL WL
where WN.STATUS = 'OPEN'
and WN.message_type = WIT.name
and WIT.language = userenv('LANG')
and WL.lookup_type = 'WF_NOTIFICATION_STATUS'
and WN.status = WL.lookup_code
and WL.language = userenv('LANG')
and WN.more_info_role in (select WUR.role_name
from WF_USER_ROLES WUR
where WUR.user_name = :4
and WUR.user_orig_system = :5
and WUR.user_orig_system_id= :6)) QRSLT ORDER BY SUBJECT desc

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.01 0 0 0 0
Execute 5 0.01 0.03 0 0 0 0
Fetch 5 4.96 5.49 0 646130 0 30
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 4.97 5.54 0 646130 0 30

Changes

No changes.

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