Oracle Query plan changed..

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 1
select 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','4dqs2k5tynk61')
and ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
order by 1, 2, 3
/

------------------------------------------------------------------------------------------------------------
set lines 155
col execs for 999,999,999
col min_etime for 999,999.99
col max_etime for 999,999.99
col avg_etime for 999,999.999
col avg_lio for 999,999,999.9
col norm_stddev for 999,999.9999
col begin_interval_time for a30
col node for 99999
break on plan_hash_value on startup_time skip 1
select * from (
select sql_id, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, plan_hash_value,
sum(nvl(executions_delta,0)) execs,
(sum(elapsed_time_delta)/decode(sum(nvl(executions_delta,0)),0,1,sum(executions_delta))/1000000) avg_etime
-- sum((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 ss.snap_id = S.snap_id
and ss.instance_number = S.instance_number
and executions_delta > 0
group by sql_id, plan_hash_value
)
)
group by sql_id, stddev_etime
)
where norm_stddev > nvl(to_number('&min_stddev'),2)
and max_etime > nvl(to_number('&min_etime'),.1)
order by norm_stddev
/
----------------------------------------------------------------------------------------------------------------

A pro-active approach can be to mine AWR data for any SQL execution plan changes.

Following query from dba_hist_sqlstat can retrieve the list of SQL IDs whose plans have changed. It orders the SQL IDs,so that those SQL IDs for which maximum gains can be achieved by fixing plan, are listed first.

spool sql_with_more_than_1plan.txt

set lines 220 pages 9999 trimspool on

set numformat 999,999,999

column plan_hash_value format 99999999999999

column min_snap format 999999

column max_snap format 999999

column min_avg_ela format 999,999,999,999,999

column avg_ela format 999,999,999,999,999

column ela_gain format 999,999,999,999,999

select sql_id,

       min(min_snap_id) min_snap,

       max(max_snap_id) max_snap,

       max(decode(rw_num,1,plan_hash_value)) plan_hash_value,

       max(decode(rw_num,1,avg_ela)) min_avg_ela,

       avg(avg_ela) avg_ela,

       avg(avg_ela) - max(decode(rw_num,1,avg_ela)) ela_gain,

       -- max(decode(rw_num,1,avg_buffer_gets)) min_avg_buf_gets,

       -- avg(avg_buffer_gets) avg_buf_gets,

       max(decode(rw_num,1,sum_exec))-1 min_exec,

       avg(sum_exec)-1 avg_exec

from (

  select sql_id, plan_hash_value, avg_buffer_gets, avg_ela, sum_exec,

         row_number() over (partition by sql_id order by avg_ela) rw_num , min_snap_id, max_snap_id

  from

  (

    select sql_id, plan_hash_value , sum(BUFFER_GETS_DELTA)/(sum(executions_delta)+1) avg_buffer_gets,

    sum(elapsed_time_delta)/(sum(executions_delta)+1) avg_ela, sum(executions_delta)+1 sum_exec,

    min(snap_id) min_snap_id, max(snap_id) max_snap_id

    from dba_hist_sqlstat a

    where exists  (

       select sql_id from dba_hist_sqlstat b where a.sql_id = b.sql_id

         and  a.plan_hash_value != b.plan_hash_value

         and  b.plan_hash_value > 0)

    and plan_hash_value > 0

    group by sql_id, plan_hash_value

    order by sql_id, avg_ela

  )

  order by sql_id, avg_ela

  )

group by sql_id

having max(decode(rw_num,1,sum_exec)) > 1

order by 7 desc

/

spool off

clear columns

set numformat 9999999999

MIN_SNAP and MAX_SNAP are the minimum/maximum snap id where the SQL statement occurs

PLAN_HASH_VALUE is the hash_value of the plan with the best elapsed time

ELA_GAIN is the estimated improvement in elapsed time by using this plan compared to the average execution time.

Using the output of the above query, sql execution plans can be fixed, after proper testing.  This method can help DBAs pin-point and resolve problems with SQL execution plans, faster.