SYSADMIN login performance issue in Oracle EBS R12

In our EBS system, we found that SYSADMIN login was slow. It was taking around 4-5 minutes to login and when inside the EBS application it was taking 2-3 minutes every time we were navigating in the self-service pages. This issue was not occurring for normal EBS users. We thought of applying the trace to SYSADMIN user to find the root cause of it.

Trace was applied as per “ USING PROFILE OPTION METHOD “Initialization SQL Statement – Custom” method which we already discussed earlier in our site. TKPORF was generated and below were the key SQLs that were taking more time in the tkprof files

SELECT * FROM (select /*+ ORDERED PUSH_SUBQ USE_NL (WN WL WIT) index(WN WF_NOTIFICATIONS_N1)*/ 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,
 nvl(WN.SENT_DATE, WN.BEGIN_DATE) BEGIN_DATE,
 WN.DUE_DATE,
 WN.STATUS,
 WN.PRIORITY*-1 AS 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'


--

 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 BEGIN_DATE desc

There were many other SQLs in the tkprof but above was the top most SQL. If you look at the information, we can see that elapsed time was 250 seconds which is approx 4 minutes. This roughly matches with the delay that we faced. SInce all tables are workflow related tables so issue was with the old workflow data which was not getting purged.

Looking at the WF_NOTIFICATIONS table for the sysadmin we found million notification which were in open status.

SInce it was non-production instance we went ahead and updated the STATUS for the SYSDADMIN notifications from back end. SYSADMIN was able to navigate normally after it.

SQL> select count(*) from WF_NOTIFICATIONS where recipient_role='SYSADMIN' and status='OPEN'
COUNT(*)
----------
1047713


SQL> SQL> update WF_NOTIFICATIONS set status = 'CLOSED', mail_status = 'SENT', end_date = sysdate where recipient_role='SYSADMIN';
1177713 rows updated.

SQL> commit;
Commit complete.

We also scheduled the WF purge programs as we saw some other WF tables also in the trace files which required purging.

Author
Recent Posts

Brijesh Gogia

I’m an experienced Cloud/Oracle Applications/DBA Architect with more than 15 years of full-time DBA/Architect experience. I have gained wide knowledge on Oracle and Non-Oracle software stack running on-prem and on Cloud and have worked on several big projects for multi-national companies. I enjoy working with leading-edge technology and have a passion for Cloud architecture, automation, database performance, and stability. Thankfully my work allows me time for researching new technologies (and to write about them).

SYSADMIN login performance issue in Oracle EBS R12

Related posts:

2 Comments