ANBOB™

根据AWR的收集信息查询出指定时间段内的逻辑读最高的几个对象，script

WITH segstat
     AS (  SELECT MIN (begin_interval_time) Btime,
                  MAX (end_interval_time) etime,
                  instance_number,
                  obj#,
                  MAX (logical_reads_total) - MIN (logical_reads_total) LRIO,
                  MAX (PHYSICAL_reads_total) - MIN (PHYSICAL_reads_total) PRIO
             FROM    dba_hist_seg_stat st
                  INNER JOIN
                     dba_hist_snapshot sn
                  USING (snap_id, dbid, instance_number)
            WHERE sn.begin_interval_time BETWEEN TO_DATE (
                                                    '2013-01-07 07:00:00',
                                                    'yyyy-mm-dd hh24:mi:ss')
                                             AND TO_DATE (
                                                    '2013-01-07 19:00:00',
                                                    'yyyy-mm-dd hh24:mi:ss')
         GROUP BY instance_number, obj#)
SELECT *
  FROM (SELECT segstat.*,
               ROUND ( (ratio_to_report (lrio) OVER ()) * 100, 2)||'%'
                  logicalratio,
               ROUND ( (ratio_to_report (prio) OVER ()) * 100, 2)||'%'
                  physicalratio,
               OBJ.OWNER,
               OBJ.OBJECT_TYPE,
               OBJ.OBJECT_NAME,
               ROW_NUMBER () OVER (ORDER BY lrio DESC) rn --sort by logical read
          FROM segstat JOIN dba_objects obj ON segstat.obj# = obj.object_id)
 WHERE rn <= 10;

--有不对的希望EMAIL通知我

打赏

微信扫一扫，打赏作者吧～