sql执行历史-凯发app官方网站

-- 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执行的历史情况

阅读(12427) | 评论(0) | 转发(0) |

给主人留下些什么吧！~~

| | | | |

感谢所有关心和支持过chinaunix的朋友们