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