如何使用AWR报告来诊断数据库性能问题

常见问题:如何使用AWR报告来诊断数据库性能问题[ID1523048.1]修改时间:2013-2-7类型:HOWTO状态:PUBLISHED优先级:1InthisDocumentGoal最佳实践如何主动避免问题发生及做好诊断信息的收集FixInterpretationTop5TimedEventsSQLStatistics分析：OtherSQLStatisticSectionsWaitsfor'Cursor:mutex/pin'LoadProfileInstanceEfficiencyLatchActivity值得注意的waiteventsCPUtimeeventsAnalysis:其他潜在的CPU相关的问题：检查是否有其他等待事件与高CPU事件同时出现数据库以外的CPU使用率过高诊断CPU使用率'Logfilesync'waitsBufferbusywaits诊断其他问题使用ADDM的报告其他的AWR参考文章StatspackReferencesAppliesto:OracleDatabase-EnterpriseEdition-Version10.2.0.1andlaterInformationinthisdocumentappliestoanyplatform.适用于Oracle数据库企业版10.2.0.1及其之后的版本，适用于任何平台。Goal本文旨在提供如何解释跟数据库性能问题息息相关的AWR信息。最佳实践如何主动避免问题发生及做好诊断信息的收集有些问题是无法预见的，但大部分其它的问题如果及早发现一些征兆其实是可以避免的。同时，如果问题确实发生了，那么收集问题发生时的信息就非常重要。有关于如何主动避免问题及诊断信息的收集，请参见：Document1482811.1BestPractices:ProactivelyAvoidingDatabaseandQueryPerformanceIssuesDocument1477599.1BestPracticesAroundDataCollectionForPerformanceIssuesFix对于数据库整体的性能问题，AWR的报告是一个非常有用的诊断工具。一般来说，当检测到性能问题时，我们会收集覆盖了发生问题的时间段的AWR报告-但是最好只收集覆盖1个小时时间段的AWR报告-如果时间过长，那么AWR报告就不能很好的反映出问题所在。还应该收集一份没有性能问题的时间段的AWR报告，作为一个参照物来对比有问题的时间段的AWR报告。这两个AWR报告的时间段应该是一致的，比如都是半个小时的，或者都是一个小时的。关于如何收集AWR报告，请参照如下文档：Document1363422.1AutomaticWorkloadRepository(AWR)Reports-StartPoint注：最好一开始我们从ADDM报告入手，因为对应时间段的ADDM报告往往已经指出了问题所在。参见:UseofADDMReportsalongsideAWRInterpretation在处理性能问题时，我们最关注的是数据库正在等待什么。当进程因为某些原因不能进行操作时，它需要等待。花费时间最多的等待事件是我们最需要关注的，因为降低它，我们能够获得最大的好处。AWR报告中的Top5TimedEvents部分就提供了这样的信息，可以让我们只关注主要的问题。Top5TimedEvents正如前面提到的，Top5TimedEvents是AWR报告中最重要的部分。它指出了数据库的sessions花费时间最多的等待事件，如下：Top5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)TimeWaitClass---------------------------------------------------------------------------dbfilescatteredread10,152,56481,327829.6UserI/Odbfilesequentialread10,327,23175,878727.6UserI/OCPUtime56,20720.5readbyothersession4,397,33033,455812.2UserI/OPXDeqCredit:sendblkd31,39826,5768469.7Other-------------------------------------------------------------Top5Events部分包含了一些跟Events（事件）相关的信息。它记录了这期间遇到的等待的总次数，等待所花费的总时间，每次等待的平均时间；这一部分是按照每个Event占总体calltime的百分比来进行排序的。根据Top5Events部分的信息的不同，接下来我们需要检查AWR报告的其他部分，来验证发现的问题或者做定量分析。等待事件需要根据报告期的持续时间和当时数据库中的并发用户数进行评估。如：10分钟内1000万次的等待事件比10个小时内的1000万等待更有问题；10个用户引起的1000万次的等待事件比10,000个用户引起的相同的等待要更有问题。就像上面的例子，将近60%的时间是在等待IO相关的事件。o事件dbfilescatteredread一般表明正在做由全表扫描或者indexfastfullscan引起的多块读。o事件dbfilesequentialread一般是由不能做多块读的操作引起的单块读（如读索引）其他20%的时间是花在使用或等待CPUtime上。过高的CPU使用经常是性能不佳的SQL引起的（或者这些SQL有可能用更少的资源完成同样的操作）；对于这样的SQL，过多的IO操作也是一个症状。关于CPU使用方面，我们会在之后讨论。在以上基础上，我们将调查是否这个等待事件是有问题的。若有问题，解决它；若是正常的，检查下个等待事件。过多的IO相关的等待一般会有两个主要的原因：o数据库做了太多的读操作o每次的IO读操作都很慢Top5Events部分的显示的信息会帮助我们检查：o是否数据库做了大量的读操作：上面的图显示了在这段时间里两类读操作都分别大于1000万，这些操作是否过多取决于报告的时间是1小时或1分钟。我们可以检查AWR报告的elapsedtime如果这些读操作确实是太多了，接下来我们需要检查AWR报告中SQLStatistics部分的信息，因为读操作都是由SQL语句发起的。o是否是每次的IO读操作都很慢：上面的图显示了在这段时间里两类读操作平均的等待时间是小于8ms的至于8ms是快还是慢取决于底层的硬件设备；一般来讲小于20ms的都可以认为是可以接受的。我们还可以在AWR报告TablespaceIOStats部分得到更详细的信息oTablespaceIOStatsDB/Inst:VMWREP/VMWREPSnaps:1-15oo-orderedbyIOs(Reads+Writes)descooooTablespaceoo------------------------------ooAvAvAvAvBufferAvBufooReadsReads/sRd(ms)Blks/RdWritesWrites/sWaitsWt(ms)oo----------------------------------------------------------------------ooTS_TX_DATAoo14,246,3672837.64.6145,263,8802,8833,844,1618.3ooUSERoo204,834410.71.017,849,02135415,2499.8ooUNDOTS1oo19,72503.01.010,064,0862001,9644.9ooAE_TSoo4,287,567855.46.79320465,7933.7ooTEMPoo2,022,883400.05.8878,0491700.0ooUNDOTS3oo1,310,493264.61.0941,67519430.0ooTS_TX_IDXoo1,884,478377.31.023,695073,7038.3ooSYSAUXoo346,09475.63.9112,744200.0ooSYSTEMo101,77127.93.525,09806532.7如上图，我们关心AvRd(ms)的指标。如果它高于20ms并且同时有很多读操作的，我们可能要开始从OS的角度调查是否有潜在的IO问题。注：对于一些比较空闲的tablespace/files,我们可能会得到一个比较大的AvRd(ms)值；对于这样的情况，我们应该忽略这样的tablespace/files;因为这个很大的值可能是由于硬盘自旋(spin)引起的，没有太大的参考意义。比如对于一个有1000万次读操作而且很慢的系统，引起问题的基本不可能是一个只有10次read的tablespace/file以下的文档可以帮助我们进一步调查IO方面的问题：Note:223117.1TroubleshootingI/O-relatedwaits虽然高dbfilescatteredread和dbfilesequentialread等待可以是I/O相关的问题，但是很多时候这些等待也可能是正常的；实际上，对一个已经性能很好的数据库系统，这些等待事件往往在top5待事件里，因为这意味着您的数据库没有那些真正的“问题”。诀窍是能够评估引起这些等待的语句是否使用了最优的访问路径。如果dbfilescatteredread比较高，那么相关的SQL语句可能使用了全表扫描而没有使用索引（也许是没有创建索引，也许是没有合适的索引）；相应的，如果dbfilesequentialread过多，则表明也许是这些SQL语句使用了selectivity不高的索引从而导致访问了过多不必要的索引块或者使用了错误的索引。这些等待可能说明SQL语句的执行计划不是最优的。接下来就需要通过AWR来检查这些topSQL是否可以进一步的调优，我们可以查看AWR报告中SQLStatistics的部分.上面的例子显示了20%的时间花在了等待或者使用CPU上，我们也需要检查SQLstatistics部分来进一步的分析。需要注意，接下来的分析步骤取决于我们在TOP5部分的发现。在上面的例子里，3个topwaitevent表明问题可能与SQL语句执行计划不好有关，所以接下来我们要去分析SQLStatistics部分。同样的，因为我们并没有看到latch相关的等待，latch在我们这个例子里并没有引发严重的性能问题；那么我们接下来就完全不需要分析latch相关的信息。一般来讲，如果数据库性能很慢，TOP5等待事件里CPU，dbfilesequentialread和dbfilescatteredread比较明显（不管它们之间的顺序如何），我们总是需要检查TopSQL(bylogicalandphysicalreads)部分；调用SQLTuningAdvisor或者手工调优这些SQL来确保它们是有效率的运行。SQLStatisticsAWR包含了一些不同的SQL统计值：根据Top5部分的TopWaitEvent不同，我们需要检查不同的SQLstatistic。在我们这个例子里，TopWaitEvent是dbfilescatteredread，dbfilesequentialread和CPU；我们最需要关心的是SQLorderedbyCPUTime,GetsandReads。我们会从SQLorderedbygets入手，因为引起高buffergets的SQL语句一般是需要调优的对象。SQLorderedbyGets-ResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode