【正文】 log buffer space1102enq: PS contention4601enq: TM contention7000IPC send pletion sync4000PX Deq: reap credit1,54400log file single write3600enq: TT contention4600enq: TD KTF dump entries1201read by other session1012LGWR wait for redo copy54000PX Deq Credit: send blkd1700enq: TA contention1400latch: ges resource hash list4400enq: PI contention800write plete waits102enq: DR contention300enq: MW contention300enq: TS contention300PX qref latch15000enq: MD contention200latch: KCL gc element parent latch1100enq: JS job run lock synchronize101SQL*Net more data to client1600latch: cache buffers lru chain100enq: UL contention100gc current split100enq: AF task serialization100latch: object queue header operation300latch: cache buffers chains100latch: enqueue hash chains200SQL*Net message from client1,544,11312,6268gcs remote message634,8849,20314DIAG idle wait23,6284,616195ges remote message149,5914,61231Streams AQ: qmn slave idle wait1674,61127611Streams AQ: qmn coordinator idle wait3514,61113137Streams AQ: waiting for messages in the queue4884,6059436virtual circuit status1574,59629272PX Idle Wait1,0722,5812407jobq slave wait1454202896Streams AQ: waiting for time management or cleanup tasks1270269747PX Deq: Parse Reply4003PX Deq: Execution Msg12100PX Deq: Join ACK3801PX Deq: Execute Reply3400PX Deq: Msg Fragment1600Streams AQ: RAC qmn coordinator idle wait35100class slave wait200db file scattered read等待事件是當(dāng)SESSION等待multiblock I/O時(shí)發(fā)生的，通過(guò)是由于full table scans或 index fast full scans。 s second cs centisecond 100th of a second background cpu time Total time in database usercalls (DB Time): 663s Background Wait Events Parameters Wait Events Statistics Library Cache Statistics Undo Statistics IO Stats Global Cache Efficiency Percentages (Target local+remote 100%) Buffer access local cache %:Buffer access remote cache %:Buffer access disk %:Global Cache and Enqueue Services Workload Characteristics Avg global enqueue get time (ms):Avg global cache cr block receive time (ms):Avg global cache current block receive time (ms):Avg global cache cr block build time (ms):Avg global cache cr block send time (ms):Global cache log flushes for cr blocks served %:Avg global cache cr block flush time (ms):Avg global cache current block pin time (ms):Avg global cache current block send time (ms):Global cache log flushes for current blocks served %:Avg global cache current block flush time (ms):Global Cache and Enqueue Services Messaging Statistics Avg message sent queue time (ms):Avg message sent queue time on ksxp (ms):Avg message received queue time (ms):Avg GCS message process time (ms):Avg GES message process time (ms):% of direct sent messages:% of indirect sent messages:% of flow controlled messages:Main Report 在這里，log file parallel write是相對(duì)比較多的等待，占用了7%的CPU時(shí)間。當(dāng)我們調(diào)優(yōu)時(shí)，總希望觀察到最顯著的效果，因此應(yīng)當(dāng)從這里入手確定我們下一步做什么。Top 5 Timed Events EventWaitsTime(s)Avg Wait(ms)% Total Call TimeWait ClassCPU timeSoft Parse：軟解析的百分比（softs/softs+hards），近似當(dāng)作sql在共享區(qū)的命中率，太低則需要調(diào)整應(yīng)用使用綁定變量。Execute to Parse：是語(yǔ)句執(zhí)行與分析的比例，如果要SQL重用率高，則這個(gè)比例會(huì)很高。Latch Hit：Latch是一種保護(hù)內(nèi)存結(jié)構(gòu)的鎖，可以認(rèn)為是SERVER進(jìn)程獲取訪問(wèn)內(nèi)存數(shù)據(jù)結(jié)構(gòu)的許可。如果太低（可參考90%閥值），考慮增加LOG BUFFER。Buffer Nowait表示在內(nèi)存獲得數(shù)據(jù)的未等待比例。其中Buffer Hit Ratio 也稱Cache Hit Ratio，Library Hit ratio也稱Library Cache Hit ratio。　　誠(chéng)然，如果上面的2個(gè)假設(shè)中任有一個(gè)不成立，那么優(yōu)化器都將進(jìn)行創(chuàng)建解析樹、生成執(zhí)行計(jì)劃的動(dòng)作?！　?duì)sql語(yǔ)句進(jìn)行解析(prase)　　利用內(nèi)部算法對(duì)sql進(jìn)行解析，生成解析樹(parse tree)及執(zhí)行計(jì)劃(execution plan)。Logical reads：每秒/每事務(wù)邏輯讀的塊數(shù)Block changes：每秒/每事務(wù)修改的塊數(shù)Physical reads：每秒/每事務(wù)物理讀的塊數(shù)Physical writes：每秒/每事務(wù)物理寫的塊數(shù)User calls：每秒/每事務(wù)用戶call次數(shù)Parses：SQL解析的次數(shù)Hard parses：其中硬解析的次數(shù)，硬解析太多，說(shuō)明SQL重用率不高。Load ProfilePer SecondPer TransactionRedo size:918,775,Logical reads:3,2,Block changes:1,1,Physical reads:Physical writes:User calls:Parses:Hard parses:Sorts:Logons:Executes:Transactions:library cache用來(lái)存儲(chǔ)最近解析（或編譯）后SQL、PL/SQL和Java classes等。如果快照周期不在這一段時(shí)間內(nèi)，或者快照周期跨度太長(zhǎng)而包含了大量的數(shù)據(jù)庫(kù)空閑時(shí)間，所得出的分析結(jié)果是沒(méi)有意義的。如果DB Time遠(yuǎn)遠(yuǎn)小于Elapsed時(shí)間，說(shuō)明數(shù)據(jù)庫(kù)比較空閑。DB Time:WORKLOAD REPOSITORY report for DB NameDB IdInstanceInst numReleaseRACHostICCI1314098396ICCI11YESHPGICCI1Snap IdSnap TimeSessionsCursors/SessionBegin Snap:267825Dec08 14:04:5024End Snap:268025Dec08 15:23:3726Elapsed: (mins)在79分鐘里（其間收集了3次快照數(shù)據(jù)），數(shù)據(jù)庫(kù)耗時(shí)11分鐘，RDA數(shù)據(jù)中顯示系統(tǒng)有8個(gè)邏輯CPU（4個(gè)物理CPU），CPU利用率只有大約2%（）。這也說(shuō)明選擇分析時(shí)間段很關(guān)鍵，要選擇能夠代表性能問(wèn)題的時(shí)間段。library cache用來(lái)存儲(chǔ)最近引用的數(shù)據(jù)字典。% Blocks changed per Read:Recursive Call %:Rollback per transaction %:Rows per Sort:顯示數(shù)據(jù)庫(kù)負(fù)載概況，將之與基線數(shù)據(jù)比較才具有更多的意義，如果每秒或每事務(wù)的負(fù)載變化不大，說(shuō)明應(yīng)用運(yùn)行比較穩(wěn)定。Sorts：每秒/每事務(wù)的排序次數(shù)Logons：每秒/每事務(wù)登錄的次數(shù)Executes：每秒/每事務(wù)SQL執(zhí)行次數(shù)Transactions：每秒事務(wù)數(shù)Blocks changed per Read：表示邏輯讀用于修改數(shù)據(jù)塊的比例Recursive Call：遞歸調(diào)用占所有操作的比率Rollback per transaction：每事務(wù)的回滾率Rows per Sort：每次排序的行數(shù)注:Oracle的硬解析和軟解析 　　提到軟解析(soft prase)和硬解析(hard prase)，就不能不說(shuō)一下Oracle對(duì)sql的處理過(guò)程?！　?zhí)行sql，返回結(jié)果(execute and return)　　其中，軟、硬解析就發(fā)生在第三個(gè)過(guò)程里。這個(gè)過(guò)程就叫硬解析。同Load Profile一節(jié)相同，這一節(jié)也沒(méi)有所謂“正確”的值，而只能根據(jù)應(yīng)用的特點(diǎn)判斷是否合適。buffer hit表示進(jìn)程從內(nèi)存中找到數(shù)據(jù)塊的比率，監(jiān)視這個(gè)值是否發(fā)生重大變化比這個(gè)值本身更重要。library hit表示Oracle從Library Cache中檢索到一個(gè)解析過(guò)的SQL或PL/SQL語(yǔ)句的比率，當(dāng)應(yīng)用程序調(diào)用SQL或存儲(chǔ)過(guò)程時(shí)，Oracle檢查L(zhǎng)ibrary Cache確定是否存在解析過(guò)的版本，如果存在，Oracle立即執(zhí)行語(yǔ)句；如果不存在，Oracle解析此語(yǔ)句，并在Library