oracle实用sql(11)--收集一周各时段的load profile

    工作中需要对近一周每小时的db time,db cpu,logical read,physical  read,transactions等信息做成EXCEL图表的形式，如果这些数据从awr报告中一个一个产生来收集就有些浪费时间了.
可以通过以下查询，直接获取数据库各实例中按默认快照间隔的load profile,然后将查询结果粘贴到EXCEL中来形成图表。

select f.instance_number,
       f.stat_name,
       to_char(f.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') b_time,
       to_char(g.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') e_time,
       (g.value - f.value) / 1000000 value_sum,
       EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +
       EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +
       EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +
       EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME) elapsed_second,
       (g.value - f.value) / 1000000 /
       (EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +
       EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +
       EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +
       EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME)) value_per_sec
  from (select a.instance_number,
               a.snap_id b_snap_id,
               lead(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) e_snap_id,
               a.stat_name,
               a.value,
               b.end_interval_time
          from DBA_HIST_SYS_TIME_MODEL a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.stat_name in ('DB time', 'DB CPU')
           and b.begin_interval_time >= trunc(sysdate - 7)
           and b.begin_interval_time <= trunc(sysdate)) f,
       (select a.instance_number,
               lag(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) b_snap_id,
               a.snap_id e_snap_id,
               a.stat_name,
               a.value,
               b.end_interval_time
          from DBA_HIST_SYS_TIME_MODEL a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.stat_name in ('DB time', 'DB CPU')
           and b.begin_interval_time >= trunc(sysdate - 7)
           and b.begin_interval_time <= trunc(sysdate)) g
 where g.e_snap_id != 0
   and f.b_snap_id != 0
   and f.instance_number = g.instance_number
   and f.stat_name = g.stat_name
   and f.b_snap_id = g.b_snap_id
   and f.e_snap_id = g.e_snap_id
union all
select f.instance_number,
       f.stat_name,
       to_char(f.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') b_time,
       to_char(g.END_INTERVAL_TIME, 'yyyy-mm-dd hh24:mi:ss') e_time,
       (g.value - f.value)  value_sum,
       EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +
       EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +
       EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +
       EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME) elapsed_second,
       (g.value - f.value) /
       (EXTRACT(DAY FROM g.end_interval_time - f.END_INTERVAL_TIME) * 86400 +
       EXTRACT(HOUR FROM g.end_interval_time - f.END_INTERVAL_TIME) * 3600 +
       EXTRACT(MINUTE FROM g.end_interval_time - f.END_INTERVAL_TIME) * 60 +
       EXTRACT(SECOND FROM g.end_interval_time - f.END_INTERVAL_TIME)) value_per_sec
  from (select a.instance_number,
               a.snap_id b_snap_id,
               lead(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) e_snap_id,
               a.stat_name,
               a.value,
               b.end_interval_time
          from DBA_HIST_SYSSTAT a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.stat_name in ('redo size',
                               'session logical reads',
                               'db block changes',
                               'physical reads',
                               'physical writes',
                               'user calls',
                               'parse count (total)',
                               'parse count (hard)',
                               'sorts (memory)',
                               'logons cumulative',
                               'execute count',
                               'user commits',
                               'user rollbacks')
           and b.begin_interval_time >= trunc(sysdate - 7)
           and b.begin_interval_time <= trunc(sysdate)) f,
       (select a.instance_number,
               lag(a.snap_id, 1, 0) over(partition by a.instance_number, stat_name order by a.snap_id) b_snap_id,
               a.snap_id e_snap_id,
               a.stat_name,
               a.value,
               b.end_interval_time
          from DBA_HIST_SYSSTAT a, dba_hist_snapshot b
         where a.snap_id = b.snap_id
           and a.instance_number = b.instance_number
           and a.stat_name in ('redo size',
                               'session logical reads',
                               'db block changes',
                               'physical reads',
                               'physical writes',
                               'user calls',
                               'parse count (total)',
                               'parse count (hard)',
                               'sorts (memory)',
                               'logons cumulative',
                               'execute count',
                               'user commits',
                               'user rollbacks')
           and b.begin_interval_time >= trunc(sysdate - 7)
           and b.begin_interval_time <= trunc(sysdate)) g
 where g.e_snap_id != 0
   and f.b_snap_id != 0
   and f.instance_number = g.instance_number
   and f.stat_name = g.stat_name
   and f.b_snap_id = g.b_snap_id
   and f.e_snap_id = g.e_snap_id
 order by 1, 2, 3;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/28539951/viewspace-2123382/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/28539951/viewspace-2123382/