ORACLE性能AWR報(bào)告的使用和分析

1、ORACLE性能診斷AWR報(bào)告的使用和分析為滿足業(yè)務(wù)的運(yùn)行要求，高性能要求是目前系統(tǒng)普遍面臨的最棘手問(wèn)題，尤其是客戶面對(duì)著目前越來(lái)越龐大系統(tǒng)和數(shù)據(jù)，系統(tǒng)整合、數(shù)據(jù)大集中似乎成了趨勢(shì)。針對(duì)系統(tǒng)性能優(yōu)化的診斷和分析，數(shù)據(jù)庫(kù)方向又是其中的重要一環(huán)，本文將針對(duì)中常用的性能診斷工具報(bào)告，進(jìn)行分析說(shuō)明。一、性能診斷工具ORACLE9iStatspack10gORACLE進(jìn)建議越來(lái)越自動(dòng)化，不過(guò)能夠熟悉并掌握ORACLE的相關(guān)性能診斷工具的使用，仍對(duì)性能問(wèn)題的ORACLE動(dòng)態(tài)性能視圖動(dòng)態(tài)性能視圖是ORACLE中最常用，也是最簡(jiǎn)單的一種工具。無(wú)論何種性能問(wèn)題，都能在10gv$session、v$session

2、_wait、v$process、v$sql、v$lock、v$latch、v$sysstat、v$system_event、v$sgastat報(bào)告statspackOracle9iSQLI/OSGA和報(bào)告AWR10gOracleStatspackADDM執(zhí)行計(jì)劃和建議ORACLESQL代碼SQL知道SQL執(zhí)行的生成報(bào)告AWRoracle10gAWR由ORACLE自動(dòng)產(chǎn)生，默認(rèn)30DBMS_WORKLOAD_REPOSITORYawrrpt.sqlawrrpti.sqlAWR$ORACLE_HOME/rdbms/adminHTMLAWRsqlplussys/sys/scmisassysdbaSQ

3、Lc:/oracle/product/10.2.4/db_1/RDBMS/ADMIN/awrrpt.sqlreport_type:htmlnum_daysbegin_snapend_snapreport_name值:1（注：選擇快照的天數(shù)）值:425（注：起始快照）:427（注：結(jié)束快照）:d:scmis-awr-2011-10-29.html報(bào)告分析AWRElapsedDBTimeDBDBTime/Elapsed16CPU(2*8)cpu26.7min26.7/60.03=44.5%WORKLOADREPOSITORYreportforDBhlameDBIdInstanceInstnumR&

4、leaseRACHostPWSCDB3613733412pwscdb11.0YESpwscdt01SnspIdSnapTimeSessionsCursors/SessionBeginSnap:59118-Oct-H10:00:274823.4EndSnap:592-IS-Od-1-111:00:292972.5Elapsed:60.03(mns)BTirne:4-27.44-(mins)AWRCacheSizesLoadProfileInstanceEfficiencyPercentagesSharedPoolStatisticsTOP5共享池統(tǒng)計(jì)()數(shù)據(jù)庫(kù)效率（）nSQLlibrarycac

5、heTop5TimedEvents緩存尺寸（)BufferCacheSGASharedPoolSizeSGAORACLESGASharedpooldictionarycacheLibrarycachedictionarycacheSQLSQLCacheSizesBeginEndBufferCache:4.4,800m44,8OOMStdBlocIcSize:BKS-haredPool20640M20.640MLogBuffer:U.220K負(fù)載性能（rLogons12Hardparses每秒100，parses300SQLSQLcursor_sharingSorts每秒100，表明排序過(guò)多，需

6、SQLLoadProfilePerSecondPerTransectionRedosize:5,022.591,21377Logicalreads:37,410409,040.68Blockchanges:34.018.22Physicalreads:19.104.62Physicalwrites:1.200.29sercalls:243.9558.95Parses;S1.2419.63Hardparse.s:5.391.30S-orts:12583.04Logons;0.770.19Executes:79.5319.22Transactions:4.14OracleInstanceEffic

7、iencyPercentages(Target100%BufferNowait%:100.00Redo陽(yáng)/Wait喘:100.00BufferHit%:100.05In-memorySort隔：100.00LibraryHit%:S-8.39SoftParse%:93-.36曰它匚utetaParse%:-2.14LatchHit%:99.07ParseCFUtoParseElapsd%:39.02%Non-ParseCPU:97.6&共享池統(tǒng)計(jì)()數(shù)據(jù)庫(kù)效率（）n共享池統(tǒng)計(jì)()數(shù)據(jù)庫(kù)效率（）n(buffernowait%)值應(yīng)接近100%，如果該值較低，則可能要redo(redonowait

8、%)該指標(biāo)的值應(yīng)接近100%，如果該值較低，2log(bufferhit%)90%以上，否則，需要調(diào)整。如果持續(xù)小于cache(in-memorysort%)100%，如果指標(biāo)的值較低，則表示出現(xiàn)sort_area_size(libraryhit%)在95%以上，否則需要考慮加大共享池(修cursor_sharing(softparse%)百分比。該指標(biāo)的值通常應(yīng)在95%以上，如sql(latchhit%)latch應(yīng)接近100%，如果低于99%，需要分析v$session_wait,v$latch,v$latch_childrenbufferbuffercache。redobuffer21o

9、nlineredolog90%db_cache_size大量排序時(shí)的磁盤i/o操sqlshared_pool_sizeoraclesql80%sqllatchLatchStatistics共享池統(tǒng)計(jì)()數(shù)據(jù)庫(kù)效率（）nsql(executetoparse%)sqlsession_cached_cursors數(shù)據(jù)庫(kù)效率（）n75%90%,sharepoolsqlEnd1sqlSQLwithexecutions免過(guò)多SQL解析。MemoryUsage開(kāi)：S2.75|73.-16%SQLwithexecLitions1:9337|92.93%MemoryforSQLw/eseb1:93.1+1927

10、0SharedPoolStatistics事件()是AWR報(bào)告中非常重要的部CPUtime(bufferbusy)信息，或正在對(duì)這個(gè)塊進(jìn)行修改。出現(xiàn)這個(gè)等待事件的頻度不應(yīng)大于1%(dbfilescatteredread)(dbfilesequentialread)db_cache_size?(enqueue)enqueueenqueue(latchfree)latch(sga)latchlatchsql排隊(duì)機(jī)制(它們被準(zhǔn)確地稱為相互排斥機(jī)制),用1sharepoollatcholtplatch2librarycachelatch(logfilesync)lgwrredologiosizecom

11、mitlgwr_log_io_sizelatch數(shù)據(jù)庫(kù)效率（）n數(shù)據(jù)庫(kù)效率（）nTop5TimedEventsE.vertWaits|Time(s|AvgWaitfms%TotalCallTimeWaitClassenq;TX-rowlockcontention+9,40724.1114S394.0ApplicationCPUtime1,37253directpathread6,275-200.1UserIDgccrblock2-way217B480.0Clusferlegfilesync10,49031.0Commit統(tǒng)計(jì)（）TOPSQLAWRSQLSQLStatisticsQiLDrdgr

12、ed口HfrdTimo壬口LordfiirMl弓ILLtFjiGeJ；壬口LordfiirKlMRa日咅。1_rriere1Gmtuili.ng!.D|Lorderedby訊raa匸nH*S0LnrrlerEby5岳刃口1_ordcTcdbyV前EonSuit右門1_iMilErM艸LiiupiTVMiiT適fMnpliailaLimri壬口LTQgtBa匚It1口TunSQLorderedbyElapaedTimeResircfisrecDWJferplSQlC4deinciuoesmeraseurMisueeatrran6QLsiaKmamgcsneoi聊ihcode.*帕T-Ddal日T

13、imeistheElapsedTimeollheSOLstartEmenl:dividedIntolheTolalDalatasie：Timemulbpliieiibf10DSQLSQLSQLCPUCPUTimeSQLExecutionsEiapefdt?)|Pcftjrnrneqei|ECM：UWMB|Etapoe#ci|i|TweireTim?|他LKlISJQL收艸|SQLIM|31,S97|75201Di4.S3ES3S|5a42rtKSnnlza|匚曰匚ThinClientdQltaIranmaxs55mntiurns3.MI1DQ:汀3.4Z130JDSDThinClientUFD

14、ATEUSERLOGINNFDTLDGOU,30314210.C49.Z2kjr!+fJ7!JDSCThinaintdpletffIrammasesc-iDnvrtinrni.9243J6a處jcecThinci-entc-rlect(ac-ed:fsumm-Frebr：比輛12di.OQo.ie同時(shí)倔4陰山陰joecnilnClientseleaeradeiarequeei0/h.2012i?B0.10QM網(wǎng)窗吋（剛河|jcecmmaientuDadleraxs-es-sionseiit15o.oe1$【軸町丘吠n曲卻|JCSCTllInClient&eleBhunionweimrecora

15、iw.13131800.07DOE|0r5idiAacY|JDBCTTiiriClientnartianMMlehpriidn13|1322J2口小D口E-|55Oq|J4Y|D5niuJESCThinClient2blcTmmcinlAlEtiracDrdw.SQLorderedbyCPUTimeCPUSQLCPU數(shù)據(jù)庫(kù)效率（）nSQLSQLorderedbyCPUTimeCPUSQLCPU數(shù)據(jù)庫(kù)效率（）nSQLorderedbyCPUTimeCPUSQLCPU數(shù)據(jù)庫(kù)效率（）nSQLorderedbyGetsSQLSQLCPUSQLorderedbyReadsSQLSQLI/OCPUSQLorderedbyExecutionsSQLsqlSQLorderedbyParseCallsbuffer