Performance issue for OAF Framework/Self Services Web Pages R12.2

Recently we are facing this issue after login in to EBS navigation on home page very slow

It was taking around 5 to 10 min to open the navigation page

We enabled the Trace to user level to find the solution for it.

 

Enabling Trace in user Level:

Step 1: Go to system administrator -> Profile ->system -> User

Enter username and Profile Option “Initialization SQL Statement – Custom” and Enter the user name then search

Step 2: Just replace the Username for below command and paste the command in profile values

 

Begin fnd_ctl.fnd_sess_ctl('','','TRUE','TRUE','LOG','ALTER SESSION SET TRACEFILE_IDENTIFIER='||''''||<Username>||''''||' EVENTS='||''''||'10046 TRACE NAME CONTEXT FOREVER, LEVEL 12'||'''');End;

 

Step 3: Retest the issue and generate tkprof

 

How to generate Tkprof:

 

Once user retest the issue disable the trace and go to trace location

Example Location: /oracle/product/diag/rdbms/TEST /TEST /trace

Search trace file ls -ltr *<User_NAme>*

Below command is recommended for generating trace files

tkprof <filename>.trc <filename>.txt sys=no  sort='(prsela,exeela,fchela)'

 

Trace files shows below query is takes long time

 

SQL ID: b6v53rtrf9h1at Plan Hash: 879829738   SELECT COUNT(1)  FROM  WF_NOTIFICATIONS WN, (SELECT WUR.ROLE_NAME FROM WF_USER_ROLES WUR WHERE   WUR.USER_NAME = :B3 AND WUR.USER_ORIG_SYSTEM = :B2 AND   WUR.USER_ORIG_SYSTEM_ID = :B1 ) WUR WHERE ( (WN.MORE_INFO_ROLE IS NULL AND   WN.RECIPIENT_ROLE = WUR.ROLE_NAME) OR (WN.MORE_INFO_ROLE = WUR.ROLE_NAME) )   AND WN.STATUS = 'OPEN'    call     count       cpu    elapsed       disk      query    current        rows ------- ------  -------- ---------- ---------- ---------- ----------  ---------- Parse        1      0.00       0.00          0          0          0           0 Execute      1      0.00       0.00          0          0          0           0 Fetch        1     25.56      25.79          2    6643422          0           1 ------- ------  -------- ---------- ---------- ---------- ----------  ---------- total        3     25.56      25.79          2    6643422          0           1

 

Solution:

In our case sql Plan was changed and we run the tuning adviser report for the SQL_ID

Tuning adviser report recommend the alternative plan and apply the tuning adviser report recommendation and retest the issue

After applying the Tuning adviser report suggestion is same issue Please export the same plan in working instance and import it.

Example for tuning adviser finding

 

3- Alternative Plan Finding ---------------------------   Some alternative execution plans for this statement were found by searching   the system's real-time and historical performance data.   The following table lists these plans ranked by their average elapsed time.   See section "ALTERNATIVE PLANS SECTION" for detailed information on each   plan.   id plan hash  last seen            elapsed (s)  origin          note   -- ---------- -------------------- ------------ --------------- --------------    1 3915022962  2016-11-01/10:37:42        0.162 STS    2 3137219961  2016-11-01/10:37:42        0.227 STS    3  755713327  2017-09-17/05:32:52        0.229 STS    4  872829738  2021-12-28/03:24:52      153.797 Cursor Cache    original plan   Recommendation   --------------   - Consider creating a SQL plan baseline for the plan with the best average     elapsed time.     execute dbms_sqltune.create_sql_plan_baseline(task_name => 'TASK_198343',             owner_name => 'SYS', plan_hash_value => 3915022654);

 

 Reference:-

Slow Performance Navigating Through OA Framework/Self Services Web Pages (Doc ID 2023793.1)