【轉自mos文章】使用單條sql來查詢出awr中的syatem statistics

標籤：

使用單條sql來查詢出awr中的syatem statistics

參考自：
How to monitor system statistics from AWR snapshot by single SQL? (Doc ID 1320445.1)

適用於：
Oracle Server - Enterprise Edition - Version: 10.2.0.1 and later   [Release: 10.2 and later ]
Information in this document applies to any platform.

目標：
SQL to monitor the latest changes of system statistics gathered by Automatic Workload Repository.

The following statistics are included. 'redo size''physical reads''physical writes''session logical reads''user calls','parse count (hard)''gcs messages sent''ges messages sent''gc cr blocks received''gc current blocks received'

解決方案：

This SQL outputs the average value (per hours) between the latest two AWR snapshots.

col STAT_NAME for a30with snap_shot as(select  begin_time,SNAP_ID,rank from (select trunc(BEGIN_INTERVAL_TIME,'MI') begin_time,SNAP_ID,rank() over (order by snap_id desc) as rank from DBA_HIST_SNAPSHOT) where rank<3),new as(select * from snap_shot where rank = 1),old as(select * from snap_shot where rank = 2)select stat1.STAT_NAME,stat2.value-stat1.value value,(new.begin_time-old.begin_time)*24 duration_in_hour,    (stat2.value-stat1.value)/((new.begin_time-old.begin_time)*24) value_per_hour from DBA_HIST_SYSSTAT stat1, DBA_HIST_SYSSTAT stat2,new,oldwhere stat1.snap_id=old.snap_id  and stat2.snap_id=new.snap_id  and stat1.STAT_NAME=stat2.STAT_NAME  and stat1.STAT_NAME in ('redo size','physical reads','physical writes','session logical reads','user calls',  'parse count (hard)','gcs messages sent','ges messages sent','gc cr blocks received','gc current blocks received')  order by stat1.STAT_NAME;

 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

Sample Output:

SQL> col STAT_NAME for a30SQL> with snap_shot as  2  (  3  select begin_time,SNAP_ID,rank from (  4  select trunc(BEGIN_INTERVAL_TIME,'MI') begin_time,SNAP_ID,rank() over (order by snap_id desc) as rank from DBA_HIST_SNAPSHOT  5  ) where rank<3  6  ),  7  new as  8  (select * from snap_shot where rank = 1),  9  old as 10  (select * from snap_shot where rank = 2) 11  select stat1.STAT_NAME,stat2.value-stat1.value value,(new.begin_time-old.begin_time)*24 duration_in_hour, 12  (stat2.value-stat1.value)/((new.begin_time-old.begin_time)*24) value_per_hour 13  from DBA_HIST_SYSSTAT stat1, DBA_HIST_SYSSTAT stat2,new,old 14  where stat1.snap_id=old.snap_id 15  and stat2.snap_id=new.snap_id 16  and stat1.STAT_NAME=stat2.STAT_NAME 17  and stat1.STAT_NAME in ('redo size','physical reads','physical writes','session logical reads','user calls', 18  'parse count (hard)','gcs messages sent','ges messages sent','gc cr blocks received','gc current blocks received') 19  order by stat1.STAT_NAME;STAT_NAME                           VALUE DURATION_IN_HOUR VALUE_PER_HOUR------------------------------ ---------- ---------------- --------------gc cr blocks received                   0                1              0gc current blocks received              0                1              0gcs messages sent                       0                1              0ges messages sent                       0                1              0parse count (hard)                      0                1              0physical reads                          7                1              7physical writes                       377                1            377redo size                          730992                1         730992session logical reads               16159                1          16159user calls                             38                1             3810 rows selected.SQL>

 

 

【轉自mos文章】使用單條sql來查詢出awr中的syatem statistics