How to find the change in explain plan in oracle for a sql id



Last updated on August 11th, 2015 at 10:10 am

 Many times because of stats issue ,addition in index and other parameter, The sql start performing bad. One of the reason could be change in execution plan of the sql. The below sql will shows the history of  all the execution/explain plan taken by the particular sql. This is very useful when your developer complains about performance on the sql query which was running nice yesterday

How to find the change in explain plan in oracle for a sql id

set
lines 155
col execs for 999,999,999
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1SELECT ss.snap_id,
ss.instance_number node,
begin_interval_time,
sql_id,
plan_hash_value,
NVL(executions_delta,0) execs,
(elapsed_time_delta/DECODE(NVL(executions_delta,0),0,1,executions_delta))/1000000
avg_etime,
(buffer_gets_delta /DECODE(NVL(buffer_gets_delta,0),0,1,executions_delta))
avg_lio
FROM DBA_HIST_SQLSTAT S,
DBA_HIST_SNAPSHOT SS
WHERE sql_id = NVL(‘&sql_id’,’xxxxxxxxxx’)
AND ss.snap_id = S.snap_id
AND ss.instance_number = S.instance_number
AND executions_delta > 0
ORDER BY 1,2,3
/

Leave a Reply