Skip to content

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.

 

[Post Views: 123]

Read Offline: PDF Download

Brijesh Gogia

I’m an experienced Oracle Applications DBA with more than a decade of full-time DBA experience. I have gained a wide knowledge of the Oracle software stack and have worked on several big projects for multi-national companies. I enjoy working with the leading-edge technology and have passion for database performance and stability. Thankfully my work allows me time for researching new technologies (and to write about them).
  
You can connect with me on LinkedIn.

One Comment

  1. Amit Kumar Das Amit Kumar Das

    Thanks Brijesh, For the tkprof analysis.

    Thanks and Regards,
    Amit

Leave a Reply

Required fields are marked *