Symptoms:
Customer complains that a particular module form LOV is inconsistently slow
Solution:
Enable tracing using below steps and generate tkprof file
a) Create the following procedure as APPS:
CREATE OR REPLACE PROCEDURE set_trace_param AS
BEGIN
FND_CTL.FND_SESS_CTL(”, ”, ”, ‘TRUE’, ”, ‘ALTER SESSION SET MAX_DUMP_FILE_SIZE=UNLIMITED’);
FND_CTL.FND_SESS_CTL(”, ”, ”, ‘TRUE’, ”, ‘ALTER SESSION SET TIMED_STATISTICS=TRUE’);
FND_CTL.FND_SESS_CTL(”, ”, ”, ‘TRUE’, ”, ‘ALTER SESSION SET STATISTICS_LEVEL=ALL’);
END;
/
–Copy the above, including the “/” in the last line
b) Set the following profile option at USER level, only for the USER who will reproduce the issue :
Profile option: Initialization SQL Statement – Custom
Value: begin set_trace_param; end;
–Copy the above, including the “;” after the “end”
IMPORTANT ==> If the user was logged to the application, please make sure he/she LOGOUT and relogin after the profile option is set.
c) Navigate to the screen to reproduce the issue. Do NOT reproduce the issue yet.
d) Just before reproducing the issue (i.e.: before clicking on FIND button) go to Help->Diagnostics->Trace->Trace With Waits and Binds
———————————————-
e) Use a stopwatch/timer while reproducing the issue. The time that you get should match or at least be similar to the time reflected in the trace that you will provide.
Reproduce the issue (i.e.: Click on FIND button)
f) Turn off trace and your stopwatch/timer immediately after you get the response from the screen, and EXIT the application completely (You must logout from the Personal Home Page too)
(To turn off trace: Help->Diagnostics->Trace->No Trace)g6) Run tkprof as follows for the trace file generated:
tkprof <trace file> <output file> sort=exeela,prsela,fchela explain=apps/<password>
IMPORTANT: Please make sure you are using tkprof from DB Oracle Home, and that you logged out from the application. If you are still in the same session in the application, the rows column in tkprof might be all zeros.
g) IMPORTANT: Do not forget to set the profile option “Initialization SQL Statement – Custom” back to BLANK afterwardsh
h) Once you have the raw trace and sorted tkprof trace files, review them for most expensive sql (sql with most no of of fetches). Extract the sql id and plash has value for the sql with most number of fetches (most expensive SQL)
i) If you have just the sql_id from an AWR report, for example, you can use the statement below to find the plan_hash_value select sql_id from v$sql where hash_value = ‘hash value from the trace’;
j) Having the sql_id, we can obtain the “plan_hash_value” running the statement below. Note that if there is more than one plan for the same SQL, it will show multiples per the example below.
WITH
p AS (
SELECT plan_hash_value
FROM gv$sql_plan
WHERE sql_id = TRIM(‘&&sql_id.’)
AND other_xml IS NOT NULL
UNION
SELECT plan_hash_value
FROM dba_hist_sql_plan
WHERE sql_id = TRIM(‘&&sql_id.’)
AND other_xml IS NOT NULL ),
m AS (
SELECT plan_hash_value,
SUM(elapsed_time)/SUM(executions) avg_et_secs
FROM gv$sql
WHERE sql_id = TRIM(‘&&sql_id.’)
AND executions > 0
GROUP BY
plan_hash_value ),
a AS (
SELECT plan_hash_value,
SUM(elapsed_time_total)/SUM(executions_total) avg_et_secs
FROM dba_hist_sqlstat
WHERE sql_id = TRIM(‘&&sql_id.’)
AND executions_total > 0
GROUP BY
plan_hash_value )
SELECT p.plan_hash_value,
ROUND(NVL(m.avg_et_secs, a.avg_et_secs)/1e6, 3) avg_et_secs
FROM p, m, a
WHERE p.plan_hash_value = m.plan_hash_value(+)
AND p.plan_hash_value = a.plan_hash_value(+)
ORDER BY
avg_et_secs NULLS LAST;
k) Download sqltxplain utility from
SQLT (SQLTXPLAIN) – Tool that helps to diagnose a SQL statement performing poorly Document 215187.1
The script named coe_xfr_sql_profile.sql is provided under the /utl directory.
l) Having the sql_id and the “desired” plan_hash_id, run the following:
sqlplus / as sysdba
SQL> START coe_xfr_sql_profile.sql [SQL_ID] [PLAN_HASH_VALUE];
This will generate a script named like:
coe_xfr_sql_profile_&&sql_id._&&plan_hash_value..sql
For example:
coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sql
This file contains the stored profile from the database that you obtained it from. You can now use this script to apply the same plan to another database:
m) Just execute this script in the DB where you want to create this profilesqlplus / as sysdba
SQL> START coe_xfr_sql_profile_dnzjmxkbmk4uy_2517809292.sqn
Metalink Reference: Using Sqltxplain To Create A ‘SQL Profile’ To Consistently Reproduce a Good SQL Plan (Doc ID 1487302.1)
Orama.blog 