深入理解AWR性能报告1

摘自：MacLiu：开Oracle调优鹰眼，深入理解AWR性能报告

 

啥是AWR
AWR (Automatic Workload Repository)
一堆历史性能数据，放在SYSAUX表空间上， AWR和SYSAUX都是10g出现的，是Oracle调优的关键特性； 大约1999年左右开始开发，已经有15年历史
默认快照间隔1小时，10g保存7天、11g保存8天; 可以通过DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS修改
DBA_HIST_WR_CONTROL
AWR程序核心是dbms_workload_repository包
@/rdbms/admin/awrrpt 本实例
@/rdbms/admin/awrrpti RAC中选择实例号

AWR数据从哪里来，放哪里去？

基础指标统计：
SQL和优化器指标
OS指标
等待事件类型
时间指标

 

度量
ASH Active Session History
建议器advisor
快照指标
数据库特性使用情况

 

谁维护AWR
主要是MMON(Manageability Monitor Process)和它的小工进程(m00x) MMON的功能包括:
1.启动slave进程m00x去做AWR快照
2.当某个度量阀值被超过时发出alert告警
3.为最近改变过的SQL对象捕获指标信息

 

AWR小技巧
手动执行一个快照：
Exec dbms_workload_repository.create_snapshot; (这个要背出来哦，用的时候去翻手册，丢脸哦 !)
创建一个AWR基线
Exec DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id，end_snap_id ,baseline_name);
@/rdbms/admin/awrddrpt AWR比对报告
@/rdbms/admin/awrgrpt RAC 全局AWR
自动生成AWR HTML报告：
http://www.oracle-base.com/dba/10g/generate_multiple_awr_reports.sql

select * from v$sysstat

DB TIME

Elapsed:    419.94 (mins)   
DB Time:    561.49 (mins)   

信息量很巨大哦！！
Elapsed快照逝去时间，如果为了诊断特定时段性能问题则不宜过长15分钟~2、3个小时。如果是看全天负载那么可以长一些。
最常见是60分钟后者120分钟，视乎实际需求，无成法。
Cursors/session  open_cursors ORA-100诊断

 

DB TIME= 所有前台session花费在database调用上的总和时间
注意是前台进程foreground sessions
包括CPU时间、IO Time、和其他一系列非空闲等待时间，别忘了cpu on queue time DB TIME 不等于 响应时间，
DB TIME高了未必响应慢，DB TIME低了未必响应快 DB Time描绘了数据库总体负载，但要和elapsed time逝去时间结合其他来。
 Average Active Session AAS= DB time/Elapsed Time DB Time =60 min ， Elapsed Time =60 min AAS=60/60=1
 负载一般 DB Time= 1min , Elapsed Time= 60 min AAS= 1/60
 负载很轻 DB Time= 60000 min，Elapsed Time= 60 min AAS=1000  系统hang了吧？
 
 
 DB TIME= DB CPU + NON-Idle Wait + Wait on CPU queue
DB TIME= DB CPU + Non-Idle Wait + Wait on CPU queue
如果仅有2个逻辑CPU，而2个session在60分钟都没等待事件，一直跑在CPU上，那么：
DB CPU= 2 * 60 mins ， DB Time = 2* 60 + 0 + 0 =120
AAS = 120/60=2 正好等于OS load 2。
如果有3个session都100%仅消耗CPU，那么总有一个要wait on queue
DB CPU = 2* 60 mins ，wait on CPU queue= 60 mins
AAS= (120+ 60)/60=3  主机load 亦为3，此时vmstat 看waiting for run time
真实世界中？ DB Cpu = xx mins ， Non-Idle Wait= enq:TX + cursor pin S on X + latch : xxx + db file sequential read + ……….. 阿猫阿狗

查最近7天的DB Time
WITH sysstat AS (select sn.begin_interval_time begin_interval_time, sn.end_interval_time end_interval_time,
ss.stat_name stat_name, ss.value e_value, lag(ss.value, 1) over(order by ss.snap_id) b_value
 from dba_hist_sysstat ss, dba_hist_snapshot sn where trunc(sn.begin_interval_time) >= sysdate - 7
 and ss.snap_id = sn.snap_id and ss.dbid = sn.dbid and ss.instance_number = sn.instance_number and
 ss.dbid = (select dbid from v$database) and ss.instance_number = (select instance_number from v$instance)
 and ss.stat_name = 'DB time')
  select to_char (BEGIN_INTERVAL_TIME, 'mm-dd hh24:mi') || to_char (END_INTERVAL_TIME, ' hh24:mi') date_time,
 stat_name,
 round((e_value - nvl(b_value, 0)) / (extract(day from(end_interval_time - begin_interval_time)) * 24 * 60 * 60
 + extract(hour from(end_interval_time - begin_interval_time)) * 60 * 60 + extract(minute from(end_interval_time - begin_interval_time)) * 60
 + extract(second from(end_interval_time - begin_interval_time))), 0) per_sec from sysstat where(e_value - nvl(b_value, 0)) > 0 and nvl(b_value, 0) > 0 /
 
 
 
 AAS平均活动会话==与ASH结合
 
 
 Cache Size 兵马未动，缓存先行

内存管理方式：MSMM、ASMM(sga_target)、AMM(memory_target) 小内存有小内存的问题，
 大内存有大内存的麻烦！ ORA-04031!! Buffer cache和shared pool size的 begin/end值
 在ASMM、AMM和11gR2 MSMM下可是会动的哦！ 这里说 shared pool一直收缩，
 则在shrink过程中一些row cache 对象被lock住可能导致前台row cache lock等解析等待，最后别让shared pool shrink。
 如果这里shared pool一直在grow，那说明shared pool原有大小不足以满足需求(可能是大量硬解析)，结合下文的解析信息和SGA breakdown来一起诊断问题。
 
 
 
 
 
 
 
 Load Profile
Redo size 单位 bytes，redo size可以用来估量update/insert/delete的频率，大的redo size往往对lgwr写日志，和arch归档造成I/O压力，
 Per Transaction可以用来分辨是 大量小事务， 还是少量大事务 如上例每秒redo 约1.5MB ，每个事务6k，符合OLTP特征
 
 
  Logical Read单位 次数*块数，
 相当于 “人*次”， 如上例 1579406 * db_block_size=12GB/s ， 逻辑读耗CPU，主频和CPU核数都很重要，逻辑读高则DB CPU往往高，
 也往往可以看到latch: cache buffer chains等待。大量OLTP系统(例如siebel)可以高达几十乃至上百Gbytes。 Block changes 单位 次数*块数 ，
 描绘数据变化频率
 
 Physical Read 单位次数*块数， 如上例 5557 * 8k = 43MB/s， 物理读消耗IO读，体现在IOPS和吞吐量等不同纬度上；
 但减少物理读可能意味着消耗更多CPU。好的存储 每秒物理读能力达到几GB，例如Exadata。
 
 
 
 Physical writes单位 次数*块数，主要是DBWR写datafile，也有direct path write。
 dbwr长期写出慢会导致定期log file switch(checkpoint no complete) 检查点无法完成的前台等待。
  User Calls 单位次数，用户调用数，more details from internal Parses，

解析次数，包括软解析+硬解析，软解析优化得不好，则夸张地说几乎等于每秒SQL执行次数。
   即执行解析比1:1，而我们希望的是 解析一次 到处运行哦！
  
  
   Hard Parses ：万恶之源． Cursor pin s on X， library cache: mutex X ， latch: row cache objects /shared pool……………..。 硬解析最好少于每秒20次
 
  Per Second Per Transaction
Redo size:  98,045.14  2,172.75
Logical reads:  58,656.57  1,299.87
Block changes:  514.49  11.40
Physical reads:  561.61  12.45
Physical writes:  23.91  0.53
User calls:  408.89  9.06
Parses:  77.84  1.73
Hard parses:  5.72  0.13
Sorts:  12.50  0.28
Logons:  0.18  0.00
Executes:  222.50  4.93
Transactions:  45.12 

 

Hard Parses实战案例

硬解析数和 hard parse elapsed time对应， 看一眼Time Model Statistics，即可知该系统是否 是 解析敏感的

等待事件看：
Latch:row cache objects保护row cache state object，例如dc_segments、dc_users

kqrpo parent cache object header
kqrso subordinate cache header
kqrpre() interface to get a row cache object

Dictionary Cache Statistics

Dc_histogram ，字典缓存row cache中的 直方图信息
大量硬解析=》 直方图字典缓存争用
思路：解决硬解析，或者减少dc_histogram争用

 

 

W/A MB processed

W/A MB processed : 单位MB W/A workarea workarea中处理的数据数量
结合 In-memory Sort%， sorts (disk) PGA Aggr一起看

 

Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:  99.99 Redo NoWait %:  100.00
Buffer Hit %:  99.05 In-memory Sort %:  100.00
Library Hit %:  94.85 Soft Parse %:  92.65
Execute to Parse %:  65.01 Latch Hit %:  99.78
Parse CPU to Parse Elapsd %:  17.46 % Non-Parse CPU:  99.84

 

Instance Activity Stats
sorts (disk) 0 0.00 0.00
sorts (memory) 314,922 12.50 0.28
sorts (rows) 21,814,352 865.78 19.19

 

PGA Memory Advisory

pga_aggregate_target过小会导致PGA overalloc过载， 但对于变态的HASH/SORT需求，再大的PGA也达不到cache hit 100%

 

PGA Memory Advisory
When using Auto Memory Mgmt, minimally choose a pga_aggregate_target value where Estd PGA Overalloc Count is 0

 

Load Profile- Logons 登陆

 

Load Profile- Logons 登陆
Logons 登陆次数， logon storm 登陆风暴，结合AUDIT审计数据一起看。短连接的附带效应是游标缓存无用,以下为短连接变长连接后的优化效果

Load Profile-Execute、rollback、transactions
Executes 执行次数，反应执行频率
Rollback 回滚次数， 反应回滚频率， 但是这个指标不太精确，参考而已，别太当真
Transactions 每秒事务数，和数据库层的TPS，可以看做压力测试或比对性能是的一个指标，孤立看无意义。

 

Instance Efficiency Percentages (Target 100%)
Instance Efficiency Percentages (Target 100%)

Buffer Nowait %:  99.99 Redo NoWait %:  100.00
Buffer Hit %:  99.05 In-memory Sort %:  100.00
Library Hit %:  94.85 Soft Parse %:  92.65
Execute to Parse %:  65.01 Latch Hit %:  99.78
Parse CPU to Parse Elapsd %:  17.46 % Non-Parse CPU:  99.84

Instance Efficiency Percentages (Target 100%)
基于命中率的调优方法论已经过时，但仍具有参考价值
全部是越高越好！
Buffer nowait%: session申请一个buffer(兼容模式)不等待的次数比例。
Buffer HIT%: 经典的经典，高速缓存命中率，反应物理读和缓存命中间的纠结，但这个指标即便99% 也不能说明物理读等待少了
Redo nowait%: session在生成redo entry时不用等待的比例，redo相关的资源争用例如redo space request争用可能造成生成redo时需求等待。
此项数据来源于v$sysstat中的redo log space requests/redo entries

 

 

In-Memory Sort%
基于命中率的调优方法论已经过时，但仍具有参考价值
In-memory Sort%:这个指标因为它不计算workarea中所有的操作类型，所以现在越来越鸡肋了。 纯粹在内存中完成的排序比例。
数据来源于v$sysstat statistics sorts (disk) 和 sorts (memory).

 

Instance Efficiency Percentages (Target 100%)
基于命中率的调优方法论已经过时，但仍具有参考价值
Library Hit%: library cache命中率，申请一个library cache object例如一个SQL cursor时，其已经在library cache中的比例。 数据来源 V$librarycache的pins和pinhits。 合理值：>95%
Soft Parse: 软解析比例，无需多说的经典指标，数据来源v$sysstat statistics的parse count(total)和parse count(hard)。 合理值>95%

基于命中率的调优方法论已经过时，但仍具有参考价值
Execute to Parse% 指标反映了执行解析比 其公式为 1-(parse/execute) , 目标为100% 及接近于只 执行而不解析。
 数据来源v$sysstat statistics parse count (total) 和execute count
Latch Hit%: willing-to-wait latch闩申请不要等待的比例。 数据来源V$latch gets和misses
Parse CPU To Parse Elapsd:该指标反映了 快照内解析CPU时间和总的解析时间的比值(Parse CPU Time/ Parse Elapsed Time)；
 若该指标水平很低，那么说明在整个解析过程中 实际在CPU上运算的时间是很短的，而主要的解析时间都耗费在各种其他非空闲的等待事件上了
 (如latch:shared pool,row cache lock之类等) 数据来源 V$sysstat 的 parse time cpu和parse time elapsed
%Non-Parse CPU 非解析cpu比例，公式为 (DB CPU - Parse CPU)/DB CPU， 若大多数CPU都用在解析上了，则可能好钢没用在刃上了。
数据来源 v$sysstat 的 parse time cpu和 cpu used by this session

 

Shared Pool Statistics
反应SQL重用率和共享池中cursor对内存的使用
该环节提供一个大致的SQL重用及shared pool内存使用的评估。 应用是否共享SQL 有多少内存是给只运行一次的SQL占掉的，对比共享SQL呢？
如果该环节中% SQL with executions>1的 比例 小于%90 ， 考虑用下面链接的SQL去抓 硬编码的非绑定变量SQL语句。
利用FORCE_MATCHING_SIGNATURE捕获非绑定变量SQL
http://www.askmaclean.com/archives/%E5%88%A9%E7%94%A8force_matching_signature%E6%8D%95%E8%8E%B7%E9%9D%9E%E7%BB%91%E5%AE%9A%E5%8F%98%E9%87%8Fsql.html

 

 

Top 5 Timed Foreground Events舞会主角
Top 5 万众瞩目，DBA为你倾倒！
基于Wait Interface的调优是目前的主流！每个指标都重要！
基于命中比例的调优，好比是统计局的报告， 张财主家财产100万，李木匠家财产1万， 平均财产50.5万。
基于等待事件的调优，好比马路上100辆汽车的行驶记录表，上车用了几分钟， 红灯等了几分钟，拥堵塞了几分钟。。。
Mysql梦寐以求的东西……

Top 5 万众瞩目，DBA为你倾倒！
CPU 上在干什么？
逻辑读？ 解析？Latch spin PL/SQL、函数运算
DB CPU/CPU time是Top 1 是好事情吗？ 未必！
注意DB CPU不包含 wait on cpu queue！

 

结合Host CPU、Instance CPU、 SQL ordered by CPU Time一起看哦！

 

db file sequential read- Top event
Avg wait time应当小于20ms
”db file sequential read”单块读等待是一种最为常见的物理IO等待事件，
这里的sequential指的是将数据块读入到相连的内存空间中(contiguous memory space)，
而不是指所读取的数据块是连续的。该wait event可能在以下情景中发生:

db file scattered read - Top event
Avg wait time应当小于20ms
常见原因 Fast Full scan Index ， FULL SCAN large table

 

Log file sync蝴蝶效应
Log file sync  enq: TX ，gc buffer busy，buffer busy wait
等待事件的混沌理论，性能不是线性的，而是多纬度的

 

Log file sync实战案例
Log file sync  enq: TX ，gc buffer busy，buffer busy wait , enq:TX index contention
等待事件的混沌理论，性能不是线性的，而是多纬度的

 

log file parallel write慢=> log file sync慢=>commit慢，
commit慢则释放行锁慢。 Rac flush redo也受到写redo慢的影响，
则出现gc buffer busy release/acquire，前后相互作用 enq:TX 大幅出现
http://www.askmaclean.com/archives/db-file-sequential-read-wait-event.html

 

Enq:TX row lock出现在哪里？哪些语句受到GC buffer busy影响？
最主要是update和insert 受影响，前台处理业务速度放慢。方向对了就处处对得上了

 

Global Buffer Busy -> gc buffer busy acquire/release 受影响的segment

 

性能优化的多维度理论
增加了cpu更大的并发量，更多的并发争用
调整了Io存储 更少的IO，更多的CPU计算，更高的cpu使用率
Redo写得慢 影响commit，造成enq:tx和gc buffer busy等待等
Datafile写得慢 检查点完不成，日志无法切换，前台DML hang
Sequence nocache INSERT index很容易造成enq:index contention，和row cache lock和 enq:SQ
通过数据库手段优化了性能 应用本身设计的瓶颈越来越凸显

 

 

 

 

 

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/27573546/viewspace-758154/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/27573546/viewspace-758154/