如何看懂Oracle数据AWR报告.doc

《如何看懂Oracle数据AWR报告.doc》由会员分享，可在线阅读，更多相关《如何看懂Oracle数据AWR报告.doc（110页珍藏版）》请在三一文库上搜索。

1、如何看懂Oracle数据库AWR报告Oracle优化经验总结李申章2014/3/22本来来源于互联网，实际的系统性能优化工作，进行归纳整理，提炼介绍如何学习看懂Oracle数据库AWR报告，作为经验分享，特编写此文。目录一、AWR报告介绍01.AWR使用02.AWR操作1（1）查看当前的AWR保存策略1（2）调整AWR配置23.AWR名词术语3（1）SQL ordered by Elapsed Time3（2）SQL ordered by CPU Time4（3）SQL ordered by Gets4（4）SQL ordered by Reads4（5）SQL ordered by Exec

2、utions:4（6）SQL ordered by Parse Calls4（7）SQL ordered by Sharable Memory4（8）SQL ordered by Version Count4（9）SQL ordered by Cluster Wait Time4二、WORKLOAD REPOSITORY report for5三、Report Summary51.Cache Sizes52.Load Profile6（1）Oracle的硬解析和软解析73.Instance Efficiency Percentages (Target 100%)84.Shared Pool S

3、tatistics105.Top 5 Timed Events11四、RAC Statistics121.Global Cache Load Profile122.Global Cache Efficiency Percentages (Target local+remote 100%)123.Global Cache and Enqueue Services - Workload Characteristics124.Global Cache and Enqueue Services - Messaging Statistics13五、Main Report13六、More RAC Stat

4、istics13七、Wait Events Statistics131.等待事件介绍14（1）db file scattered read 文件分散读取14（2）db file sequential read 文件顺序读取14（3）buffer busy wait 缓冲区忙15（4）常见的等待事件和解决方法252.Time Model Statistics263.Wait Class 等待事件的类型274.Wait Events非空闲等待事件27（1）查询所有等待事件及其属性：27（2）查询Oracle 10gR1提供的12个等待事件类：285.Background Wait Events空闲

5、等待事件326.Operating System Statistics347.Service Statistics358.Service Wait Class Stats359.SQL Statistics35（1）SQL ordered by Elapsed Time36（2）SQL ordered by CPU Time37（3）SQL ordered by Gets37（4）SQL ordered by Reads39（5）SQL ordered by Executions40（6）SQL ordered by Parse Calls41（7）SQL ordered by Sharabl

6、e Memory42（8）SQL ordered by Version Count43（9）SQL ordered by Cluster Wait Time43（10）Complete List of SQL Text4510.Instance Activity Statistics48（1）Instance Activity Stats48（2）Instance Activity Stats - Absolute Values54（3）Instance Activity Stats - Thread Activity5411.IO Stats54（1）Tablespace IO Stats5

7、5（2）File IO Stats55（3）Buffer Pool Statistics5612.Advisory Statistics56（1）Instance Recovery Stats56（2）Buffer Pool Advisory57（3）PGA Aggr Summary57（4）PGA Aggr Target Stats57（5）PGA Aggr Target Histogram58（6）PGA Memory Advisory58（7）Shared Pool Advisory59（8）SGA Target Advisory59（9）Streams Pool Advisory59（

8、10）Java Pool Advisory59（11）Wait Statistics59（12）Buffer Wait Statistics60（13）Enqueue Activity6013.Undo Statistics60（1）Segment Summary61（2）Undo Segment Stats6114.Latch Statistics62（1）Latch Activity65（2）Latch Sleep Breakdown69（3）Latch Miss Sources69（4）Parent Latch Statistics69（5）Child Latch Statistics7

9、015.Segment Statistics70（1）Segments by Logical Reads70（2）Segments by Physical Reads70（3）Segments by Row Lock Waits71（4）Segments by ITL Waits71（5）Segments by Buffer Busy Waits71（6）Segments by Global Cache Buffer Busy71（7）Segments by CR Blocks Received71（8）Segments by Current Blocks Received7116.Dicti

10、onary Cache Statistics72（1）Dictionary Cache Stats72（2）Dictionary Cache Stats (RAC)7317.Library Cache Statistics73（1）Library Cache Activity73（2）Library Cache Activity (RAC)7318.Memory Statistics74（1）Process Memory Summary74（2）SGA Memory Summary74（3）SGA breakdown difference7519.Streams Statistics75（1）

11、Streams CPU/IO Usage76（2）Streams Capture76（3）Streams Apply76（4）Buffered Queues76（5）Buffered Subscribers76（6）Rule Set7620.Resource Limit Stats7621.init.ora Parameters76八、More RAC Statistics771.Global Enqueue Statistics772.Global CR Served Stats793.Global CURRENT Served Stats794.Global Cache Transfer

12、Stats80一、 AWR报告介绍Oracle 10g之前对数据库做性能检测使用Statspack工具。Oracle Database 10g提供了一个显著改进的工具：自动工作负载信息库(AWR:Automatic Workload Repository)。Oracle建议用户用这个取代Statspack。AWR实质上是一个Oracle 的内置工具，它采集与性能相关的统计数据，并从那些统计数据中导出性能量度，以跟踪潜在的问题。与Statspack不同，快照由一个称为MMON的新的后台进程默认自动地每小时采集一次。为了节省空间，采集的数据在7天后自动清除。快照频率和保留时间都可以由用户修改。AW

13、R是通过对比两次快照(Snapshot)收集到的统计信息，来生成报表数据，生成的报表包括多个部分。它产生两种类型的输出：文本格式（类似于Statspack报表的文本格式但来自于AWR信息库）和默认的HTML格式（拥有到部分和子部分的所有超链接），从而提供了非常用户友好的报表。 AWR使用几个表来存储采集的统计数据，所有的表都存储在新的名称为 SYSAUX的特定表空间中的SYS模式下，并且以WRM$_*和WRH$_*的格式命名。前一种类型存储元数据信息（如检查的数据库和采集的快照），后一种类型保存实际采集的统计数据。H代表“历史数据(historical)”而M代表“元数据 (Metadata)

14、在这些表上构建了几种带前缀DBA_HIST_的视图，这些视图可以用来编写您自己的性能诊断工具。视图的名称直接与表相关；例如，视图DBA_HIST_SYSMETRIC_SUMMARY是在WRH$_SYSMETRIC_SUMMARY 表上构建的。 1. AWR使用 获取Oracle数据库的AWR报告，有很多种方法，可以利用专业的Oracle开发管理工具，如Toad、PLSQL等工具软件。也可以使用Oracle自带的Sqlplus命令行管理。个人则更喜欢Sqlplus命令行管理工具，理由是不受操作系统、应用程序的限制，简单易用。SQL?/rdbms/admin/awrrpt.sql 执行上述SQ

15、L语句命令，获取AWR报告的交互界面如下：Specify the Report Type Would you like an HTML report, or a plain text report? Enter html for an HTML report, or text for plain text Defaults to html 输入 report_type 的值: Type Specified: html Specify the number of days of snapshots to choose from Entering the number of days (n) wi

16、ll result in the most recent (n) days of snapshots being listed. Pressing without specifying a number lists all completed snapshots. 输入 num_days 的值: 1 Listing the last days Completed Snapshots Snap Instance DB Name Snap Id Snap Started Level - - - - - orcl10g ORCL10G 142 03 7月 2009 08:11 1 143 03 7月

17、 2009 09:00 1 144 03 7月 2009 10:00 1 145 03 7月 2009 11:00 1 146 03 7月 2009 12:01 1 Specify the Begin and End Snapshot Ids 输入 begin_snap 的值: 142 Begin Snapshot Id specified: 142 输入 end_snap 的值: 146 End Snapshot Id specified: 146 Specify the Report Name The default report file name is awrrpt_1_142_146

18、html. To use this name, press to continue, otherwise enter an alternative. 输入 report_name 的值: D:awrrpt_1_142_146.html Report written to D:awrrpt_1_142_146.html上述介绍的是最简单的使用方式，对于数据库优化、运维管理来说，每次按照上述方式，比较耗费时间和力气，如果熟悉操作系统批处理命令、及Oracle的PLSQL语言开发，那么就可以对上述使用方式稍加修改，就能够按照特定的需求自东导出AWR报告。在运维管理的过程中，曾按照特定的需求，对做了

19、相应的完善，得到了很好的应用效果，这里不做详述，如果感兴趣，欢迎交流经验。2. AWR操作 （1） 查看当前的AWR保存策略 SQL col snap_interval format a20 SQL col retention format a20 SQL select * from dba_hist_wr_control; DBID SNAP_INTERVAL RETENTION TOPNSQL - - - - 262089084 +00000 01:00:00.0 +00007 00:00:00.0 DEFAULT以上结果表示：每小时产生一个SNAPSHOT，保留7天。 （2） 调整AWR

20、配置 AWR配置都是通过dbms_workload_repository包进行配置。 调整AWR产生snapshot的频率和保留策略，如将收集间隔时间改为30 分钟一次。并且保留5天时间（单位都是分钟）：SQLexec dbms_workload_repository.modify_snapshot_settings(interval=30, retention=5*24*60); 关闭AWR，把interval设为0则关闭自动捕捉快照 ：SQL exec dbms_workload_repository.modify_snapshot_settings(interval=0); 手工创建一个

21、快照 SQL exec dbms_workload_repository.create_snapshot(); 查看快照 SQL select * from sys.wrh$_active_session_history; 手工删除指定范围的快照 SQL exec dbms_workload_repository.drop_snapshot_range(low_snap_id = 973, high_snap_id = 999, dbid = 262089084); 创建Baseline，保存这些数据用于将来分析和比较 SQL exec dbms_workload_repository.cre

22、ate_baseline(start_snap_id = 1003, end_snap_id = 1013, apply_interest_1); 删除Baseline SQL exec dbms_workload_repository.drop_baseline(baseline_name = apply_interest_1, cascade = false); 将AWR数据导出并迁移到其它数据库以便于以后分析 SQL exec DBMS_SWRF_INTERNAL.AWR_EXTRACT(dmpfile = awr_data.dmp, mpdir = DIR_BDUMP, bid = 1

23、003, eid = 1013); 迁移AWR数据文件到其他数据库 SQL exec dbms_swrf_internal.awr_load(schname = awr_test, dmpfile = awr_data.dmp, dmpdir = dir_bdump); 把AWR数据转移到SYS模式中： SQL exec dbms_swrf_internal.move_to_awr (schname = test); 3. AWR名词术语 （1） SQL ordered by Elapsed Time记录了执行总和时间的TOP SQL(请注意是监控范围内该SQL的执行时间总和，而不是单次SQL

24、执行时间 Elapsed Time = CPU Time + Wait Time)。 Elapsed Time(S): SQL语句执行用总时长，此排序就是按照这个字段进行的。注意该时间不是单个SQL跑的时间，而是监控范围内SQL执行次数的总和时间。单位时间为秒。Elapsed Time = CPU Time + Wait Time DB Time：是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间。不包括Oracle后台进程消耗的时间。CPU Time(s): 为SQL语句执行时CPU占用时间总时长，此时间会小于等于Elapsed Time时间。单位时间为秒。 Execut

25、ions: SQL语句在监控范围内的执行次数总计。 Elap per Exec(s): 执行一次SQL的平均时间。单位时间为秒。 % Total DB Time: 为SQL的Elapsed Time时间占数据库总时间的百分比。 SQL ID： SQL语句的ID编号，点击之后就能导航到下边的SQL详细列表中，点击IE的返回可以回到当前SQL ID的地方。 SQL Module: 显示该SQL是用什么方式连接到数据库执行的，如果是用SQL*Plus或者PL/SQL链接上来的那基本上都是有人在调试程序。一般用前台应用链接过来执行的sql该位置为空。 SQL Text: 简单的sql提示，详细的需要点

26、击SQL ID。 （2） SQL ordered by CPU Time 记录了执行占CPU时间总和时间最长的TOP SQL(请注意是监控范围内该SQL的执行占CPU时间总和，而不是单次SQL执行时间)。 （3） SQL ordered by Gets 记录了执行占总buffer gets(逻辑IO)的TOP SQL(请注意是监控范围内该SQL的执行占Gets总和，而不是单次SQL执行所占的Gets)。 （4） SQL ordered by Reads记录了执行占总磁盘物理读(物理IO)的TOP SQL(请注意是监控范围内该SQL的执行占磁盘物理读总和，而不是单次SQL执行所占的磁盘物理读)。

27、 （5） SQL ordered by Executions:记录了按照SQL的执行次数排序的TOP SQL。该排序可以看出监控范围内的SQL执行次数。 （6） SQL ordered by Parse Calls记录了SQL的软解析次数的TOP SQL。说到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。 （7） SQL ordered by Sharable Memory记录了SQL占用library cache的大小的TOP SQL。Sharable Mem (b)：占用library cache的大小，单位是byte。 （8

28、 SQL ordered by Version Count 记录了SQL的打开子游标的TOP SQL。 （9） SQL ordered by Cluster Wait Time记录了集群的等待时间的TOP SQL。二、 WORKLOAD REPOSITORY report for DB1314098396DB1110.2.0.5.0YESDB1Begin Snap:267821-mar -14 14:04:50241.5End Snap:268021-mar -14 15:23:37261.5Elapsed:78.79 (mins)DB Time:11.05 (mins)从awr repor

29、t的Elapsed time和DB Time就能大概了解db的负载。DB Time不包括Oracle后台进程消耗的时间。如果DB Time远远小于Elapsed时间，说明数据库比较空闲，反之，则说明数据库比较繁忙。DB Time = cpu time + wait time（不包含空闲等待） （非后台进程）说白了就是db time就是记录的服务器花在数据库运算(非后台进程)和等待(非空闲等待)上的时间。DB time = cpu time + all of nonidle wait event time分析结论：DB数据服务器中显示系统有8个逻辑CPU（4个物理CPU）。在79分钟里收集了3次

30、快照数据，数据库耗时11分钟，平均每个CPU耗时1.4分钟，CPU利用率只有大约2%（1.4/79）。说明系统压力非常小。可是对于批量系统，数据库的工作负载总是集中在一段时间内。如果快照周期不在这一段时间内，或者快照周期跨度太长而包含了大量的数据库空闲时间，所得出的分析结果是没有意义的。这也说明选择分析时间段很关键，要选择能够代表性能问题的时间段。三、 Report Summary1. Cache Sizes Buffer Cache:3,344M3,344MStd Block Size:8KShared Pool Size:704M704MLog Buffer:14,352K显示SGA中每个

31、区域的大小（在AMM改变它们之后），可用来与初始参数值比较。Shared Pool主要包括Library Cache和Dictionary Cache。Library Cache用来存储最近解析（或编译）后SQL、PL/SQL和Java classes等。Library Cache用来存储最近引用的数据字典。发生在Library Cache或Dictionary Cache的Cache Miss代价要比发生在Buffer Cache的代价高得多。因此Shared Pool的设置要确保最近使用的数据都能被Cache。2. Load ProfileRedo size:918,805.72775,9

32、12.72Logical reads:3,521.772,974.06Block changes:1,817.951,535.22Physical reads:68.2657.64Physical writes:362.59306.20User calls:326.69275.88Parses:38.6632.65Hard parses:0.030.03Sorts:0.610.51Logons:0.010.01Executes:354.34299.23Transactions:1.18% Blocks changed per Read:51.62Recursive Call %:51.72Ro

33、llback per transaction %:85.49Rows per Sort:#显示数据库负载概况，将之与基线数据比较才具有更多的意义，如果每秒或每事务的负载变化不大，说明应用运行比较稳定。单个的报告数据只说明应用的负载情况，绝大多数据并没有一个所谓“正确”的值，然而Logons大于每秒12个、Hard parses大于每秒100、全部parses超过每秒300表明可能有争用问题。Redo size：每秒产生的日志大小(单位字节)，可标志数据变更频率, 数据库任务的繁重与否。Logical reads：每秒/每事务逻辑读的块数.平决每秒产生的逻辑读的block数。Logical Re

34、ads= Consistent Gets + DB Block Gets Block changes：每秒/每事务修改的块数Physical reads：每秒/每事务物理读的块数Physical writes：每秒/每事务物理写的块数User calls：每秒/每事务用户call次数Parses：每秒解析SQL次数。包括fast parse，soft parse和hard parse三种数量的综合。软解析每秒超过300次意味着你的应用程序效率不高，调整session_cursor_cache。在这里，fast parse指的是直接在PGA中命中的情况（设置了session_cached_cur

35、sors=n）；soft parse是指在shared pool中命中的情形；hard parse则是指都不命中的情况。Hard parses：其中硬解析的次数，硬解析太多，说明SQL重用率不高。每秒产生的硬解析次数, 每秒超过100次，就可能说明你绑定使用的不好，也可能是共享池设置不合理。这时候可以启用参数cursor_sharing=similar|force，该参数默认值为exact。但该参数设置为similar时，存在bug，可能导致执行计划的不优。Sorts：每秒/每事务的排序次数Logons：每秒/每事务登录的次数Executes：每秒/每事务SQL执行次数Transactions

36、每秒事务数.每秒产生的事务数，反映数据库任务繁重与否。Blocks changed per Read：表示逻辑读用于修改数据块的比例.在每一次逻辑读中更改的块的百分比。Recursive Call：递归调用占所有操作的比率.递归调用的百分比，如果有很多PL/SQL，那么这个值就会比较高。Rollback per transaction：每事务的回滚率.看回滚率是不是很高，因为回滚很耗资源 ,如果回滚率过高,可能说明你的数据库经历了太多的无效操作 ,过多的回滚可能还会带来Undo Block的竞争。该参数计算公式如下: Round(User rollbacks / (user commits

37、 user rollbacks) ,4)* 100% 。Rows per Sort：每次排序的行数（1） Oracle的硬解析和软解析 提到软解析(soft prase)和硬解析(hard prase)，就不能不说一下Oracle对sql的处理过程。当你发出一条sql语句交付Oracle，在执行和获取结果前，Oracle对此sql将进行几个步骤的处理过程：1、语法检查(syntax check)检查此sql的拼写是否语法。2、语义检查(semantic check)诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。3、对sql语句进行解析(prase)利用内部算法对sql进行

38、解析，生成解析树(parse tree)及执行计划(execution plan)。4、执行sql，返回结果(execute and return)其中，软、硬解析就发生在第三个过程里。Oracle利用内部的hash算法来取得该sql的hash值，然后在library cache里查找是否存在该hash值；假设存在，则将此sql与cache中的进行比较；假设“相同”，就将利用已有的解析树与执行计划，而省略了优化器的相关工作。这也就是软解析的过程。诚然，如果上面的两个假设中任有一个不成立，那么优化器都将进行创建解析树、生成执行计划的动作。这个过程就叫硬解析。创建解析树、生成执行计划对于sql的执

39、行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。如何提高SQL解析效率，最有效的是SQL语句尽量使用变量绑定的书写方式。3. Instance Efficiency Percentages (Target 100%) Buffer Nowait %:100.00Redo NoWait %:100.00Buffer Hit %:98.72In-memory Sort %:99.86Library Hit %:99.97Soft Parse %:99.92Execute to Parse %:89.09Latch Hit %:99.99Parse CPU to Parse Ela

40、psd %:7.99% Non-Parse CPU:99.95本节包含了Oracle关键指标的内存命中率及其它数据库实例操作的效率。其中Buffer Hit Ratio 也称Cache Hit Ratio，Library Hit ratio也称Library Cache Hit ratio。同Load Profile一节相同，这一节也没有所谓“正确”的值，而只能根据应用的特点判断是否合适。在一个使用直接读执行大型并行查询的DSS环境，20%的Buffer Hit Ratio是可以接受的，而这个值对于一个OLTP系统是完全不能接受的。根据Oracle的经验，对于OLTPOLAP:联机分析处理OL

41、TP:联机事务处理OLAP是主要应用数据仓库系统OLTP是一般的项目开发用到的基本的、日常的事务处理；比如数据库记录的增、删、改、查。系统，Buffer Hit Ratio理想应该在90%以上。Buffer Nowait：表示在内存获得数据的未等待比例。在缓冲区中获取Buffer的未等待比率。Buffer Nowait的这个值一般需要大于99%。否则可能存在争用，可以在后面的等待事件中进一步确认。Buffer Hit：表示进程从内存中找到数据块的比率，监视这个值是否发生重大变化比这个值本身更重要。对于一般的OLTP系统，如果此值低于80%，应该给数据库分配更多的内存。数据块在数据缓冲区中的命中

42、率，通常应在95%以上。否则，小于95%，需要调整重要的参数，小于90%可能是要加db_cache_size。一个高的命中率，不一定代表这个系统的性能是最优的，比如大量的非选择性的索引被频繁访问，就会造成命中率很高的假相（大量的db file sequential read），但是一个比较低的命中率，一般就会对这个系统的性能产生影响，需要调整。命中率的突变，往往是一个不好的信息。如果命中率突然增大，可以检查top buffer get SQL，查看导致大量逻辑读的语句和索引，如果命中率突然减小，可以检查top physical reads SQL，检查产生大量物理读的语句，主要是那些没有使用索

43、引或者索引被删除的。Redo NoWait：表示在LOG缓冲区获得BUFFER的未等待比例。如果太低（可参考90%阀值），考虑增加LOG BUFFER。当redo buffer达到1M时，就需要写到redo log文件，所以一般当redo buffer设置超过1M，不太可能存在等待buffer空间分配的情况。当前，一般设置为2M的redo buffer，对于内存总量来说，应该不是一个太大的值。Library Hit：表示Oracle从Library Cache中检索到一个解析过的SQL或PL/SQL语句的比率，当应用程序调用SQL或存储过程时，Oracle检查Library Cache确定是否

44、存在解析过的版本，如果存在，Oracle立即执行语句；如果不存在，Oracle解析此语句，并在Library Cache中为它分配共享SQL区。低的library hit ratio会导致过多的解析，增加CPU消耗，降低性能。如果library hit ratio低于90%，可能需要调大shared pool区。STATEMENT在共享区的命中率，通常应该保持在95%以上，否则需要要考虑：加大共享池；使用绑定变量；修改cursor_sharing等参数。Latch Hit：Latch是一种保护内存结构的锁，可以认为是SERVER进程获取访问内存数据结构的许可。要确保Latch Hit99%，否则意味着Shared Pool latch争用，可能由于未共享的SQL，或者Library Cache太小，可使用绑定变更或调大Shared Pool解决。要确保