    工作中需要對近一週每小時的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;

oracle實用sql(11)--收集一週各時段的load profile

相關文章