ORACLE性能AWR報告的使用和分析

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

2、_wait、v$process、v$sql、v$lock、v$latch、v$sysstat、v$system_event、v$sgastat報告statspackOracle9iSQLI/OSGA和報告AWR10gOracleStatspackADDM執(zhí)行計劃和建議ORACLESQL代碼SQL知道SQL執(zhí)行的生成報告AWRoracle10gAWR由ORACLE自動產(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報告分析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)計()數(shù)據(jù)庫效率（）nSQLlibrarycac

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

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陽/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)計()數(shù)據(jù)庫效率（）n共享池統(tǒng)計()數(shù)據(jù)庫效率（）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大量排序時的磁盤i/o操sqlshared_pool_sizeoraclesql80%sqllatchLatchStatistics共享池統(tǒng)計()數(shù)據(jù)庫效率（）nsql(executetoparse%)sqlsession_cached_cursors數(shù)據(jù)庫效率（）n75%90%,sharepoolsqlEnd1sqlSQLwithexecutions免過多SQL解析。MemoryUsage開：S2.75|73.-16%SQLwithexecLitions1:9337|92.93%MemoryforSQLw/eseb1:93.1+1927

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

11、mitlgwr_log_io_sizelatch數(shù)據(jù)庫效率（）n數(shù)據(jù)庫效率（）nTop5TimedEventsE.vertWaits|Time(s|AvgWaitfms%TotalCallTimeWaitClassenq;TX-rowlockcontention+9,40724.1114S394.0ApplicationCPUtime1,37253directpathread6,275-200.1UserIDgccrblock2-way217B480.0Clusferlegfilesync10,49031.0Commit統(tǒng)計（）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同時倔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ù)庫效率（）nSQLSQLorderedbyCPUTimeCPUSQLCPU數(shù)據(jù)庫效率（）nSQLorderedbyCPUTimeCPUSQLCPU數(shù)據(jù)庫效率（）nSQLorderedbyGetsSQLSQLCPUSQLorderedbyReadsSQLSQLI/OCPUSQLorderedbyExecutionsSQLsqlSQLorderedbyParseCallsbuffer