0racle深度学习笔记- 12C的AWR初步解读

34.0racle深度学习笔记——12C的AWR初步解读关于AWR,蛤蟆也经常看。因为经常看别人给出的建议,很难有深刻体会。对此,计划花费几个晚上时间好好体会一把并记录下来。此处以单实例为例。列出目录便于整体理解。目录TOC\o"1-5"\h\z\o"CurrentDocument". 快照生成方法 3\o"CurrentDocument". 生成AWR报告 3\o"CurrentDocument".系统描述 5\o"CurrentDocument".报告汇总 6» 负载信息 6\o"CurrentDocument".等待事件统计 7时间模型统计 7操作系统统计 7操作系统统计详细 7前台等待类 7前台等待事件 7后台等待事件 7等待事件直方图 7等待事件直方图的详细（64ms到2s） 7等待事件直方图的详细（4ms到2min） 7等待事件直方图的详细（4ms到!hr） 7服务统计 7通过执行时间排序 8通过CPU时间排序 8通过USER10排序 8通过Gets排序 8通过Reads排序 8通过物理读（无优化）排序 8通过执行数量排序 8通过解析调用排序 8通过共享内存排序 8通过打开的子游标排序 8SQL文本的完整列表 8\o"CurrentDocument".实例活动统计 8关键实例活动统计 8实例活动统计 8实例活动统计的绝对值 9实例活动统计的进程活动 9. 10状态 9函数引起的10 9文件类型引起的I〇 9函数或文件类型的10 9表空间的10 9文件的10 9\o"CurrentDocument".缓存池统计 9\o"CurrentDocument"数据库建议统计 9实例汇通统计 10MTTR建议 10缓存池建议 10PGA命中汇总 10PGA命中统计 10PGA命中直方图 10PGA内存建议 10共享库建议 10SGA建议 10流池建议 10JAVAPOOL建议 10缓存等待统计 10队列等待活动 10\o"CurrentDocument"回滚统计 10回滚段汇总 11回滚段统计 11\o"CurrentDocument"Latch统计 11LACTH活动 11LATCH丢失源 11父Latch统计 11子Latch统计 11\o"CurrentDocument"段信息统计 11段的逻辑读 11段的物理读 11段的物理读请求 11段的未优化读 11段的优化读 12段的直接物理读 12段的物理写 12段的物理写请求 12段的直接物理写 12表扫描 12块改变 12行锁等待 12ITL等待 12BufferBusy等待 12\o"CurrentDocument"字典CACHE统计 12库缓存统计 12内存统计 12内存动态组件 12内存组件大小重设置操作汇总 12进程内存汇总 13SGA内存汇总 13SGA中组件变化 13\o"CurrentDocument"复制统计（X流,GoldenGate） 13\o"CurrentDocument"流统计 13\o"CurrentDocument"资源限制统计 13\o"CurrentDocument"共享服务统计 13\o"CurrentDocument"启动参数汇总 13\o"CurrentDocument"ASH报告 14\o"CurrentDocument"ADDM执行 1412C完整AWR报告 14L快照生成方法如下:SQL>execdbms_workload_repository.create_snapshot();2.生成AWR报告如下:SQL>@$ORACLE_HOME/rdbms/admin/awrrpt.sqlCurrentInstanceDBIdDBNameInstNumInstance53626287TOADDB1toaddbSpecifytheReportTypeAWRreportscanbegeneratedinthefollowingformats.Pleaseenterthenameoftheformatattheprompt.Defaultvalueis'html'.'html'HTMLformat(default)'texゼTextformat,active-html'IncludesPerformanceHubactivereportEntervalueforreport_type:htmlTypeSpecified:htmlInstancesinthisWorkloadRepositoryschemaDBIdInstNumDBNameInstanceHost*53626287 1TOADDBtoaddbOELUsing 53626287fordatabaseIdUsing 1forinstancenumberSpecifythenumberofdaysofsnapshotstochoosefrom~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~Enteringthenumberofdays(n)willresultinthemostrecent(n)daysofsnapshotsbeinglisted.Pressing<return>withoutspecifyinganumberlistsallcompletedsnapshots.Entervaluefornum_days:lListingthelastday'sCompletedSnapshotsSnapInstanceDBName SnapIdSnapStartedLeveltoaddbTOADDB10209Mar201616:48 110309Mar201617:19 1SpecifytheBeginandEndSnapshotIdsEntervalueforbegin_snap:102BeginSnapshotIdspecified:102Entervalueforend_snap:103EndSnapshotIdspecified:103SpecifytheReportName~~~~~~~~~~~~~~~~~~~~~~~Thedefaultreportfilenameisawrrpt__l_102_103.html.Tousethisname,press<return>tocontinue,otherwiseenteranalternative.Entervalueforreport_name:test__awr.htmlUsingthereportnametest_awr.html然后在当前目录下生成一个test_awr.html的awr报告。.系统描述DBNameDBIdInstanceInstnumStartupTimeReleaseRACTOADDB 53626287toaddb 109-Mar-1616:03.0 NOHostNamePlatformCPUsCoresSocketsMemory(GB)OEL Linuxx8664-bit 1 3.61SnapIdSnapTimeSessionsCursors/Session jBeginSnap: 10209-Mar-1616:48:00331.0EndSnap: 10309-Mar-1617:19:01321.1Elapsed:31.01(mins)DBTime:0.10(mins)描述了数据的ID,数据库名字,启动时间，版本，快照号,运行的版本,CPU数量,内存大小,快照抓取时间,开始的会话数量,结束时候的会话数量,每个会话的游标数量等。.报告汇总•负载信息・实例命中TOP10等待的前台事件按等待事件的等待类主机系统CPU实例的CPU10负载总体内存统计总体CACHAE大小共享池统计5.等待事件统计时间模型统计操作系统统计操作系统统计详细前台等待类前台等待事件后台等待事件等待事件直方图等待事件直方图的详细（64ms到2s）等待事件直方图的详细（4ms至リ2min）等待事件直方图的详细（4ms到Ihr）服务统计服务等待类统计.SQL统计通过执行时间排序通过CPU时间排序通过USER10排序通过Gets排序通过Reads排序通过物理读（无优化）排序通过执行数量排序通过解析调用排序通过共享内存排序通过打开的子游标排序SQL文本的完整列表.实例活动统计・关键实例活动统计•实例活动统计•实例活动统计的绝对值•实例活动统计的进程活动8.10状态函数引起的10文件类型引起的10函数或文件类型的10表空间的10文件的10.缓存池统计缓冲池中包含默认的和其他块大小的。.数据库建议统计根据负载情况,AWR给DBA的一些建议实例汇通统计MTTR建议缓存池建议PGA命中汇总PGA命中统计PGA命中直方图PGA内存建议共享库建议SGA建议流池建议JAVAPOOL建议11,等待统计缓存等待统计队列等待活动12.回滚统计•回滚段汇总•回滚段统计.Latch统计记录的所有LATCH的统计信息LACTH活动LATCH睡眠中断LATCH丢失源Mutex睡眠汇总父Latch统计子Latch统计.段信息统计段的逻辑读段的物理读段的物理读请求段的未优化读•段的优化读段的直接物理读段的物理写段的物理写请求段的直接物理写表扫描块改变行锁等待ITL等待BufferBusy等待.字典CACHE统计.库缓存统计.内存统计内存动态组件•内存组件大小重设置操作汇总进程内存汇总SGA内存汇总SGA中组件变化18.复制统计（X流,GoldenGate）启用了GoldenGate或XStream后就有记录,蛤蟆这里都是空。19B流统计没有设置就是空了,蛤蟆这里都是空。.资源限制统计资源限制管理,如果有没有设置就是空了,蛉蟆这里都是空。.共享服务统计数据库启动共享模式下オ有记录。如果是专有模式就都是。了。.启动参数汇总修改过的非默认的参数都列出来了,如ド:ParameterNameI Beginvalue IEndvalue(ifdifferent)audit_file_dest/u01/app/oracle/admin/toaddb/adumpaudit_trailDBcompatible.0control_files+DATA/toaddb/control01.ctl,+DATA/toaddb/control02.ctldb_block_size8192db_domaindb_nametoaddbdiagnostic_dest/u01/app/oracledispatchers(PROTOCOL=TCP)(SERVICE=toaddbXDB)localjistenerLISTENER_TOADDBopen_cursors300optimizer_adaptive_featuresFALSEparallel_max_servers8parallel_min_servers0pga_aggregate_target377487360processes300remote_login_passwordfileEXCLUSIVEsession_cached_cursors201sga_target1140850688undo_tablespaceUNDOTBS1utl_file_dir/home/oracle/logminer.ASH报告12C的AWR也包好了ASH报告,不用再手动去生成了。.ADDM执行在12c中,AWR报告自带了ADDM了报告,不用再手动生成了。.12C完整AWR报告WORKLOADREPOSITORYreportforDBNameIDBIdIInstanceIInstnumIStartupTimeIReleaseIRAC

HostNamePlatformCPUsCores1SocketsMemory(GB)OELLinuxx8664-bit13.61|Sn叩(dSnapTimeSessionsCursors/SessionBeginSnap:10209-Mar-1616:48:00331.0EndSnap:10309-Mar-1617:19:01321.1Elapsed:31.01(mins)DBTime:0.10(mins)TOADDB53626287toaddb109-Mar-1616:03.0NOReportSummaryLoadProfilePerSecondPerTransactionPerExecPerCallDBTime(s):0.00.10.000.01DBCPU(s):0.00.00.000.00BackgroundCPU(s):0.00.10.000.00Redosize(bytes):1,679.840,074.2Logicalread(blocks):40.5966.7Blockchanges:5.8138.2Physicalread(blocks):0.819.9Physicalwrite(blocks):0.410.1ReadIOrequests:0.819.5WriteIOrequests:0.25.7ReadIO(MB):0.00.2WriteIO(MB):0.00.1IMscanrows:0.00.0SessionLogicalReadIM:Usercalls:0.410.1Parses(SQL):2.970.0Hardparses(SQL):0.36.0SQLWorkArea(MB):0.11.8Logons:0.12.1Executes(SQL):5.8137.1Rollbacks:0.00.0Transactions:0.0InstanceEfficiencyPercentages(Target100%)

BufferHit%:97.95In-memorySort%:100.00LibraryHit%:89.21SoftParse%:91.37ExecutetoParse%:48.96LatchHit%:100.00ParseCPUtoParseElapsd%:47.06%Non-ParseCPU:50.82FlashCacheHit%:0.00BufferNowait%:100.00RedoNoWait%:100.00Top10ForegroundEventsbyTotalWaitTimeI EventWaitsTotalWaitTime(sec)WaitAvg(ms)%DBtimeWaitClassDBCPU231.2dbfilesequentialread951.313.2520.1UserI/Ologfilesync21147.2115.8CommitDiskfileoperationsI/O399J0.161.0UserI/OcontrolfilesequentialreadSystemI/OCSSinitialization103.25.1Otherlatch:sharedpool101.58.0ConcurrencyCSSoperation:query600.12.0OtherCSSoperation:action100.68.0Otherdirectpathwritetemp200.23.0UserI/OWaitClassesbyTotalWaitTimeWaitClassWaitsTotalWaitTime(sec)AvgWait(ms)%DBtimeAvgActiveSessionsSystemI/O5,881101.72161.50.0UserI/O2,32062.4590.70.0Other89433.4749.60.0DBCPU231.20.0Commit25139.8315.90.0Concurrency40Network37300.00.00.0Application200.10.00.0HostCPUICPUsCoresSocketsILoadAverageBeginLoadAverageEnd%User%System%WIO%ldleI 0.4 1.198.8InstanceCPU

%TotalCPU%BusyCPU%DBtimewaitingforCPU(ResourceManager)0.4 35.7 0.010ProfileRead+WritePerSecondReadperSecondWritePerSecondTotalRequests:DatabaseRequests:OptimizedRequests:0.00.00.0RedoRequests:0.10.00.1Total(MB):0.10.00.0Database(MB):0.00.00.0OptimizedTotal(MB):0.00.00.0Redo(MB):0.00.00.0Database(blocks):ViaBufferCache(blocks):1,20.80.4Direct(blocks):0.00.00.0MemoryStatisticsIBeginEnd |HostMem(MB):3,693.93,693.9SGAuse(MB):1,088.01,088.0PGAuse(MB):140.9140.1%HostMemusedforSGA+PGA:33.2733.25CacheSizesBufferCache:736M736MStdBlockSize:8KSharedPoolSize:240M240MLogBuffer:13.528KIn-MemoryArea:0M0MSharedPoolStatisticsMemoryUsage%:%SQLwithexecutions>1:MemoryUsage%:%SQLwithexecutions>1:%MemoryforSQLw/exec>1:90.4772.3470.9786.4576.6561.66MainReportReportSummaryWaitEventsStatisticsSQLStatisticsInstanceActivityStatisticsIOStatsBufferPoolStatisticsAdvisoryStatisticsWaitStatisticsUndoStatisticsLatchStatisticsSeamentStatisticsDictionaryCacheStatisticsLibraryCacheStatisticsMemoryStatisticsReplicationStatistics(GoldenGate,XStream)StreamsStatisticsResourceLimitStatisticsSharedServerStatisticsinit.oraParametersActiveSessionHistory(ASH)ReportADDMReportsBackt。TopWaitEventsStatisticsTimeModelStatisticsOperating!SystemStatisticsOperatinaSystemStatistics •DetailForegroundWaitClassForearoundWaitEventsBackqroundWaitEventsWaitEventHistoaramWaitEventHistogramDetail(64msecto2sec)WaitEventHistoaramDetail (4secto 2 min)WaitEventHistoqramDetail (4minto 1 hr)ServiceStatisticsServiceWaitClassStatsBacktoTop

TimeModelStatisticsDBTimerepresentstotaltimeinusercallsDBCPUrepresentsCPUtimeofforegroundprocessesTotalCPUTimerepresentsforegroundandbackgroundprocessesStatisticsincludingtheword"background"measurebackgroundprocesstime,thereforedonotcontributetotheDBtimestatisticOrderedby%ofDBtimeindescendingorder,followedbyStatisticNameStatisticNameTime(s)94)ofDBTime%ofTotalCPUTimeIsqlexecuteelapsedtime4.6474.11DBCPU1.9531.1924.43parsetimeelapsed1.8830.05hardparseelapsedtime1.8329.23PL/SQLcompilationelapsedtime0.548.61PL/SQLexecutionelapsedtime0.182.88connectionmanagementcallelapsedtime0.091.38hardparse(sharingcriteria)elapsedtime0.040.63failedparseelapsedtime0.030.48repeatedbindelapsedtime0.000.03sequenceloadelapsedtime0.000.01DBtime6.26backgroundelapsedtime32.35backgroundcputime6.0475.57totalCPUtime7.99BacktoWaitEventsStatisticsBacktoTopOperatingSystemStatistics*TIMEstatisticvaluesarediffed.Allothersdisplayactualvalues.EndValueisdisplayedifdifferentorderedbystatistictype(CPUUse,VirtualMemory,HardwareConfig),NameFREE_MEMORY_BYTESINACTIVE_MEMORY_BYTESFREE_MEMORY_BYTESINACTIVE_MEMORY_BYTESSWAP_FREE_BYTES921,088,000907,710,4641,740,865,5361,696,837,6322,097,147,904StatisticValueEndValue

IDLE_TIME177,120IOWAIT_TIME1,925SYS_TIME705USER_TIME1,518LOAD0PHYSICAL_MEMORY_BYTES3,873,296,384NUM_CPUS1GLOBAL_RECEIVE_SIZE_MAX4,194,304GLOBAL_SEND_SIZE_MAX1,048,586TCP_RECEIVE_SIZE_DEFAULT87,380TCP_RECEIVE_SIZE_MAX6,291,456TCP_RECEIVE_SIZE_MIN4,096TCP_SEND_SIZE_DEFAULT16,384TCP_SEND_SIZE_MAX4,194,304TCP_SEND_SIZE_MIN4,096BUSY_TIME2,240BacktoWaitEventsStatisticsBacktoTopOperatingSystemStatistics-DetailSnapTimeLoadI%busyI%userI%sysI%idleI%iowait09-Mar16:48:000.1609-Mar17:19:010.10 1.25 0.850.3998.75 1.07BacktoWaitEventsStatisticsBacktoTopForegroundWaitClasss-second,ms-millisecond-1000thofasecondorderedbywaittimedesc,waitsdesc%Timeouts:valueof0indicatesvaluewas<.5%.Valueofnullistruly0CapturedTimeaccountsfor69.1%ofTotalDBtime6.26(s)TotalFGWaitTime:2.37(s)DBCPUtime:1.95(s)WaitClass|Waits|%Time-outsTotalWaitTime(s)Avgwait(ms)%DBtime

UserI/O496012.6721.12Commit210147.2115.84SystemI/O217000.240.82Other8000.580.07Concurrency1001.580.03Network233000.000.00Application2000.100.00DBCPU231.19BacktoWaitEventsStatisticsBacktoTopForegroundWaitEventss-second,ms-millisecond-10OOthofasecondOnlyeventswithTotalWaitTime(s)>=.001areshownorderedbywaittimedesc,waitsdesc(idleeventslast)%Timeouts:valueof0indicatesvaluewas<.5%.Valueofnullistruly0EventWaits%Time-outsTotalWaitTime(s)IAvgwait(ms)IWaits/txnI%DBtimedbfilesequentialread95113.251.2220.11logfilesync21147.210.2715.84DiskfileoperationsI/O39901controlfilesequentialread21700.242.780.82CSSinitialization103.250.010.05latch:sharedpool101.580.010.03SQL*Netmessagefromclient2342,52110774.053.00jobqslavewait1,91799955498.3724.58BacktoWaitEventsStatisticsBacktoTopBackgroundWaitEventsorderedbywaittimedesc,waitsdesc(idleeventslast)OnlyeventswithTotalWaitTime(s)>=.001areshown%Timeouts:valueof0indicatesvaluewas<.5%.Valueofnullistruly000000000000000000000989601001001001401000100100500EventIWaits[controlfileparallelwrite618dbfilesequentialread1,558dbfileparallelwrite406oraclethreadbootstrap101logfileparallelwrite243osthreadcreation101controlfilesequentialread3,992DiskfileoperationsI/O205LGWRwaitforredocopy22dbfilescatteredread8directpathwritetemp34CSSinitialization2rowcachelock1localwritewait4logfilesync4directpathwrite2ASMfilemetadataoperation372CSSoperation:query12CSSoperation:action2latch:lobsegmenthashtablelatch1rdbmsipcmessage10,742SpaceManager:slaveidlewait1,221DIAGidlewait3,719sharedserveridlewait62AQPCidle62dispatchertimer31smontimer21ASMbackgroundtimer372pmontimer620heartbeatredoinformer1,859waitforunreadmessageonbroadcast619channelIregtimer619StreamsAQ:qmncoordinatoridlewait132StreamsAQ:qmnslaveidlewait66TotalWaitTime(s)Avgwait(ms)Waits/txnI%bgtime58.727.9216.6642.7019.9713.0036.625.218.31221.021.296.5626.573.124.9319.221.292.8800.1051.181.2800.222.630.1401.140.280.0802.510.100.0600.440.440.0507.160.030.0406.960.010.0201.130.050.0101.090.050.0101.150.030.0100.004.770.0000.740.030.0001.090.010.0027,8142589.30137.725,9184846.7715.653,7191000.0847.681,86030007.410.791,86030004.640.791,86060006.850.401,86088576.390.271,8605000.044.771,8602999.887.951,8601000.4523.831,8593003.477.941,8583001.267.941,84814003.431.691,84828006.820.85175 081 0175 081 00 0.38 2.240 0.68 1.04SQL*NetmessagefromclientclassslavewaitBacktoWaitEventsStatisticsBacktoTopWaitEventHistogramUnitsforTotalWaitscolumn:Kis1000,Mis1000000,Gis1000000000%ofWaits:valueof.0indicatesvaluewas<.05%;valueofnullistruly0%ofWaits:columnheadingof<=1sistruly<1024ms,>1sistruly>=1024msOrderedbyEvent(idleeventslast)%ofWaitsEventTotalWaits<1ms<2ms<4ms<8ms<16ms<32ms<=1sASMfilemetadataoperation372100.0CSSinitialization333.333.333.3CSSoperation:action3100.0CSSoperation:query18100.0DiskfileoperationsI/O60999.2.2,2.5LGWRwaitforredocopy2240.959.1SQL*Netbreak/resettoclient2100.0SQL*Netmessagetoclient373100.0asynchdescriptorresize277100.0bufferbusywaits1100.0controlfileparallelwrite6183.4controlfilesequentialread4216,0.1.0dbfileasyncI/Osubmit406100.0dbfileparallelwrite4064.9dbfilescatteredread875.012.512.5dbfilesequentialread14572.7directpathwrite250.050.0directpathwritetemp36latch:lobsegmenthashtablelatch1100.0latch:sharedpool250.050.0localwritewait475.025.0logfileparallelwrite24339.9

logfilesync2552.032.04.012.0oraclethreadbootstrap1031.951.520.426.2osthreadcreation1037.85.835.01.0rowcachelock1100.0AQPCidle62100.0ASMbackgroundtimer372100.0DIAGidlewait372199.9.1SQL*Netmessagefromclient409SpaceManager:slaveidlewait1.97.6StreamsAQ:qmncoordinatoridlewait13250.050.0StreamsAQ:qmnslaveidlewait66100.0classslavewait831.2dispatchertimer31100.0heartbeatredoinformer186099.9.1jobqslavewait1917.199.9Iregtimer620100.0pmontimer621100.0rdbmsipcmessage10.7K.5.1.1,4.2d35.662.9sharedserveridlewait62100.0smontimer1910.510.552.6waitforunreadmessageonbroadcast621100.0channelBacktoWaitEventsStatisticsBacktoTopWaitEventHistogramDetail(64msecto2sec)UnitsforTotalWaitscolumn:Kis1000,Mis1000000,Gis1000000000Unitsfor%ofTotalWaits:msismillisecondssis1024milliseconds(approximately1second)%ofTotalWaits:totalwaitsforallwaitclasses,includingIdle%ofTotalWaits:valueof.0indicatesvaluewas<.05%;valueofnullistruly0OrderedbyEvent(onlynon-idleeventsaredisplayed)%3TotalWaitsEvent Waits64msto2sI<32ms<64ms<1/8s<1/4s<1/2s<1s<2s>=2scontrolfileparallelwrite 21 96.6 .8 1.1 .5 .8.2controlfilesequentialread 1 100.0 .0

dbfileparallelwrite201.0dbfilesequentialread40.2logfileparallelwrite896.71.6,4.4 .8logfilesync388.04.04.0 4.0oraclethreadbootstrap2773.825.21.0osthreadcreation199.01.0BacktoWaitEventsStatisticsBacktoTopWaitEventHistogramDetail(4secto2min)Nodataexistsforthissectionofthereport,BacktoWaitEventsStatisticsBacktoTopWaitEventHistogramDetail(4minto1hr)Nodataexistsforthissectionofthereport.BacktoWaitEventsStatisticsBacktoTopServiceStatisticsorderedbyDBTimeIServiceNameDBTime(s)DBCPU(s)PhysicalReads(K)LogicalReads(K)lSYS$USERS62023SYS$BACKGROUND00151toaddb0000toaddbXDB0000BacktoWaitEventsStatisticsBacktoTopServiceWaitClassStatsWaitClassinfoforservicesintheServiceStatisticssection.TotalWaitsandTimeWaiteddisplayedforthefollowingwaitclasses:UserI/O,Concurrency,Administrative,NetworkTimeWaited(WtTime)insecondsUserServiceNameI/OTotalWtsUserI/OWtTimeConcurcyTotalWtsConcurcyWtTimeAdminTotalWtsAdminWtTimeNetworkTotalWtsNetworkWtTimeSYS$USERS579510002330SYS$BACKGROUNacktoWaitEventsStatisticsBacktoTopSQLStatisticsSQLorderedbvElapsedTimeSQLorderedbvCPUTimeSQLorderedbvUserI/OWaitTimeSQLorderedbvGetsSQLorderedbvReadsSQLorderedbvPhysicalReads(UnOptimized)SQLorderedbvExecutionsSQLorderedbvParseCallsSQLorderedbvSharableMemorySQLorderedbvVersionCoentCompleteListofSQLTextBacktoTodSQLorderedbyElapsedTimeResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100%Total-ElapsedTimeasapercentageofTotalDBtime%CPU-CPUTimeasapercentageofElapsedTime

%IO-UserI/OTimeasapercentageof