sql执行历史

-- sql 历史

set lines 200
set pages 1000
col shijian for a13
col execu_d for 999999
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

select to_char(b.END_INTERVAL_TIME, 'yyyy-mm-dd hh24') shijian,
       plan_hash_value,
       round(sum(a.EXECUTIONS_DELTA),1) exec_cnt,
       round(sum(a.BUFFER_GETS_DELTA),1) buffer_get,
       round(sum(a.DISK_READS_DELTA),1) disk_read,
       round(sum(a.ELAPSED_TIME_DELTA / 1000000),1) et_d,
       round(sum(a.CPU_TIME_DELTA / 1000000),1) ct_d,
       round(sum(IOWAIT_DELTA / 1000000),1) io_time,
       round(sum(CLWAIT_DELTA / 1000000),1) clus_time,
       round(sum(APWAIT_DELTA / 1000000),1) ap_time,
       round(sum(ccwait_delta / 1000000),1) cc_time,
       round(decode(sum(a.EXECUTIONS_DELTA), 0,
              sum(a.BUFFER_GETS_DELTA),
              round(sum(a.BUFFER_GETS_DELTA) / sum(a.EXECUTIONS_DELTA), 0)),1) get_onetime,
       round(decode(sum(a.EXECUTIONS_DELTA), 0,
              sum(a.rows_processed_delta),
              round(sum(a.rows_processed_delta) / sum(a.EXECUTIONS_DELTA), 0)),1) rows_onetime,
      round( decode(sum(a.EXECUTIONS_DELTA), 0,
              sum(a.ELAPSED_TIME_DELTA / 1000),
              round(sum(a.ELAPSED_TIME_DELTA / 1000) /
                    sum(a.EXECUTIONS_DELTA), 0)),1) exec_ms
  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 = '&1'
 group by to_char(b.END_INTERVAL_TIME, 'yyyy-mm-dd hh24'),plan_hash_value
 order by 1,2;

展示一条sql执行的历史情况