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.
- Oracle Multitenant DB 4 : Parameters/SGA/PGA management in CDB-PDB - July 18, 2020
- Details of running concurrent requests in Oracle EBS - May 26, 2020
- Oracle Multitenant DB 3 : Data Dictionary Architecture in CDB-PDB - March 20, 2020
Thanks Brijesh, For the tkprof analysis.
Thanks and Regards,
Amit
Nice one Brijesh.
just came across the problem. I will test the solution.
-Soumya