show_sql_stat
emcs
81次下载
669次浏览
2019-07-24
4.5

脚本内容

--yong.zhao --查询SQL历史执行性能消耗情况,默认sysdate-7,可修改 select 'GV$' flag, 0 snap_id, inst_id, plan_hash_value phv, executions execs, disk_reads reads, disk_reads / decode(executions, NULL, 1, 0, 1, executions) reads_per, buffer_gets gets, buffer_gets / decode(executions, NULL, 1, 0, 1, executions) gets_per, rows_processed, rows_processed / decode(executions, NULL, 1, 0, 1, executions) rows_per, elapsed_time/1000 elap_ms, (elapsed_time/1000) / decode(executions, NULL, 1, 0, 1, executions) elap_per_ms from gv$sql where sql_id='&sql_id' union all select to_char(sht.begin_interval_time,'dd hh24:mi')||'--'||to_char(sht.end_interval_time,'hh24:mi') flag, sta.snap_id, sta.instance_number inst, sta.plan_hash_value phv, sta.executions_delta execs, sta.disk_reads_delta reads, sta.disk_reads_delta / decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) reads_per, sta.buffer_gets_delta gets, sta.buffer_gets_delta / decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) gets_per, sta.rows_processed_delta, sta.rows_processed_delta / decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) rows_per, sta.elapsed_time_delta/1000 elap_ms, (sta.elapsed_time_delta/1000) / decode(sta.executions_delta, NULL, 1, 0, 1, sta.executions_delta) elap_per_ms from dba_hist_sqlstat sta,dba_hist_snapshot sht where 1=1 and sta.instance_number=sht.instance_number and sta.snap_id=sht.snap_id and sht.begin_interval_time>= sysdate-7 and sta.sql_id='&sql_id' order by 1,2;

评论

贡献排行榜