查看SQL的历史执行情况

查看SQL的历史执行情况

set lines 200 pages 30
col shijian for a12
col inst_id for 99
col execu_d for 999999999999 
col bg_d for 9999999999
col dr_d for 9999999999
col et_d for 99999999
col ct_d for 99999999
col io_time for 999999
col clus_time for 999999
col ap_time for 999999
col cc_time for 999999
col et_onetime for 999999.99
select 
    to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24') shijian,
	a.instance_number inst_id,
	a.plan_hash_value,
    sum(a.EXECUTIONS_DELTA) execu_d,
    sum(a.BUFFER_GETS_DELTA ) bg_d,
    sum(a.DISK_READS_DELTA ) dr_d,
    sum(a.ELAPSED_TIME_DELTA/1000000) et_d,
    sum(a.CPU_TIME_DELTA/1000000)  ct_d,
    sum(IOWAIT_DELTA/1000000) io_time,
    sum(CLWAIT_DELTA/1000000) clus_time,
    sum(APWAIT_DELTA/1000000) ap_time,
    sum(ccwait_delta/1000000) cc_time,
    decode(sum(a.EXECUTIONS_DELTA),0,sum(a.ELAPSED_TIME_DELTA/1000000),
	sum(a.ELAPSED_TIME_DELTA/1000000)/sum(a.EXECUTIONS_DELTA)) et_onetime,	decode(sum(a.EXECUTIONS_DELTA),0,sum(a.rows_processed_DELTA),sum(a.rows_processed_DELTA)/sum(a.EXECUTIONS_DELTA)) rw_onetime
from     
    dba_hist_sqlstat a,dba_hist_snapshot b
where 
    a.SNAP_ID =b.SNAP_ID
and a.INSTANCE_NUMBER=b.INSTANCE_NUMBER
and a.sql_id='&sql_id' 
group by 
    to_char(b.END_INTERVAL_TIME,'yyyymmdd hh24'),a.plan_hash_value,a.instance_number
 order by 1;

获取以下信息：

执行计划是否改变

执行次数

执行时间消耗

返回结果集

逻辑读物理读