【转自mos文章】使用单条sql来查询出awr中的syatemstatistics

使用单条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. 

#39;redo size#39;
#39;physical reads#39;
#39;physical writes#39;
#39;session logical reads#39;
#39;user calls#39;,
#39;parse count (hard)#39;
#39;gcs messages sent#39;
#39;ges messages sent#39;
#39;gc cr blocks received#39;
#39;gc current blocks received#39;

解决方案：

this sql outputs the average value (per hours) between the latest two awr snapshots.

col stat_name for a30
with snap_shot as
(
select  begin_time,snap_id,rank from (
select trunc(begin_interval_time,#39;mi#39;) begin_time,snap_id,rank() over (order by snap_id desc) as rank from dba_hist_snapshot
) where rank3
),
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,old
where 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 (#39;redo size#39;,#39;physical reads#39;,#39;physical writes#39;,#39;session logical reads#39;,#39;user calls#39;,
  #39;parse count (hard)#39;,#39;gcs messages sent#39;,#39;ges messages sent#39;,#39;gc cr blocks received#39;,#39;gc current blocks received#39;)
  order by stat1.stat_name;

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

sample output:

sql col stat_name for a30
sql with snap_shot as
  2  (
  3  select begin_time,snap_id,rank from (
  4  select trunc(begin_interval_time,#39;mi#39;) begin_time,snap_id,rank() over (order by snap_id desc) as rank from dba_hist_snapshot
  5  ) where rank3
  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 (#39;redo size#39;,#39;physical reads#39;,#39;physical writes#39;,#39;session logical reads#39;,#39;user calls#39;,
 18  #39;parse count (hard)#39;,#39;gcs messages sent#39;,#39;ges messages sent#39;,#39;gc cr blocks received#39;,#39;gc current blocks received#39;)
 19  order by stat1.stat_name;

stat_name                           value duration_in_hour value_per_hour
------------------------------ ---------- ---------------- --------------
gc cr blocks received                   0                1              0
gc current blocks received              0                1              0
gcs messages sent                       0                1              0
ges messages sent                       0                1              0
parse count (hard)                      0                1              0
physical reads                          7                1              7
physical writes                       377                1            377
redo size                          730992                1         730992
session logical reads               16159                1          16159
user calls                             38                1             38

10 rows selected.

sql

【转自mos文章】使用单条sql来查询出awr中的syatem statistics

原文地址：http://blog.csdn.net/msdnchina/article/details/45424337

以上就是由（软件教程库https://www.itjcku.com/9999/1091340.html）本站为大家整理