为了对数据库一段时间的性能情况有个全面了解，显然AWR是一个非常有用的工具，
但很多人只会在数据库有性能问题时才会生成问题时段的awr报告去分析。虽然AWR
中给出的统计数据是一段时间的，对于峰值情况把握不准，但是如果把一段时间的
下列度量值做成统计趋势图，大家对系统的空闲、繁忙、异常时段可以一目了然，
也能从趋势变化中提前发现问题。
METRIC2012-10-182012-10-19DIFF ( % )HOST_CPU_UTIL7.43666666676.99916666676USER_TXN_PER_SEC87463936.8656065932LOGFILESYNC_WAIT_MS3.3952.677521.1340206186LOGFILESYNC_TOTAL_WAITS2200393.583333331364391.0833333337.9933165745REDO_MBPS1.711.285833333324.8050682261PHYWTS_REDO_IOPS297.2958333333232.861666666721.6734173312PHYREAD_IOPS1,0011,012-1PHYREAD_TOT_MBPS46445PHYWRITE_TOT_MBPS9.698.400833333313.3040935673PHYWRITE_IOPS528.375439.058333333316.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_Splitsfrom dba_hist_sysmetric_summarygroup by snap_idorder by snap_id;
