使用SQL从AWR收集数据库性能变化趋势

为了对数据库一段时间的性能情况有个全面了解，显然AWR是一个非常有用的工具，

但很多人只会在数据库有性能问题时才会生成问题时段的awr报告去分析。虽然AWR

中给出的统计数据是一段时间的，对于峰值情况把握不准，但是如果把一段时间的

下列度量值做成统计趋势图，大家对系统的空闲、繁忙、异常时段可以一目了然，

也能从趋势变化中提前发现问题。

 
METRIC
2012-10-18
2012-10-19
DIFF ( % )
HOST_CPU_UTIL
7.4366666667
6.9991666667
6
USER_TXN_PER_SEC
874
639
36.8656065932
LOGFILESYNC_WAIT_MS
3.395
2.6775
21.1340206186
LOGFILESYNC_TOTAL_WAITS
2200393.58333333
1364391.08333333
37.9933165745
REDO_MBPS
1.71
1.2858333333
24.8050682261
PHYWTS_REDO_IOPS
297.2958333333
232.8616666667
21.6734173312
PHYREAD_IOPS
1,001
1,012
-1
PHYREAD_TOT_MBPS
46
44
5
PHYWRITE_TOT_MBPS
9.69
8.4008333333
13.3040935673
PHYWRITE_IOPS
528.375
439.0583333333
16.9040296507

大家可以将AVERAGE 更改为 MAXVAL 了解峰值情况，

可以参考另外SQL，从AWR 中发现异常SQL DIFF AWR TOPSQL

     
select max(end_time) time,      
trunc(sum(case metric_name when                 'Host CPU Utilization (%)' then average end),2) Host_CPU_util,
trunc(sum(case metric_name when                          'Current OS Load' then average end),2) OS_LOAD,  
trunc(sum(case metric_name when                    'Database Time Per Sec' then average end),2) Database_Time,
trunc(sum(case metric_name when                 'User Transaction Per Sec' then average end),2) User_Txn_Per_Sec,
trunc(sum(case metric_name when                       'Executions Per Sec' then average end),2) Executions_ptx,
trunc(sum(case metric_name when                    'Response Time Per Txn' then average end),2) Response_Time_ptx,
trunc(sum(case metric_name when                   'Redo Generated Per Sec' then average end)/1024/1024,2) Redo_MBps,
trunc(sum(case metric_name when                      'Redo Writes Per Sec' then average end),2) PhyWts_redo_IOPS,
trunc(sum(case metric_name when  'Physical Read Total IO Requests Per Sec' then average end),2) PhyRead_IOPS,
trunc(sum(case metric_name when 'Physical Write Total IO Requests Per Sec' then average end),2) Phywrite_IOPS,
trunc(sum(case metric_name when       'Physical Write Total Bytes Per Sec' then average end)/1024/1024,2) Phywrite_Tot_MBps,
trunc(sum(case metric_name when      'Physical Read Total Bytes Per Sec' then average end)/1024/1024,2) PhyRead_Tot_MBps,
trunc(sum(case metric_name when                           'Logons Per Sec' then average end),2) Logons_PS,
trunc(sum(case metric_name when                     'User Commits Per Sec' then average end),2) User_Commit_Per_Sec,
trunc(sum(case metric_name when                   'User Rollbacks Per Sec' then average end),2) User_Rollback_Per_Sec,
trunc(sum(case metric_name when                    'Logical Reads Per Txn' then average end),2) Logical_Rds_PTxn,
trunc(sum(case metric_name when                 'Hard Parse Count Per Sec' then average end),2) HardParse_Per_Sec,
trunc(sum(case metric_name when           'Network Traffic Volume Per Sec' then average end/1024/1024),2) Network_Mbps,
trunc(sum(case metric_name when                    'Enqueue Waits Per Sec' then average end),2) Enq_wait_Per_Sec,
trunc(sum(case metric_name when                 'DB Block Changes Per Sec' then average end),2) BLOCK_CHG_Per_Sec,
trunc(sum(case metric_name when                 'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits_ps,
trunc(sum(case metric_name when                          'Process Limit %' then average end),2) Process_Limit,
trunc(sum(case metric_name when                          'Session Limit %' then average end),2) Session_Limit,
trunc(sum(case metric_name when                       'Shared Pool Free %' then average end),2) Shared_Pool_Free_PCT,            
trunc(sum(case metric_name when                   'Buffer Cache Hit Ratio' then average end),2) Buffer_CACHE_HIT_RAT,
trunc(sum(case metric_name when                       'Memory Sorts Ratio' then average end),2) Memory_Sorts_Ratio,
trunc(sum(case metric_name when                'Redo Allocation Hit Ratio' then average end),2) Redo_Allocation_Hit_Rat,
trunc(sum(case metric_name when                  'Library Cache Hit Ratio' then average end),2) Library_Cache_Hit_Rat,
trunc(sum(case metric_name when                         'Soft Parse Ratio' then average end),2) Soft_Parse_Rat, 
trunc(sum(case metric_name when                   'Cursor Cache Hit Ratio' then average end),2) Cursor_Cache_Hit_Rat,
trunc(sum(case metric_name when                   'Leaf Node Splits Per Sec' then average end),2) Leaf_Node_Splits
from dba_hist_sysmetric_summary
group by snap_id
order by snap_id;