Over time oracle data can grow, along with other factors, that can change the execution plan for a particular query. What sucks is that execution plan can change without you knowing and could cause a big problem with performance.

The below is one tool your tool belt to beat bad queries before they beat you. Save the below in a .sql file, open a sqlplus shell and call the script with @. Once the script gets executed, you’ll be prompted for the AWR, standard targeted deviation, and the execution time target.

select * from (
select sql_id, parsing_schema_name, sum(execs), min(avg_etime) min_etime, max(avg_etime) max_etime, stddev_etime/min(avg_etime) norm_stddev
from (
select sql_id, parsing_schema_name, plan_hash_value, execs, avg_etime,
stddev(avg_etime) over (partition by sql_id) stddev_etime
from (
select sql_id, parsing_schema_name, 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
and elapsed_time_delta > 0
and s.snap_id > nvl(‘&earliest_snap_id’,0)
group by sql_id, plan_hash_value, parsing_schema_name
)
)
group by sql_id, stddev_etime, parsing_schema_name
)
where norm_stddev > nvl(to_number(‘&min_stddev’),2)
and max_etime > nvl(to_number(‘&min_etime’),.1)
order by norm_stddev