使用SQL从AWR收集数据库性能变化趋势
为了对数据库一段时间的性能情况有个全面了解，显然AWR是一个非常有用的工具，
但很多人只会在数据库有性能问题时才会生成问题时段的awr报告去分析。虽然AWR
中给出的统计数据是一段时间的，对于峰值情况把握不准，但是如果把一段时间的
下列度量值做成统计趋势图，大家对
系统的空闲、繁忙、异常时段可以一目了然，
也能从趋势变化中提前发现问题。
|
1
2
3
4
5
6
7
8
9
10
11
12
|
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
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
|
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;
|