0
show_sql_stat
emcs
55次下载
426次浏览
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;

评论

热门资源
热门脚本
近期活动
全部
暂无活动,敬请期待...
相关课程
全部
暂无课程,敬请期待...