oracle数据库性能优化实务32 - 33讲解读报告

Oracle数据库性能优化实务

第16讲：解读AWR报告主讲人：白鳝华章培训网、[]华章培训网本章的目的掌握如何解读AWR报告掌握在AWR报告中的一些主要小节的作用掌握AWR报告中的一些主要等待事件的含义掌握AWR报告中的一些主要统计数据的含义掌握如何在AWR报告中分析TOPSQL掌握如何使用AWR报告中的建议解读AWR报告的作用AWR报告是Oracle数据库性能和运行状态的最全面的展示AWR报告对于数据库整体性能分析十分关键学会解读AWR报告是一个高级DBA的必备功课解读AWR报告所需的基础知识OracleWaitInterface时间模型OracleConcepts中相关的基础知识解读AWR报告的技巧孰能生巧基于基线比对类似系统比对AWR报告头WORKLOADREPOSITORYreportforDBNameDBIdInstanceInstNumReleaseRACHost---------------------------------------------------------------------XXOSS1544217329xxoss2210.2.0.4.0YESrac2SnapIdSnapTimeSessionsCurs/Sess---------------------------------------------BeginSnap:1325716-Nov-0919:00:082975.9EndSnap:1325816-Nov-0920:00:002966.0Elapsed:59.88(mins)DBTime:698.54(mins)关注点：SESSIONS:采样时点的会话数CURS/SESS：采样时点的每个会话打开的CURSOR的数量DBTIME：数据库用于用户会话调用的时间，可反映系统负载情况CACHESIZECacheSizes~~~~~~~~~BeginEnd--------------------BufferCache:24,432M24,432MStdBlockSize:8KSharedPoolSize:4,096M4,096MLogBuffer:14,268K分析SGARESIZEaltersessionsetnls_date_format='yyyy-mm-ddhh24:mi:ss';colcomponentformata40truncate;selectcomponent,oper_type,oper_mode,start_time,end_time,trunc(target_size/1024/1024)targetfromv$sga_resize_ops;

例子COMPONENTOPER_TYPEOPER_MODESTART_TIMEEND_TIME-----------------------------------------------------------------------------------DEFAULTbuffercacheGROWDEFERRED2009-08-2511:52:192009-08-2511:52:19streamspoolSHRINKDEFERRED2009-08-2511:52:192009-08-2511:52:19DEFAULTbuffercacheSHRINKIMMEDIATE2009-08-2511:53:412009-08-2511:53:41streamspoolGROWIMMEDIATE2009-08-2511:53:412009-08-2511:53:41DEFAULTbuffercacheSHRINKIMMEDIATE2009-08-2511:53:422009-08-2511:53:42streamspoolGROWIMMEDIATE2009-08-2511:53:422009-08-2511:53:42streamspoolGROWIMMEDIATE2009-08-2511:53:422009-08-2511:53:42DEFAULTbuffercacheSHRINKIMMEDIATE2009-08-2511:53:422009-08-2511:53:42streamspoolGROWIMMEDIATE2009-08-2511:54:162009-08-2511:54:16DEFAULTbuffercacheSHRINKIMMEDIATE2009-08-2511:54:162009-08-2511:54:16DEFAULTbuffercacheGROWDEFERRED2009-08-2511:57:492009-08-2511:58:04streamspoolSHRINKDEFERRED2009-08-2511:57:492009-08-2511:58:04DEFAULTbuffercacheGROWDEFERRED2009-08-2512:00:172009-08-2512:00:17streamspoolSHRINKDEFERRED2009-08-2512:00:172009-08-2512:00:17streamspoolSHRINKDEFERRED2009-08-2512:03:172009-08-2512:03:17DEFAULTbuffercacheGROWDEFERRED2009-08-2512:03:172009-08-2512:03:17streamspoolSHRINKDEFERRED2009-08-2512:07:212009-08-2512:07:21DEFAULTbuffercacheGROWDEFERRED2009-08-2512:07:212009-08-2512:07:21LoadProfile~~~~~~~~~~~~PerSecondPerTransaction------------------------------Redosize:4,055,059.4233,250.38Logicalreads:143,003.191,172.59Blockchanges:23,643.28193.87Physicalreads:441.323.62Physicalwrites:749.276.14Usercalls:1,957.5816.05Parses:602.774.94Hardparses:0.030.00Sorts:259.332.13Logons:0.220.00Executes:2,517.3820.64 Transactions:121.96%BlockschangedperRead:16.53RecursiveCall%:54.68Rollbackpertransaction%:0.85RowsperSort:409.87InstanceEfficiencyPercentages(Target100%)InstanceEfficiencyPercentages(Target100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~BufferNowait%:99.87RedoNoWait%:99.98BufferHit%:99.71In-memorySort%:99.99LibraryHit%:100.02SoftParse%:100.00ExecutetoParse%:76.06LatchHit%:98.98ParseCPUtoParseElapsd%:2.92%Non-ParseCPU:99.97

SharedPoolStatisticsSharedPoolStatisticsBeginEnd------------MemoryUsage%:51.0751.16%SQLwithexecutions>1:97.0696.56%MemoryforSQLw/exec>1:95.4795.04感谢您对华章培训网的支持!. Top5TimedEventsTop5TimedEventsAvg%Total~~~~~~~~~~~~~~~~~~waitCallEventWaitsTime(s)(ms)TimeWaitClass---------------------------------------------------------------------------dbfilesequentialread1,507,66715,6971037.5UserI/OCPUtime12,20329.1logfilesync508,5806,2301214.9CommitStreamscapture:waitingfors1,3081,99615264.8Configuratlogfileparallelwrite523,1671,02922.5SystemI/O-------------------------------------------------------------GlobalCacheLoadProfileGlobalCacheLoadProfile~~~~~~~~~~~~~~~~~~~~~~~~~PerSecondPerTransaction------------------------------GlobalCacheblocksreceived:274.272.25GlobalCacheblocksserved:144.481.18GCS/GESmessagesreceived:1,175.379.64GCS/GESmessagessent:1,128.879.26DBWRFusionwrites:12.700.10EstdInterconnecttraffic(KB)3,800.04GlobalCacheEfficiencyPercentagesGlobalCacheEfficiencyPercentages(Targetlocal+remote100%)~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Bufferaccess-localcache%:99.51Bufferaccess-remotecache%:0.19Bufferaccess-disk%:0.29GlobalCacheandEnqueueServices-WorkloadCharacteristicsGlobalCacheandEnqueueServices-WorkloadCharacteristics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avgglobalenqueuegettime(ms):0.7Avgglobalcachecrblockreceivetime(ms):0.7Avgglobalcachecurrentblockreceivetime(ms):0.7Avgglobalcachecrblockbuildtime(ms):0.0Avgglobalcachecrblocksendtime(ms):0.0Globalcachelogflushesforcrblocksserved%:39.5Avgglobalcachecrblockflushtime(ms):4.1Avgglobalcachecurrentblockpintime(ms):0.8Avgglobalcachecurrentblocksendtime(ms):0.0Globalcachelogflushesforcurrentblocksserved%:1.5Avgglobalcachecurrentblockflushtime(ms):22.5重要指标解释Aveglobalcachegettime(ms)：一个GLOBALCACHE的获取时间，一般来说应该小于5毫秒，如果这个指标超过20毫秒，对系统的性能影响将十分大。Aveglobalcacheconverttime(ms)：这个指标是对一个DATABUFFER的访问权限转换所需要的平均时间，这个指标一般来说应该在4毫秒左右，超过20毫秒说明RAC的性能可能存在问题。AvetimetoprocessCRblockrequest(ms):这个指标统计一个CRBLOCK请求的时间，包含生成CRBLOCK、刷新CRBLOCK和发送CRBLOCK的时间，在Oracle10G的AWR报告里，这个指标被分解为三个部分（Avgglobalcachecrblockbuildtime、Avgglobalcachecrblockflushtime、Avgglobalcachecrblocksendtime），正常范围是在0.1毫秒到1毫秒，超过10毫秒，对系统的性能有较大的影响。AvereceivetimeforCRblock(ms)：一个CRBLOCK从发起请求到收到的时间，一般来说这个指标在0.3毫秒-4毫秒之间，这个指标超过12毫秒，我们就需要关注Avetimetoprocesscurrentblockrequest(ms):处理一个CURRENT状态的BLOCK请求的处理时间。一般情况下指标在3毫秒左右，大于20毫秒的时候就需要关注了。Avereceivetimeforcurrentblock(ms)：一个CURRENT的BLOCK从发起请求到收到的时间。这个指标一般情况下在8毫秒左右，负载较轻的系统可能会小于1毫秒，负载较重的系统，这个指标可能偏高，不过一般情况下，这个指标应该小于30毫秒。GlobalCacheandEnqueueServices-MessagingStatisticsGlobalCacheandEnqueueServices-MessagingStatistics~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Avgmessagesentqueuetime(ms):0.0Avgmessagesentqueuetimeonksxp(ms):0.5Avgmessagereceivedqueuetime(ms):0.0AvgGCSmessageprocesstime(ms):0.0AvgGESmessageprocesstime(ms):0.0%ofdirectsentmessages:49.59%ofindirectsentmessages:31.96%offlowcontrolledmessages:18.45重要指标解释Avgmessagesentqueuetime(ms)：一个非直接传输的逻辑消息在队列中的平均等待时间，正常情况下，这个值应该在0.1毫秒左右，并且不会超过1毫秒Avgmessagesentqueuetimeonksxp(ms)：在IPC层面上发送一个消息到收到ACK回应的时间差，这个指标可以看出IPC的性能。正常情况下这个指标也应该小于1毫秒Avgmessagereceivedqueuetime(ms)：消息收到进入队列到开始处理的时间，一般来说这个时间越长说明等待处理的消息越多，一般来说这个值应该在0.1毫秒左右，甚至更低AvgGCSmessageprocesstime(ms)：平均处理一个BUFFER相关的GCS请求的时间，一般这个指标小于0.5毫秒。AveGESmessageprocesstime(ms)：平均处理一个锁操作消息的时间，一般来说这个指标小于0.5毫秒。TimeModelStatisticsStatisticNameTime(s)%ofDBTime------------------------------------------------------------------------sqlexecuteelapsedtime31,563.475.3DBCPU12,203.129.1PL/SQLexecutionelapsedtime2,816.96.7parsetimeelapsed68.5.2connectionmanagementcallelapsedtime12.5.0hardparseelapsedtime2.6.0repeatedbindelapsedtime1.1.0sequenceloadelapsedtime0.4.0hardparse(sharingcriteria)elapsedtime0.2.0failedparseelapsedtime0.0.0PL/SQLcompilationelapsedtime0.0.0DBtime41,912.2N/Abackgroundelapsedtime4,886.0N/Abackgroundcputime1,428.3N/A-------------------------------------------------------------WaitClass

Avg%TimeTotalWaitwaitWaitsWaitClassWaits-outsTime(s)(ms)/txn--------------------------------------------------------------------------UserI/O1,586,311.016,229103.6Commit508,580.06,230121.2SystemI/O1,305,402.02,22823.0Configuration4,59420.42,0954560.0Cluster1,891,580.01,81514.3Network6,344,952.01,495014.5Other1,183,92150.91,42212.7Concurrency200,260.126910.5Application13,8583.1224160.0-------------------------------------------------------------WaitEvent

Avg%TimeTotalWaitwaitWaitsEventWaits-outsTime(s)(ms)/txn---------------------------------------------------------------------------dbfilesequentialread1,507,667.015,697103.4logfilesync508,580.06,230121.2Streamscapture:waitingfor1,30870.61,99615260.0logfileparallelwrite523,167.01,02921.2gcbufferbusy508,039.085821.2dbfileparallelwrite589,632.078711.3enq:SR-contention17,4407.5666380.0ARCHwaitonSENDREQ29,696.0525180.1readbyothersession56,607.051590.1SQL*Netmoredatatodblink947,828.050312.2gcslogflushsync231,54730.647320.5SQL*Netmoredatafromclien224,985.045920.5LogarchiveI/O33,555.024770.1BackgroundWaitEventsAvg%TimeTotalWaitwaitWaitsEventWaits-outsTime(s)(ms)/txn---------------------------------------------------------------------------logfileparallelwrite523,215.01,02921.2dbfileparallelwrite589,632.078711.3eventsinwaitclassOther1,090,68754.355112.5ARCHwaitonSENDREQ29,696.0525180.1LogarchiveI/O33,555.024770.1dbfilesequentialread55,862.011120.1logfilesequentialread16,081.09160.0controlfilesequentialread52,528.01800.1controlfileparallelwrite6,487.01020.0logfileswitchcompletion184.07390.0gccrmultiblockrequest929.0660.0OperatingSystemStatisticsStatisticTotal----------------------------------------------------NUM_LCPUS0NUM_VCPUS0AVG_BUSY_TIME58,026AVG_IDLE_TIME301,217AVG_IOWAIT_TIME47,980AVG_SYS_TIME8,480AVG_USER_TIME49,421BUSY_TIME1,395,195IDLE_TIME7,232,055IOWAIT_TIME1,154,217SYS_TIME206,241USER_TIME1,188,954LOAD0OS_CPU_WAIT_TIME1,383,500RSRC_MGR_CPU_WAIT_TIME0PHYSICAL_MEMORY_BYTES75,161,927,680NUM_CPUS24NUM_CPU_CORES12ServiceStatisticsPhysicalLogicalServiceNameDBTime(s)DBCPU(s)ReadsReads----------------------------------------------------------------------------xxoss24,556.74,034.81,192,835##########SYS$USERS17,375.38,156.9311,706##########STRMADMIN.HD_APPLY_QU0.00.000STRMADMIN.HQ_CAPTURE_QU0.00.000STRMADMIN.HZ_APPLY_QU0.00.000SYS$BACKGROUND0.00.080,379##########sfossXDB0.00.000ServiceWaitClassStatsServiceName----------------------------------------------------------------UserI/OUserI/OConcurcyConcurcyAdminAdminNetworkNetworkTotalWtsWtTimeTotalWtsWtTimeTotalWtsWtTimeTotalWtsWtTime------------------------------------------------------------------------xxoss119912513248200534087146510SYS$USERS30506128343512506170790097331150491SYS$BACKGROUND82124145286133775002970652540SQLorderedbyElapsedTimeElapsedCPUElapper%TotalTime(s)Time(s)ExecutionsExec(s)DBTimeSQLId--------------------------------------------------------------5,582882210,6180.013.3g82tjz7g9cjcjModule:JDBCThinClientinsertintoAO1_RECORD(A01_TM,NOOBILL_NO,A_CODE,OP_CODE,OP_INFO,B_CODE,A_OPR_CODE,OBJ_TYPE_CODE,C_CODE,Y_FLG,STAY_CODE,PIECE_QTY,A_UPLOAD_TYPE_CODE,QTY,AMT,ACC_CODE,O_INFO,O_NAME,ZONE_CODE,E_STRING,B_SN,SCHInstanceActivityStatsStatisticTotalperSecondperTrans-----------------------------------------------------------------------------CPUusedbythissession1,030,040286.72.4CPUusedwhencallstarted707,330196.91.6CRblockscreated213,78659.50.5CachedCommitSCNreferenced6,5621.80.0CommitSCNcached10.00.0DBtime12,024,0423,346.927.4DBWRcheckpointbufferswritten2,569,456715.25.9DBWRcheckpoints1480.00.0DBWRfusionwrites45,61412.70.1DBWRobjectdropbufferswritten00.00.0DBWRrevisitedbeing-writtenbuf80.00.0DBWRthreadcheckpointbuffersw1,802,168501.64.1感谢您对华章培训网的支持!.InstanceActivityStats-AbsoluteValuesStatisticBeginValueEndValue--------------------------------------------------------------sessioncursorcachecount2,671,2572,677,927openedcursorscurrent1,7481,774workareamemoryallocated87,511,55687,513,485logonscurrent297296InstanceActivityStats-ThreadActivityStatisticTotalperHour-----------------------------------------------------------logswitches(derived)148148.31TablespaceIOStatsTablespace------------------------------AvAvAvAvBufferAvBufReadsReads/sRd(ms)Blks/RdWritesWrites/sWaitsWt(ms)----------------------------------------------------------------------A01_RECORD_I01247,1416913.51.0469,81813134,2071.9A01_NOOBILL_I01235,7726610.61.0480,99413415,9791.8A01_RECORD_D01332,7099310.31.06,142212,1378.2STREAMS_TBS71,790202.01.035,517102590.1FileIOStatsTablespaceFilename----------------------------------------------------------------------------AvAvAvAvBufferAvBufReadsReads/sRd(ms)Blks/RdWritesWrites/sWaitsWt(ms)----------------------------------------------------------------------A01_RECORD_D01/dev/rv_XXXd1_1_10g20,980611.81.043407118.8A01_RECORD_D01/dev/rv_XXXd1_2_10g20,911611.51.069707368.6A01_RECORD_D01/dev/rv_XXXd1_3_10g20,977611.81.069307589.4A01_RECORD_D01/dev/rv_XXXd1_4_10g20,995611.51.0474076111.5A01_RECORD_D01/dev/rv_XXXd1_5_10g20,943611.71.0414068310.3A01_RECORD_D01/dev/rv_XXXd1_6_10g20,537612.01.0357067912.8A01_RECORD_D01/dev/rv_XXXd1_7_10gBufferPoolStatisticsFreeWritBufferNumberofPoolBufferPhysicalPhysicalBuffCompBusyPBuffersHit%GetsReadsWritesWaitWaitWaits------------------------------------------------------------------------D2,930,313100513,126,6101,509,7042,618,33700679,515InstanceRecoveryStatsTargtEstdLogFileLogCkptLogCkptMTTRMTTRRecoveryActualTargetSizeTimeoutInterval(s)(s)EstdIOsRedoBlksRedoBlksRedoBlksRedoBlksRedoBlks----------------------------------------------------------------------B0731546811365065202752020275202757386N/AE0961709331845384202752020275202797272N/ABufferPoolAdvisoryEstPhysSizeforSizeBuffersforReadEstimatedPEst(M)FactorEstimateFactorPhysicalReads---------------------------------------------------------D2,432.1291,6884.41,884,522,143D4,864.2583,3762.91,258,101,527D7,296.3875,0642.2968,644,064D9,728.41,166,7521.9808,378,549D12,160.51,458,4401.6703,726,860D14,592.61,750,1281.5625,478,384D17,024.72,041,8161.3563,387,061D19,456.82,333,5041.2511,992,776D21,888.92,625,1921.1468,874,071D24,3201.02,916,8801.0432,162,668D24,4321.02,930,3131.0430,595,785D26,7521.13,208,5680.9400,871,822D29,1841.23,500,2560.9373,393,743D31,6161.33,791,9440.8349,076,484D34,0481.44,083,6320.8327,693,238D36,4801.54,375,3200.7309,308,005图形化展示PGAAggrSummaryPGACacheHit%W/AMBProcessedExtraW/AMBRead/Written-----------------------------------------------------------98.7123,6331,566重要的警告Warning:pga_aggregate_targetwassettoolowforcurrentworkload,asthisvaluewasexceededduringthisinterval.UsethePGAAdvisoryviewtohelpidentifyadifferentvalueforpga_aggregate_target.PGAAggrTargetStats%PGA%Auto%ManPGAAggrAutoPGAPGAMemW/APGAW/AW/AW/AGlobalMemTarget(M)Target(M)Alloc(M)Used(M)MemMemMemBound(K)---------------------------------------------------------------------B10,24064017,078.91.6.0100.0.01,048,570E10,24064017,371.43.0.0100.0.01,048,570AUTOPGATARGET:自动估算的PGA大小，一般小于PGA参数PGAMemAlloc：实际分配的PGA空间的最大值W/APGAUsed：当前手工分配和自动分配PGA的大小PGAAggrTargetHistogramLowHighOptimalOptimalTotalExecsOptimalExecs1-PassExecsM-PassExecs------------------------------------------------------------------2K4K1,093,4881,093,4880064K128K5,9705,97000128K256K1,9041,90400256K512K4,2804,27640512K1024K144,239144,12711201M2M1,2531,251202M4M5574857204M8M5204774308M16M45044010016M32M1301300032M64M382018064M128M6600PGAMemoryAdvisoryEstdExtraEstdPGAEstdPGAPGATargetSizeW/AMBW/AMBRead/CacheOverallocEst(MB)FactrProcessedWrittentoDiskHit%Count-------------------------------------------------------------------1,2800.143,285,852.41,443,733.397.098,6622,5600.343,285,852.41,258,915.197.090,1795,1200.543,285,852.41,014,195.298.073,2087,6800.843,285,852.4796,843.898.057,49410,2401.043,285,852.4138,262.0100.044,62812,2881.243,285,852.46,783.8100.031,89114,3361.443,285,852.44,854.6100.018,26916,3841.643,285,852.4928.5100.01,28618,4321.843,285,852.40.0100.0020,4802.043,285,852.40.0100.0030,7203.043,285,852.40.0100.00