OracleAWR報(bào)告分析實(shí)例講解

{管理信息化ORACLE}OracleAWR報(bào)告分析實(shí)例講解WORKLOADREPOSITORYreportforDBNameDBIdInstanceInstnumReleaseRACHostICCI396ICCI11.0YESHPGICCI1SnapIdSnapTimeSessionsCursors/SessionBeginSnap:267825-Dec-0814:04:50241.5EndSnap:268025-Dec-0815:23:37261.5Elapsed:78.79(mins)DBTime:11.05(mins)DBTime不包括OracleDBTime遠(yuǎn)遠(yuǎn)小于Elapsed時(shí)間，說明數(shù)據(jù)庫比較空閑。在79分鐘里（其間收集了3次快照數(shù)據(jù)），數(shù)據(jù)庫耗時(shí)11分鐘，RDA數(shù)據(jù)中顯示系統(tǒng)有8個(gè)邏輯CPU（4個(gè)物理CPUCPU耗時(shí)1.4分鐘，CPU利用率只有大約2%（1.4/79）。說明系統(tǒng)壓力非常小。表性能問題的時(shí)間段。ReportSummaryCacheSizesBeginEndBufferCache:3,344M3,344MStdBlockSize:8KSharedPoolSize:704M704MLogBuffer:14,352K顯示SGA中每個(gè)區(qū)域的大小（在AMM較。sharedpool主要包括librarycache和dictionarycache。librarycache用來SQLPL/SQL和Javaclasseslibrarycache用來存儲最近引用的數(shù)據(jù)字典。發(fā)生在librarycache或dictionarycache的cachemiss代價(jià)要比發(fā)生在buffercachesharedpool的設(shè)置要確保最近使用的數(shù)據(jù)都能被cache。LoadProfilePerSecondPerTransactionRedosize:918,805.72775,912.72Logicalreads:3,521.772,974.06Blockchanges:1,817.951,535.22Physicalreads:68.2657.64Physicalwrites:362.59306.20Usercalls:326.69275.88Parses:38.6632.65Hardparses:0.030.03Sorts:0.610.51Logons:0.010.01Executes:354.34299.23Transactions:1.18%BlockschangedperRead:51.62RecursiveCall%:51.72Rollbackpertransaction%:85.49RowsperSort:########載情況，絕大多數(shù)據(jù)并沒有一個(gè)所謂“正確”的值，然而Logons大于每秒1~2Hardparses大于每秒100parses超過每秒300表明可能有爭用問題。Redosize：每秒/每事務(wù)產(chǎn)生的redo繁重程序。Logicalreads：每秒/每事務(wù)邏輯讀的塊數(shù)Blockchanges：每秒/每事務(wù)修改的塊數(shù)Physicalreads：每秒/每事務(wù)物理讀的塊數(shù)Physicalwrites：每秒/每事務(wù)物理寫的塊數(shù)Usercalls：每秒/每事務(wù)用戶call次數(shù)Parses：SQL解析的次數(shù)Hardparses：其中硬解析的次數(shù)，硬解析太多，說明SQL重用率不高。Sorts：每秒/每事務(wù)的排序次數(shù)Logons：每秒/每事務(wù)登錄的次數(shù)Executes：每秒/每事務(wù)SQL執(zhí)行次數(shù)Transactions：每秒事務(wù)數(shù)BlockschangedperRead：表示邏輯讀用于修改數(shù)據(jù)塊的比例RecursiveCall：遞歸調(diào)用占所有操作的比率Rollbackpertransaction：每事務(wù)的回滾率RowsperSort：每次排序的行數(shù)注:Oracle的硬解析和軟解析提到軟解析(softparse)和硬解析(hardparse)，就不能不說一下Oracle對sqlsql語句交付OracleOracle對此sql將進(jìn)行幾個(gè)步驟的處理過程：1、語法檢查(syntaxcheck)檢查此sql的拼寫是否語法。2、語義檢查(semanticcheck)諸如檢查sql語句中的訪問對象是否存在及該用戶是否具備相應(yīng)的權(quán)限。3、對sql語句進(jìn)行解析(parse)利用內(nèi)部算法對sql進(jìn)行解析，生成解析樹(parsetree)及執(zhí)行計(jì)劃(executionplan)。4、執(zhí)行sql，返回結(jié)果(executeandreturn)其中，軟、硬解析就發(fā)生在第三個(gè)過程里。Oracle利用內(nèi)部的hash算法來取得該sql的hash值，然后在librarycache里查找是否存在該hash值；假設(shè)存在，則將此sql與cache中的進(jìn)行比較；這也就是軟解析的過程。2生成執(zhí)行計(jì)劃的動作。這個(gè)過程就叫硬解析。sql極力避免硬解析，盡量使用軟解析。InstanceEfficiencyPercentages(Target100%)BufferNowait%:100.00RedoNoWait%:100.00BufferHit%:98.72In-memorySort%:99.86LibraryHit%:99.97SoftParse%:99.92ExecutetoParse%:89.09LatchHit%:99.99ParseCPUtoParseElapsd%:7.99%Non-ParseCPU:99.95本節(jié)包含了Oracle關(guān)鍵指標(biāo)的內(nèi)存命中率及其它數(shù)據(jù)庫實(shí)例操作的效率。其中BufferHitRatio也稱CacheHitRatio，LibraryHitratio也稱LibraryCacheHitratio。同LoadProfile一節(jié)相同，這一節(jié)也沒有所謂“正確”詢的DSS環(huán)境，20%的BufferHitRatio是可以接受的，而這個(gè)值對于一個(gè)OLTPOracleOLTPT系統(tǒng)，BufferHitRatio理想應(yīng)該在90%以上。BufferNowait表示在內(nèi)存獲得數(shù)據(jù)的未等待比例。bufferhit表示進(jìn)程從內(nèi)存中找到數(shù)據(jù)塊的比率，監(jiān)視這個(gè)值是否發(fā)生重大變OLTP80%庫分配更多的內(nèi)存。RedoNoWait表示在LOG緩沖區(qū)獲得BUFFER的未等待比例。如果太低（可參考90%閥值），考慮增加LOGBUFFER。libraryhit表示Oracle從LibraryCache中檢索到一個(gè)解析過的SQL或PL/SQL語句的比率，當(dāng)應(yīng)用程序調(diào)用SQL或存儲過程時(shí)，Oracle檢查LibraryCache確定是否存在解析過的版本，如果存在，Oracle立即執(zhí)行語句；如果不存在，Oracle解析此語句，并在LibraryCache中為它分配共享SQL區(qū)。低的libraryhitratio會導(dǎo)致過多的解析，增加CPU消耗，降低性能。如果libraryhitratio低于90%，可能需要調(diào)大sharedpool區(qū)。LatchHitLatchSERVER進(jìn)程獲取訪問LatchHit>99%SharedPoollatch爭用，可能由于未共享的SQL，或者LibraryCache太小，可使用綁定變更或調(diào)大SharedPool解決。ParseCPUtoParseElapsd/(解析實(shí)際運(yùn)行時(shí)間+解析中等待資源時(shí)間)，越高越好。Non-ParseCPUSQL實(shí)際運(yùn)行時(shí)間/(SQL實(shí)際運(yùn)行時(shí)間+SQL解析時(shí)間)示解析消耗時(shí)間過多。ExecutetoParseSQL重用率高，則這個(gè)比例會很高。該值越高表示一次解析后被重復(fù)執(zhí)行的次數(shù)越多。In-memorySort：在內(nèi)存中排序的比率，如果過低說明有大量的排序在臨時(shí)表空間中進(jìn)行?？紤]調(diào)大PGA。SoftParse：軟解析的百分比（softs/softs+hardssql在共享區(qū)的命中率，太低則需要調(diào)整應(yīng)用使用綁定變量。SharedPoolStatisticsBeginEndMemoryUsage%:47.1947.50%SQLwithexecutions>1:88.4879.81%MemoryforSQLw/exec>1:79.9973.52MemoryUsage%應(yīng)該穩(wěn)定在75%-90%間，如果太小，說明SharedPool有浪費(fèi)，而如果高于90，說明共享池中有爭用，內(nèi)存不足。SQLwithexecutions>1：執(zhí)行次數(shù)大于1的sql比率，如果此值太小，說明需要在應(yīng)用中更多使用綁定變量，避免過多SQL解析。MemoryforSQLw/exec>1：執(zhí)行次數(shù)大于1的SQL消耗內(nèi)存的占比。Top5TimedEventsEventWaitsTime(s)AvgWait(ms)%TotalCallTimeWaitClassCPUtime51577.6SQL*Netmoredatafromclient27,3196429.7Networklogfileparallelwrite5,4974797.1SystemI/Odbfilesequentialread7,9003545.3UserI/Odbfileparallelwrite4,8063475.1SystemI/O這是報(bào)告概要的最后一節(jié)，顯示了系統(tǒng)中最嚴(yán)重的5‘bufferbusywait’是較嚴(yán)重的等待事件，我們應(yīng)當(dāng)繼續(xù)研究報(bào)告中BufferWait和File/TablespaceIO區(qū)的內(nèi)容，識別哪I/O排序的SQL語句區(qū)以識別哪些語句在執(zhí)行大量I/OTablespace和I/O區(qū)觀察較慢響應(yīng)時(shí)間的文件。如果有較高的LATCH等待，就需要察看詳細(xì)的LATCH統(tǒng)計(jì)識別哪些LATCH產(chǎn)生的問題。logfileparallelwrite7%的CPU時(shí)間。通常，在沒有問題的數(shù)據(jù)庫中，CPUtime總是列在第一個(gè)。更多的等待事件，參見本報(bào)告的WaitEvents一節(jié)。RACStatisticsBeginEndNumberofInstances:22GlobalCacheLoadProfilePerSecondPerTransactionGlobalCacheblocksreceived:4.163.51GlobalCacheblocksserved:5.975.04GCS/GESmessagesreceived:408.47344.95GCS/GESmessagessent:258.03217.90DBWRFusionwrites:0.050.05EstdInterconnecttraffic(KB)211.16GlobalCacheEfficiencyPercentages(Targetlocal+remote100%)Bufferaccess-localcache%:98.60Bufferaccess-remotecache%:0.12Bufferaccess-disk%:1.28GlobalCacheandEnqueueServices-WorkloadCharacteristicsAvgglobalenqueuegettime(ms):0.1Avgglobalcachecrblockreceivetime(ms):1.1Avgglobalcachecurrentblockreceivetime(ms):0.8Avgglobalcachecrblockbuildtime(ms):0.0Avgglobalcachecrblocksendtime(ms):0.0Globalcachelogflushesforcrblocksserved%:3.5Avgglobalcachecrblockflushtime(ms):3.9Avgglobalcachecurrentblockpintime(ms):0.0Avgglobalcachecurrentblocksendtime(ms):0.0Globalcachelogflushesforcurrentblocksserved%:0.4Avgglobalcachecurrentblockflushtime(ms):3.0GlobalCacheandEnqueueServices-MessagingStatisticsAvgmessagesentqueuetime(ms):0.0Avgmessagesentqueuetimeonksxp(ms):0.3Avgmessagereceivedqueuetime(ms):0.5AvgGCSmessageprocesstime(ms):0.0AvgGESmessageprocesstime(ms):0.0%ofdirectsentmessages:14.40%ofindirectsentmessages:77.04%offlowcontrolledmessages:8.56MainReportWaitEventsStatisticsSQLStatisticsInstanceActivityStatisticsIOStatsBufferPoolStatisticsAdvisoryStatisticsWaitStatisticsUndoStatisticsLatchStatisticsSegmentStatisticsDictionaryCacheStatisticsLibraryCacheStatisticsMemoryStatisticsStreamsStatisticsResourceLimitStatisticsParametersWaitEventsStatisticsTimeModelStatisticsWaitClassWaitEventsBackgroundWaitEventsOperatingSystemStatisticsServiceStatisticsServiceWaitClassStatsBacktoTopTimeModelStatisticsTotaltimeindatabaseuser-calls(DBTime):663sStatisticsincludingtheword"background"measurebackgroundprocesstime,andsodonotcontributetotheDBtimestatisticOrderedby%orDBtimedesc,StatisticnameStatisticNameTime(s)%ofDBTimeDBCPU514.5077.61sqlexecuteelapsedtime482.2772.74parsetimeelapsed3.760.57PL/SQLexecutionelapsedtime0.500.08hardparseelapsedtime0.340.05connectionmanagementcallelapsedtime0.080.01hardparse(sharingcriteria)elapsedtime0.000.00repeatedbindelapsedtime0.000.00PL/SQLpilationelapsedtime0.000.00failedparseelapsedtime0.000.00DBtime662.97backgroundelapsedtime185.19backgroundcputime67.48此節(jié)顯示了各種類型的數(shù)據(jù)庫處理任務(wù)所占用的CPU時(shí)間。BacktoWaitEventsStatisticsBacktoTopWaitClasss-secondcs-centisecond-100thofasecondms-millisecond-1000thofasecondus-microsecond-thofasecondorderedbywaittimedesc,waitsdescWaitClassWaits%Time-outsTotalWaitTime(s)Avgwait(ms)Waits/txnUserI/O66,8370.00120211.94SystemI/O28,2950.009335.05Network1,571,4500.00660280.72Cluster210,5480.0029037.61Other81,78371.8228014.61Application333,1550.0016059.51Concurrency5,1820.04510.93Commit9190.00440.16Configuration25,42799.46104.54BacktoWaitEventsStatisticsBacktoTopWaitEventss-secondcs-centisecond-100thofasecondms-millisecond-1000thofasecondus-microsecond-thofasecondorderedbywaittimedesc,waitsdesc(idleeventslast)EventWaits%Time-outsTotalWaitTime(s)Avgwait(ms)Waits/txnSQL*Netmoredatafromclient27,3190.006424.88logfileparallelwrite5,4970.004790.98dbfilesequentialread7,9000.003541.41dbfileparallelwrite4,8060.003470.86dbfilescatteredread10,3100.003131.84directpathwrite42,7240.003017.63reliablemessage3552.8218490.06SQL*Netbreak/resettoclient333,0840.0016059.50dbfileparallelread3,7320.001340.67gccurrentmultiblockrequest175,7100.0010031.39controlfilesequentialread15,9740.001012.85directpathreadtemp1,8730.00950.33gccrmultiblockrequest20,8770.00803.73logfilesync9190.00440.16gccrblockbusy5260.00360.09enq:FB-contention10,3840.00301.85DFSlockhandle3,5170.00310.63controlfileparallelwrite1,9460.00310.35gccurrentblock2-way4,1650.00200.74librarycachelock4320.00240.08name-servicecallwait220.002760.00rowcachelock3,8940.00200.70gcslogflushsync1,25942.02210.22osthreadstartup185.562890.00gccrblock2-way3,6710.00200.66gccurrentblockbusy1130.001120.02SQL*Netmessagetoclient1,544,110.0010275.835gcbufferbusy156.671700.00gccrdiskread3,2720.00100.58directpathwritetemp1590.00150.03gccurrentgrantbusy8980.00110.16logfileswitchpletion290.001170.01CGSwaitforIPCmsg48,73999.87008.71gccurrentgrant2-way1,1420.00000.20kjbdrmcvtqlmondrmquiesce:pingpletion90.000190.00enq:US-contention5670.00000.10directpathread1380.00010.02enq:WF-contention140.00090.00ksxrpollremoteinstances13,29158.45002.37librarycachepin2110.00010.04gesglobalresourcedirectorytobefrozen9100.000100.00waitforscnack5830.00000.10logfilesequentialread360.00020.01undosegmentextension25,34299.79004.53rdbmsipcreply2790.00000.05ktfbtgex6100.000100.00enq:HW-contention440.00010.01gccrgrant2-way1580.00000.03enq:TX-indexcontention10.000340.00enq:CF-contention640.00010.01PXDeq:SignalACK3721.62010.01latchfree30.000100.00bufferbusywaits6250.16000.11KJC:Waitformsgsendstoplete1540.00000.03logbufferspace110.00020.00enq:PS-contention460.00010.01enq:TM-contention700.00000.01IPCsendpletionsync40100.00000.01PXDeq:reapcredit1,54499.81000.28logfilesinglewrite360.00000.01enq:TT-contention460.00000.01enq:TD-KTFdumpentries120.00010.00readbyothersession10.000120.00LGWRwaitforredocopy5400.00000.10PXDeqCredit:sendblkd175.88000.00enq:TA-contention140.00000.00latch:gesresourcehashlist440.00000.01enq:PI-contention80.00000.00writepletewaits10.00020.00enq:DR-contention30.00000.00enq:MW-contention30.00000.00enq:TS-contention30.00000.00PXqreflatch150100.00000.03enq:MD-contention20.00000.00latch:KCLgcelementparentlatch110.00000.00enq:JS-jobrunlock-synchronize10.00010.00SQL*Netmoredatatoclient160.00000.00latch:cachebufferslruchain10.00000.00enq:UL-contention10.00000.00gccurrentsplit10.00000.00enq:AF-taskserialization10.00000.00latch:objectqueueheaderoperation30.00000.00latch:cachebufferschains10.00000.00latch:enqueuehashchains20.00000.00SQL*Netmessagefromclient1,544,110.0012,6268275.833gcsremotemessage634,88498.649,20314113.41DIAGidlewait23,6280.004,6161954.22gesremotemessage149,59193.454,6123126.72StreamsAQ:qmnslaveidlewait1670.004,611276110.03StreamsAQ:qmncoordinatoridlewait35147.864,611131370.06StreamsAQ:waitingformessagesinthequeue488100.004,60594360.09virtualcircuitstatus157100.004,596292720.03PXIdleWait1,07297.112,58124070.19jobqslavewait14597.9342028960.03StreamsAQ:waitingfortimemanagementorcleanupt1100.002702697470.00asksPXDeq:ParseReply4040.00030.01PXDeq:ExecutionMsg12126.45000.02PXDeq:JoinACK3842.11010.01PXDeq:ExecuteReply3432.35000.01PXDeq:MsgFragment160.00000.00StreamsAQ:RACqmncoordinatoridlewait351100.00000.06classslavewait20.00000.00dbfilescatteredread等待事件是當(dāng)SESSION等待multi-blockI/O時(shí)發(fā)生的，通過是由于fulltablescans或indexfastfullscans。發(fā)生過多讀操作的Segments可以在“和節(jié)中識別（在其它版本的報(bào)告中，可能是別的名稱）。如果在OLTP應(yīng)用中，不應(yīng)該有過多的全掃描操作，而應(yīng)使用選擇性好的索引操作。DBfilesequentialread等待意味著發(fā)生順序I/O操作的熱點(diǎn)SEGMENT，然后通過對大表進(jìn)行分區(qū)以減少I/O量，或者優(yōu)化執(zhí)行計(jì)劃（通過使用存儲大綱或執(zhí)行數(shù)據(jù)分析）以避免單塊讀操作引起的sequentialread等待。通過在批量應(yīng)用中，DBfilesequentialread是很影響性能的事件，總是應(yīng)當(dāng)設(shè)法避免。LogFileParallelWrite事件是在等待LGWR進(jìn)程將REDO記錄從LOG緩沖區(qū)寫到聯(lián)機(jī)日志文件時(shí)發(fā)生的。雖然寫操作可能是并發(fā)的，但LGWR需要等待最后的I/OOS完成所有請求的LOG文件移到更快的磁盤上或者條帶化磁盤（減少爭用）而降低這個(gè)等待。BufferBusyWaits事件是在一個(gè)SESSION需要訪問BUFFERCACHE中的一個(gè)數(shù)據(jù)庫塊而又不能訪問時(shí)發(fā)生的。緩沖區(qū)“busy”的兩個(gè)原因是：1SESSION正在將數(shù)據(jù)塊讀進(jìn)BUFFER。2）另一個(gè)SESSION正在以排它模式占用著這塊被請求的BUFFER?？梢栽凇癝egmentsbyBufferBusyWaits”一節(jié)中找出發(fā)生這種等待的SEGMENTreverse-keyindexes并對熱表進(jìn)行分區(qū)而減少這種等待事件。LogFileSync事件，當(dāng)用戶SESSION執(zhí)行事務(wù)操作（COMMIT或ROLLBACK等）LGWR進(jìn)程將所需要的所有REDO信息從LOGBUFFER寫到LOG用戶SESSION等待LGWR返回安全寫入磁盤的通知時(shí)發(fā)生此等待。減少此等待的方法寫LogFileParallelWrite事件的處理。EnqueueWaitsSESSION鎖類型。導(dǎo)致Enqueue等待的主要鎖類型有三種：TX（事務(wù)鎖）,TMD（ML鎖）和ST（空間管理鎖）。BacktoWaitEventsStatisticsBacktoTopBackgroundWaitEventsorderedbywaittimedesc,waitsdesc(idleeventslast)EventWaits%Time-outsTotalWaitTime(s)Avgwait(ms)Waits/txnlogfileparallelwrite5,4970.004790.98dbfileparallelwrite4,8060.003470.86eventsinwaitclassOther69,00283.2522012.33controlfilesequentialread9,3230.00711.67controlfileparallelwrite1,9460.00310.35osthreadstartup185.562890.00directpathread1380.00010.02dbfilesequentialread210.00050.00directpathwrite1380.00000.02logfilesequentialread360.00020.01gccrblock2-way960.00000.02gccurrentblock2-way780.00000.01logbufferspace110.00020.00rowcachelock590.00000.01logfilesinglewrite360.00000.01bufferbusywaits1510.66000.03gccurrentgrantbusy290.00000.01librarycachelock40.00010.00enq:TM-contention100.00000.00gccurrentgrant2-way80.00000.00gccrmultiblockrequest70.00000.00gccrgrant2-way50.00000.00rdbmsipcmessage97,28873.7750,19451617.38gcsremotemessage634,88698.649,20314113.41DIAGidlewait23,6280.004,6161954.22pmontimer1,621100.004,61528470.29gesremotemessage149,59193.454,6123126.72StreamsAQ:qmnslaveidlewait1670.004,611276110.03StreamsAQ:qmncoordinatoridlewait35147.864,611131370.06smontimer2776.504,531163560.05StreamsAQ:waitingfortimemanagementorcleanupta1100.002702697470.00sksPXDeq:ParseReply4040.00030.01PXDeq:JoinACK3842.11010.01PXDeq:ExecuteReply3432.35000.01StreamsAQ:RACqmncoordinatoridlewait351100.00000.06BacktoWaitEventsStatisticsBacktoTopOperatingSystemStatisticsStatisticTotalNUM_LCPUS0NUM_VCPUS0AVG_BUSY_TIME101,442AVG_IDLE_TIME371,241AVG_IOWAIT_TIME5,460AVG_SYS_TIME25,795AVG_USER_TIME75,510BUSY_TIME812,644IDLE_TIME2,971,077IOWAIT_TIME44,794SYS_TIME207,429USER_TIME605,215LOAD0OS_CPU_WAIT_TIME854,100RSRC_MGR_CPU_WAIT_TIME0PHYSICAL_MEMORY_BYTES8,589,934,592NUM_CPUS8NUM_CPU_CORES4NUM_LCPUS：如果顯示0，是因?yàn)闆]有設(shè)置LPARSNUM_VCPUS：同上。AVG_BUSY_TIME：BUSY_TIME/NUM_CPUSAVG_IDLE_TIME：IDLE_TIME/NUM_CPUSAVG_IOWAIT_TIME：IOWAIT_TIME/NUM_CPUSAVG_SYS_TIME：SYS_TIME/NUM_CPUSAVG_USER_TIME：USER_TIME/NUM_CPUSaroBUSY_TIME：timeequivof%usr+%sysinsaroutputIDLE_TIME：timeequivof%idleinsarIOWAIT_TIME：timeequivof%wioinsarSYS_TIME：timeequivof%sysinsarUSER_TIME：timeequivof%usrinsarLOAD：未知OS_CPU_WAIT_TIME：supposedlytimewaitingonrunqueuesRSRC_MGR_CPU_WAIT_TIME：timewaitedcozofresourcemanagerPHYSICAL_MEMORY_BYTES：totalmemoryinusesupposedlyNUM_CPUS：numberofCPUsreportedbyOSNUM_CPU_CORES：numberofCPUsocketsonmotherboard總的elapsedtime也可以用以公式計(jì)算：BUSY_TIME+IDLE_TIME+IOWAITTIME或：SYS_TIME+USER_TIME+IDLE_TIME+IOWAIT_TIME（因?yàn)锽USY_TIME=SYS_TIME+USER_TIME）BacktoWaitEventsStatisticsBacktoTopServiceStatisticsorderedbyDBTimeServiceNameDBTime(s)DBCPU(s)PhysicalReadsLogicalReadsICCI608.10496.60315,84916,550,972SYS$USERS54.7017.806,53958,929ICCIXDB0.000.0000SYS$BACKGROUND0.000.0028238,990BacktoWaitEventsStatisticsBacktoTopServiceWaitClassStatsWaitClassinfoforservicesintheServiceStatisticssection.TotalWaitsandTimeWaiteddisplayedforthefollowingwaitclasses:UserI/O,Concurrency,Administrative,NetworkTimeWaited(WtTime)incentisecond(100thofasecond)ServiceNameUserI/OTotalWtsUserI/OWtTimeConcurcyTotalWtsConcurcyWtTimeAdminTotalWtsAdminWtTimeNetworkTotalWtsNetworkWtTimeICCI5982686404621338006552SYS$USERS65673238231110073233SYS$BACKGROU4431153301680000NDBacktoWaitEventsStatisticsBacktoTopSQLStatisticsSQLorderedbyElapsedTimeSQLorderedbyCPUTimeSQLorderedbyGetsSQLorderedbyReadsSQLorderedbyExecutionsSQLorderedbyParseCallsSQLorderedbySharableMemorySQLorderedbyVersionCountSQLorderedbyClusterWaitTimeCompleteListofSQLText本節(jié)按各種資源分別列出對資源消耗最嚴(yán)重的SQL期內(nèi)全部資源的比例，這給出我們調(diào)優(yōu)指南。例如在一個(gè)系統(tǒng)中，CPU資源是系統(tǒng)性能瓶頸所在，那么優(yōu)化buffergets最多的SQL語句將獲得最大效果。在一個(gè)I/O等待是最嚴(yán)重事件的系統(tǒng)中，調(diào)優(yōu)的目標(biāo)應(yīng)該是physicalIOs最多的SQL語句。在STATSPACKSQLHashValue通過下面語句從數(shù)據(jù)庫中查到：selectsql_textfromstats$sqltextwherehash_value=&hash_valueorderbypiece;BacktoTopSQLorderedbyElapsedTimeResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100ElapsedTimeCPUTime(ExecutioElapperExe%TotalDBTiSQLIdSQLModulSQLText(s)s)nsc(s)mee9357193.5014.10d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUID_y)...7675172,3290.0011.524vja2k2gdtyu(TNSV1-V3insertintoICCICCSvalues(:p)...5842158.048.75569r5k05drsj(TNSV1-V3insertintoCUMIselectCUSV_7)...5142150.937.68ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUSM_c)...3836166,0690.005.677gtztzv329wg,fromco0...353135.005.286z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAME,TdO_...2323172,3290.003.461dm3bq36vu3g(TNSV1-V3insertintoiccifnsactvalue8)s...151152.982.25djs2w2f17nw2DECLAREjobBINARY_INTEGER:z=...1414172,9830.002.167wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORM_Az)D...1313172,3370.002.00gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalues4)...1313166,0510.001.89chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvalues(:j)...10419.701.460yv9t4qb1zb2(TNSV1-V3selectCUID_CUST_NO,CUID_Ib)D_...10851.911.441crajpb7j5tyINSERTINTOSTATS$SGA_TARGEzT_A...88172,3290.001.2538apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOVEs)R...88172,9830.001.165c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEwhx)...BacktoSQLStatisticsBacktoTopSQLorderedbyCPUTimeResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.%TotalDBTimeistheElapsedTimeoftheSQLstatementdividedintotheTotalDatabaseTimemultipliedby100CPUTime(s)ElapsedTime(s)ExecutionsCPUperExec(s)%TotalDBTimeSQLIdSQLModuleSQLText7576172,3290.0011.524vja2k2gdtyu(TNSV1-V3insertintoICCICCSvalues(:p)...5793157.3114.10d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUID_y)...4251142.437.68ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUSM_c)...4258142.018.75569r5k05drsj(TNSV1-V3insertintoCUMIselectCUSV_7)...3638166,0690.005.677gtztzv329wg,fromco0...2323172,3290.003.461dm3bq36vu3g(TNSV1-V3insertintoiccifnsactvalue8)s...1414172,9830.002.167wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORM_Az)D...1313172,3370.002.00gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalues4)...1313166,0510.001.89chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvalues(:j)...111552.232.25djs2w2f17nw2DECLAREjobBINARY_INTEGER:z=...88172,3290.001.2538apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOVEs)R...81051.601.441crajpb7j5tyINSERTINTOSTATS$SGA_TARGEzT_A...88172,9830.001.165c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEwhx)...41013.541.460yv9t4qb1zb2(TNSV1-V3selectCUID_CUST_NO,CUID_Ib)D_...33513.135.286z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAME,TdO_...BacktoSQLStatisticsBacktoTopSQLorderedbyGetsResourcesreportedforPL/SQLcodeincludestheresourcesusedbyallSQLstatementscalledbythecode.TotalBufferGets:16,648,792CapturedSQLaccountfor97.9%ofTotalBufferGetsExecutionsGetsperExec%TotalCPUTime(s)ElapsedTime(s)SQLIdSQLModuleSQLText3,305,363172,32919.1819.8574.5776.414vja2k2gdtyu(TNSV1-V3insertintoICCICCSvaluesp)(:...2,064,41412,064,414.12.4057.3193.50d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUI00y)D_...1,826,869166,06911.0010.9735.8437.607gtztzv329wg,from0co...1,427,648172,3378.288.5812.9713.29gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalu4)es...1,278,667172,3297.427.6822.8522.941dm3bq36vu3g(TNSV1-V3insertintoiccifnsactval8)ues...1,216,36711,216,367.7.3142.4350.93ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUS00c)M_...1,107,30511,107,305.6.6542.0158.04569r5k05drsj(TNSV1-V3insertintoCUMIselectCUS007)V_...898,868172,9835.205.4014.2814.347wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORMz)_AD...711,450166,0514.284.2712.5212.55chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvaluesj)(:...692,996172,3294.024.168.318.3138apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOs)VER...666,748166,0524.024.006.366.367v9dyf5r424y(TNSV1-V3selectNEWACTNOinto:b0frh)om...345,357172,9832.002.077.707.715c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEx)wh...231,75651,6334.491.395.755.8349ms69srnaxz(TNSV1-V3insertintoICCIRPYVvaluej)s(...BacktoSQLStatisticsBacktoTopSQLorderedbyReadsTotalDiskReads:322,678CapturedSQLaccountfor66.1%ofTotalPhysicalReadsExecutionsReadsperExec%TotalCPUTime(s)ElapsedTime(s)SQLIdSQLModuleSQLText66,286166,286.0020.5457.3193.50d8z0u8hgj8xd(TNSV1-V3insertintoCUIDselectCUIy)D_...50,646150,646.0015.703.549.700yv9t4qb1zb2(TNSV1-V3selectCUID_CUST_NO,CUIDb)_ID_...24,507124,507.007.5942.0158.04569r5k05drsj(TNSV1-V3insertintoCUMIselectCUS7)V_...21,893121,893.006.7842.4350.93ackxqhnktxnb(TNSV1-V3insertintoCUSMselectCUSc)M_...19,761119,761.004a7nh7j8zmfrz(TNSV1-V3selectCUSV_CUST_NOfromCw)UMI...19,554119,554.006.061.273.8338gak8u2qm11SQL*Plusselectcount(*)fromCUSVAwA_T...6,34216,342.001.973.1335.006z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAMdE,TO_...4,38514,385.001.361.592.43cp5duhcsj72q(TNSV1-V3selectCUSM_CUST_ACCT_NO0)from...63512.600.0211.1714.91djs2w2f17nw2DECLAREjobBINARY_INTEGEzR:=...35135.000.010.080.671uk5m5qbzj1vSQL*PlusBEGINdbms_workload_repotsitory...BacktoSQLStatisticsBacktoTopSQLorderedbyExecutionsTotalExecutions:1,675,112CapturedSQLaccountfor99.8%ofTotalExecutionsRowsProcessedRowsperExecCPUperExec(s)ElapperExec(s)SQLIdSQLModuleSQLText172,983172,3291.000.000.005c4qu2zmj3gu(TNSV1-V3select*fromICCIPRODCODEx)wh...172,983172,3291.000.000.007wwv1ybs9zgu(TNSV1-V3updateICCIFNSACTsetBORMz)_AD...172,337172,3371.000.000.00gmn2w09rdxn1(TNSV1-V3insertintoOLDNEWACTvalu4)es...172,329172,3291.000.000.001dm3bq36vu3g(TNSV1-V3insertintoiccifnsactval8)ues...172,329172,3291.000.000.0038apjgr0p55n(TNSV1-V3updateICCICCSsetCCSMAXOs)VER...172,3296,2860.040.000.004vja2k2gdtyu(TNSV1-V3insertintoICCICCSvaluesp)(:...166,069166,0691.000.000.007gtztzv329wg,from0co...166,052166,0521.000.000.007v9dyf5r424y(TNSV1-V3selectNEWACTNOinto:b0frh)om...166,051166,0511.000.000.00chjmy0dxf9mb(TNSV1-V3insertintoICCICCSvaluesj)(:...51,74051,7401.000.000.00bu8tnqr3xv25(TNSV1-V3selectcount(*)into:b0frq)o...51,63351,6331.000.000.0049ms69srnaxz(TNSV1-V3insertintoICCIRPYVvaluej)s(...BacktoSQLStatisticsBacktoTopSQLorderedbyParseCallsTotalParseCalls:182,780CapturedSQLaccountfor99.0%ofTotalParseCallsExecutions%TotalParsesSQLIdSQLModuleSQLText166,069166,06990.867gtztzv329wg0,fromco...6,3046,3043.452ym6hhaq30r73selecttype#,blocks,extents,...2,4372,4381.33bsa0wjtftg3uwselectfile#fromfile$where...1,5681,5680.869qgtwh66xg6nzupdateseg$settype#=:4,bloc...1,5541,5540.85aq4js2gkfjru8updatetsq$setblocks=:3,max...4444440.24104pd9mm3fh9pselectblocks,maxblocks,gran...4214210.23350f5yrnnmshslocktable_mods$inex...4214210.23g00cj285jmgswupdate_mods$setinser...86860.053m8smr0v7v1m6INSERTINTO$_adv_messa...81810.04f80h0xb1qvbskSELECT$_adv_seq_msggro...BacktoSQLStatisticsBacktoTopSQLorderedbySharableMemoryNodataexistsforthissectionofthereport.BacktoSQLStatisticsBacktoTopSQLorderedbyVersionCountNodataexistsforthissectionofthereport.BacktoSQLStatisticsBacktoTopSQLorderedbyClusterWaitTimeClusterWaitTime(s)CWT%ofElapsdTimeElapsedTime(s)CPUTime(s)ExecutionsSQLIdSQLModuleSQLText10.9611.7293.5057.311d8z0u8hgj8xd(TNSV1-V3)insertintoCUIDselectCyUID_...4.217.2558.0442.011569r5k05drsj(TNSV1-V3)insertintoCUMIselectC7USV_...3.627.1250.9342.431ackxqhnktxnb(TNSV1-V3)insertintoCUSMselectCcUSM_...2.396.3537.6035.84166,0697gtztzv329wg,fr0omco...2.383.1276.4174.57172,3294vja2k2gdtyu(TNSV1-V3)insertintoICCICCSvalupes(:...1.6416.919.703.5410yv9t4qb1zb2(TNSV1-V3)selectCUID_CUST_NO,CUbID_ID_...1.063.0235.003.1316z06gcfw39pkSQL*PlusSELECTF.TABLESPACE_NAdME,TO_...0.8313.766.042.141a7nh7j8zmfrz(TNSV1-V3)selectCUSV_CUST_NOfrowmCUMI...0.6687.900.750.42444104pd9mm3fh9selectblocks,maxblockps,gran...0.5013.013.831.27138gak8u2qm11SQL*Plusselectcount(*)fromCUSwVAA_T...0.5051.750.960.791,554aq4js2gkfjruupdatetsq$setblocks=:83,max...0.3391.110.360.3318704xtrk7uyhknselectobj#,type#,ctimhe,mti...0.332.4713.2912.97172,337gmn2w09rdxn1(TNSV1-V3)insertintoOLDNEWACTva4lues...0.291.2622.9422.85172,3291dm3bq36vu3g(TNSV1-V3)insertintoiccifnsactv8alues...0.2510.142.431.591cp5duhcsj72q(TNSV1-V3)selectCUSM_CUST_ACCT_0NOfrom...0.2127.920.740.741,5689qgtwh66xg6nupdateseg$settype#=:4z,bloc...0.203.495.835.7551,63349ms69srnaxz(TNSV1-V3)insertintoICCIRPYVvaljues(...0.171.3912.5512.52166,051chjmy0dxf9mb(TNSV1-V3)insertintoICCICCSvalujes(:...0.1657.640.280.2439cn1gtsav2d5j(TNSV1-V3)BEGINBEGINIF(...h0.1474.580.190.141215ngzsfstg8tmselecto.owner#,y,o.nam...0.1164.720.180.158078m9ryygp65v(TNSV1-V3)SELECT/*+ALL_ROWS*/CO5UNT(*...0.1194.540.120.0117bwt0pmxhv7qkdeletefromcon$whereow7ner#=...0.1180.260.140.1432753saa2zkr6wcselectintcol#,nvl(pos3#,0),...0.0819.200.420.241d92h3rjp0y21beginprvt__execute(:.7..0.0754.970.130.13837ng34ruy5awxselecti.obj#,i.ts#,i.qfile#,...0.02770hhmdwwgxbw0selectobj#,type#,flagrs,...0.0686.500.060.0645a2any035u1qzselectowner#,namefrom1con$...0.068.190.670.0811uk5m5qbzj1vSQL*PlusBEGINdbms_workload_retpository...0.0475.690.060.06876769wyy3yf66selectpos#,intcol#,cofl#,sp...0.0448.050.090.0770pvtkmrrq8usselectfile#,block#frogmseg...0.048.840.400.406,3042ym6hhaq30r7selecttype#,blocks,ex3tents,...0.03249b52m6vduutr8deletefromRecycleBin$j...0.0366.230.050.05851gu8t96d0bdmselectt.ts#,t.file#,tu.block...0.0367.030.050.0538btzq46kta67dDBMS_SCHEDULupdateobj$setobj#=:6,zERtype#...0.0266.730.040.04863m8smr0v7v1mINSERTINTO$_adv_messa6...0.0226.940.090.09380k8h617b8guhdeletefromRecycleBin$f...0.0276.760.030.03519vtm7gy4fr2nselectcon#fromcon$wheyreow...0.0251.910.050.058483taa7kaw59cselectname,intcol#,se1gcol#,...0.020.1514.9111.175djs2w2f17nw2DECLAREjobBINARY_INTEzGER:=...0.022.121.000.998,784501v412s13r4(TNSV1-V3)updateICCIFNSACTsetBOmRM_FA...0.0253.820.030.0339bdv0rkkssq2j(TNSV1-V3)SELECTcount(*)FROMusemr_poli...0.010.1014.3414.28172,98