【轉自mos文章】使用單條sql來查詢出awr中的syatem statistics
來源：程序員人生 發布時間：2015-07-01 08:49:08 閱讀次數：4069次
使用單條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 a30
with 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,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 ('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 a30
SQL> 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 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>
生活不易，碼農辛苦
如果您覺得本網站對您的學習有所幫助,可以手機掃描二維碼進行捐贈
