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.

 

Brijesh Gogia

2 Comments

  1. Amit Kumar Das Amit Kumar Das

    Thanks Brijesh, For the tkprof analysis.

    Thanks and Regards,
    Amit

  2. Soumya G Roy Soumya G Roy

    Nice one Brijesh.
    just came across the problem. I will test the solution.

    -Soumya

Leave a Reply