RE: Enforcing SQL execution plan

So, how do the 2 runs compare when broken down by wait event class? What class is contributing the most to the bad run?

select st.SNAP_ID, st.sql_id, st.plan_hash_value, ss.begin_interval_time ,EXECUTIONS_DELTA execs
, IOWAIT_DELTA
, CLWAIT_TOTAL
, CCWAIT_DELTA
, APWAIT_DELTA
, CPU_TIME_DELTA
, ELAPSED_TIME_DELTA
, round((IOWAIT_DELTA/ELAPSED_TIME_DELTA)*100,2) io_wt_perc
, round((CLWAIT_TOTAL/ELAPSED_TIME_DELTA)*100,2) clust_wt_perc
, round((CCWAIT_DELTA/ELAPSED_TIME_DELTA)*100,2) concur_wt_perc
, round((APWAIT_DELTA/ELAPSED_TIME_DELTA)*100,2) app_wt_perc
, round((CPU_TIME_DELTA/ELAPSED_TIME_DELTA)*100) cpu_wt_perc
from dba_hist_sqlstat st, dba_hist_snapshot ss where
ss.snap_id = st.snap_id
and st.instance_number = (select instance_number from v$instance ) and EXECUTIONS_DELTA > 0
--and sql_id = '&sql_id'
and rownum < 1000
and begin_interval_time >= sysdate - 7
order by snap_id
;

From: Hameed, Amir

--
http://www.freelists.org/webpage/oracle-l