常見等待事件說明
?
?
??? Oracle的等待事件是衡量Oracle運(yùn)行狀況的重要依據(jù)及指標(biāo)。?
??? 等待事件的概念是在Oracle7.0.1.2中引入的，大致有100個(gè)等待事件。在Oracle 8.0中這個(gè)數(shù)目增加到了大約150個(gè)，在Oracle8i中大約有200個(gè)事件,在Oracle9i中大約有360個(gè)等待事件。主要有兩種類別的等待事件，即空閑（idle）等待事件和非空閑（non-idle）等待事件。
??? 空閑事件指Oracle正等待某種工作,在診斷和優(yōu)化數(shù)據(jù)庫的時(shí)候,我們不用過多注意這部分事件。常見的空閑事件有:
?
??? dispatcher timer
??? lock element cleanup
??? Null event?
??? parallel query dequeue wait?
??? parallel query idle wait - Slaves?
??? pipe get?
??? PL/SQL lock timer?
??? pmon timer- pmon
??? rdbms ipc message
??? slave wait?
??? smon timer?
??? SQL*Net break/reset to client?
??? SQL*Net message from client?
??? SQL*Net message to client?
??? SQL*Net more data to client?
??? virtual circuit status?
??? client message
?
??? 非空閑等待事件專門針對Oracle的活動(dòng),指數(shù)據(jù)庫任務(wù)或應(yīng)用運(yùn)行過程中發(fā)生的等待，這些等待事件是我們在調(diào)整數(shù)據(jù)庫的時(shí)候應(yīng)該關(guān)注與研究的。一些常見的非空閑等待事件有:
?
??? db file scattered read
??? db file sequential read?
??? buffer busy waits?
??? free buffer waits?
??? enqueue
??? latch free?
??? log file parallel write?
??? log file sync
?
1、db file scattered read-DB 文件分散讀取
?
??? 這種情況通常顯示與全表掃描相關(guān)的等待。
?
??? 當(dāng)數(shù)據(jù)庫進(jìn)行全表掃時(shí)，基于性能的考慮，數(shù)據(jù)會(huì)分散(scattered)讀入 Buffer Cache。如果這個(gè)等待事件比較顯著，可能說明對于某些全表掃描的表，沒有創(chuàng)建索引或者沒有創(chuàng)建合適的索引，我們可能需要檢查這些數(shù)據(jù)表已確定是否進(jìn)行了正確的設(shè)置。
??? 然而這個(gè)等待事件不一定意味著性能低下，在某些條件下 Oracle 會(huì)主動(dòng)使用全表掃描來替換索引掃描以提高性能，這和訪問的數(shù)據(jù)量有關(guān)，在 CBO 下 Oracle 會(huì)進(jìn)行更為智能的選擇，在RBO下Oracle更傾向于使用索引。因?yàn)槿頀呙璞恢糜?LRU（Least Recently Used，最近最少適用）列表的冷端（cold end），對于頻繁訪問的較小的數(shù)據(jù)表，可以選擇把他們 Cache 到內(nèi)存中，以避免反復(fù)讀取。
??? 當(dāng)這個(gè)等待事件比較顯著時(shí)，可以結(jié)合 v$session_longops 動(dòng)態(tài)性能視圖來進(jìn)行診斷，該視圖中記錄了長時(shí)間（運(yùn)行時(shí)間超過 6 秒的）運(yùn)行的事物，可能很多是全表掃描操作（不管怎樣，這部分信息都是值得我們注意的）。
?
??? 我們通過通過一個(gè)案例分析來熟悉一下這個(gè)等待事件:
?
DB Name???????? DB Id??? Instance???? Inst Num Release???? Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
K2????????????1999167370 k2? ??????????????? 1 8.1.5.0.0?? NO??????K2
?
??????????? Snap Id???? Snap Time????? Sessions Curs/Sess Comment
??????????? ------- ------------------ -------- --------- -------------------
Begin Snap:?????170 25-Feb-03 10:00:11?????? 12?????? 4.5
? End Snap:?????176 25-Feb-03 15:00:05?????? 12?????? 5.0
?? Elapsed:?????????????299.90 (mins)
?
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
?????????????? Buffer Cache:??????? 24M????? Std Block Size:???????? 8K
?????????? Shared Pool Size:???????157M????????? Log Buffer:??????8388K
………………
?
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~???????????????????????????????????????????????????? % Total
Event?????????????????????????????????????????????? Waits??? Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
db file scattered read?????????????????????????16,842,920??3,490,719??? 43.32
latch free????????????????????????????????????????844,272???3,270,073??? 40.58
buffer busy waits?????????????????????????????????114,421?????933,136????11.58
db file sequential read?????????????????????????2,067,910?????117,750?????1.46
enqueue???????????????????????????????????????????????464?????110,840?????1.38
????????? -------------------------------------------------------------
?
??? 這是一個(gè)典型的性能低下的系統(tǒng)，幾個(gè)重要的等待事件都在 Top 5 中出現(xiàn)，其中，前 3 個(gè)等待極為顯著，需要進(jìn)行相應(yīng)的調(diào)整。
?
??? 在 5 小時(shí)的采樣間隔內(nèi)，其中 db file scattered read 累計(jì)等待時(shí)間約 10 小時(shí)，已經(jīng)成為影響系統(tǒng)性能的主要原因。了解了這些以后我們就可以進(jìn)一步察看相關(guān) SQL 看是否存在可以的 SQL 語句。
?
SQL ordered by Gets for DB:?K2? Instance:?k2? Snaps: 170 -176
-> End Buffer Gets Threshold:?? 10000
-> Note that resources reported for PL/SQL includes the resources used by
?? all SQL statements called within the PL/SQL code.? As individual SQL
?? statements are also reported, it is possible and valid for the summed
?? total % to exceed 100
?
???????????????????????????????????????????????????? CPU????? Elapsd
? Buffer Gets??? Executions? Gets per Exec? %Total Time (s)? Time (s) Hash Value
--------------- ------------ -------------- ------ -------- --------- ----------
??????6,480,163???????????12??????540,013.6??? 2.4???? 0.00????? 0.00 3791855498
SELECT "PROCESS_REQ"."WORK_ID", "PROCESS_REQ"."STOCK_NO","PROCESS_R
??????3,784,566???????????16????? 236,535.4??? 1.4???? 0.00????? 0.00?2932917818
SELECT * FROM FIND_LATER_WO ORDER BY NOTE,ORDER_NO?
??????1,200,976????????????3??????400,325.3???? .4???? 0.00????? 0.00?4122791109
SELECT "ITEM_STOCK"."ITEM_NO","ITEM"."NOTE",?"ITEM"
????????923,944????????????9??????102,660.4?????.3?????0.00????? 0.00?2200071737
SELECT "ITEM_STOCK"."ITEM_NO" ,?"ITEM_STOCK"."STOCK_NO" ,
????????921,301????????????3?????307,100.3?????.3?????0.00????? 0.00?2218843294
SELECT "ITEM_STOCK"."ITEM_NO",?"ITEM"."NOTE",?"ITEM"
?
………………
?
??? 注意到以上很多查詢導(dǎo)致的 Buffer Gets 都非常龐大，我們非常有理由懷疑索引存在問題，甚至缺少必要的索引。以上記錄的是 SQL 的片段，通過 Hash Value 值結(jié)合 v$sql_text 我們可以獲得完整的 SQL語句。
?
??? 在這次診斷中，我緊接著去查詢的是 v$session_longops 數(shù)據(jù)表，一個(gè)分組查詢的結(jié)果如下:
?
??? TARGET????????????????????????????????????COUNT(*)
??? ----------------------------------------- ----------
??? SA.PPBT_GRAPHOBJTABLE?????????????????????418
??? SA.PPBT_PPBTOBJRELATTABLE?????????????????53
?
??? 我們發(fā)現(xiàn)這些問題 SQL 的全表掃描(結(jié)合 v$session_longops 視圖中的 OPNAME)主要集中在 PPBT_GRAPHOBJTABLE 和 PPBT_PPBTOBJRELATTABLE 兩張數(shù)據(jù)表上。進(jìn)一步研究發(fā)現(xiàn)這兩個(gè)數(shù)據(jù)表上沒有任何索引，并且有相當(dāng)?shù)臄?shù)據(jù)量:
??? SQL> select count(*) from SA.PPBT_PPBTOBJRELATTABLE;
????COUNT(*)?
????----------?
???1209017?
??? SQL> select count(*) from SA.PPBT_GRAPHOBJTABLE;
????COUNT(*)?
????----------?
????2445
??? 在創(chuàng)建了合適的索引后，系統(tǒng)性能得到了大幅提高!
?
2、db file sequential read-DB 文件順序讀取
?
??? 這一事件通常顯示與單個(gè)數(shù)據(jù)塊相關(guān)的讀取操作(如索引讀取)。
?
??? 如果這個(gè)等待事件比較顯著，可能表示在多表連接中，表的連接順序存在問題，可能沒有正確的使用驅(qū)動(dòng)表；或者可能說明不加選擇地進(jìn)行索引。
?
??? 在大多數(shù)情況下我們說，通過索引可以更為快速的獲取記錄，所以對于一個(gè)編碼規(guī)范、調(diào)整良
?
好的數(shù)據(jù)庫，這個(gè)等待很大是很正常的。
?
??? 但是在很多情況下，使用索引并不是最佳的選擇，比如讀取較大表中大量的數(shù)據(jù)，全表掃描可
??? 能會(huì)明顯快于索引掃描，所以在開發(fā)中我們就應(yīng)該注意，對于這樣的查詢應(yīng)該進(jìn)行避免使用索引掃描。
?
3、Free Buffer-釋放緩沖區(qū)
?
??? 這個(gè)等待事件表明系統(tǒng)正在等待內(nèi)存中的可用空間，這說明當(dāng)前 Buffer 中已經(jīng)沒有 Free 的內(nèi)存空間。
?
??? 如果應(yīng)用設(shè)計(jì)良好，SQL 書寫規(guī)范，充分綁定變量，那這種等待可能說明 Buffer Cache 設(shè)置的偏小，你可能需要增大 DB_BUFFER_CACHE。
?
??? Free Buffer 等待可能說明 DBWR 的寫出速度不夠，或者磁盤存在嚴(yán)重的競爭，可以需要考慮增加檢查點(diǎn)、使用更多的 DBWR 進(jìn)程，或者增加物理磁盤的數(shù)量,分散負(fù)載，平衡 IO。
?
4、Buffer Busy-緩沖區(qū)忙
?
??? 該等待事件表示正在等待一個(gè)以unshareable方式使用的緩沖區(qū)，或者表示當(dāng)前正在被讀入buffer cache。一般來說Buffer Busy Wait不應(yīng)大于1%。
?
??? 檢查緩沖等待統(tǒng)計(jì)部分（或V$WAITSTAT），看一下等待是否位于段頭(Segment Header)。如果是，可以考慮增加自由列表（freelist，對于Oracle8i DMT）或者增加freelist groups（在很多時(shí)候這個(gè)調(diào)整是立竿見影的，在8.1.6之前，這個(gè)freelists參數(shù)不能動(dòng)態(tài)修改；在8.1.6及以后版本，動(dòng)態(tài)修改feelists需要設(shè)置COMPATIBLE至少為8.1.6）.
?
??? 其修改語法為：
?
??? SQL> alter table sp_item storage (freelists 2);
?
??? 表已更改。
?
??? 如果這一等待位于undo header，可以通過增加回滾段（rollback segment）來解決緩沖區(qū)的問題。如果等待位于undo block上，我們可能需要檢查相關(guān)應(yīng)用，適當(dāng)減少大規(guī)模的一致性讀取，或者降低一致性讀取(consistent read)的表中的數(shù)據(jù)密度或者增大DB_CACHE_SIZE。
?
??? 如果等待處于data block，可以考慮將頻繁并發(fā)訪問的表或數(shù)據(jù)移到另一數(shù)據(jù)塊或者進(jìn)行更大范圍的分布（可以增加pctfree值 ，擴(kuò)大數(shù)據(jù)分布，減少競爭），以避開這個(gè)"熱點(diǎn)"數(shù)據(jù)塊，或者可以考慮增加表中的自由列表或使用本地化管理的表空間（Locally Managed Tablespaces）。
?
??? 如果等待處于索引塊，應(yīng)該考慮重建索引、分割索引或使用反向鍵索引。為了防止與數(shù)據(jù)塊相關(guān)的緩沖忙等待，也可以使用較小的塊：在這種情況下，單個(gè)塊中的記錄就較少，所以這個(gè)塊就不是那么"繁忙"；或者可以設(shè)置更大的pctfree,使數(shù)據(jù)擴(kuò)大物理分布，減少記錄間的熱點(diǎn)競爭。?
??? 在執(zhí)行DML (insert/update/ delete)時(shí)，Oracle向數(shù)據(jù)塊中寫入信息，對于多事務(wù)并發(fā)訪問的數(shù)據(jù)表，關(guān)于ITL的競爭和等待可能出現(xiàn)，為了減少這個(gè)等待，可以增加initrans，使用多個(gè)ITL槽。以下是一個(gè)生產(chǎn)系統(tǒng)v$waitstat試圖所顯示的等待信息:
?
??? SQL> select * from v$waitstat where count<>0 or time <>0;
??? CLASS??????? COUNT???? TIME
??? ---------------------?-------------
??? data block?? 453???????6686
??? undo header?391?????? 1126
??? undo block?? 172???????3
??? 在 Oracle9i 中，引入了一個(gè)新概念:ASSM（Segment Space Management Auto）。通過這個(gè)新特性 Oracle 使用位圖來管理空間使用。 ASSM 結(jié)合 LMT 徹底改變了 Oracle 的存儲(chǔ)機(jī)制，位圖 freelist 能夠減輕緩沖區(qū)忙等待（buffer busy??wait），這個(gè)問題在 Oracle9i 以前的版本里曾是一個(gè)嚴(yán)重的問題。
?
??? Oracle 宣稱 ASSM 顯著地提高了 DML 并發(fā)操作的性能，因?yàn)椋ㄍ粋€(gè)）位圖的不同部分可以被同時(shí)使用，這樣就消除了尋找剩余空間的串行化。根據(jù) Oracle 的測試結(jié)果，使用位圖 freelist 會(huì)消除所有分段頭部（對資源）的爭奪，還能獲得超快的并發(fā)插入操作在 Oracle9i 之中，Buffer Busy wait 不再常見！
?
?
5、latch free-latch 釋放
?
??? latch是一種低級排隊(duì)機(jī)制，用于保護(hù)SGA中共享內(nèi)存結(jié)構(gòu)。
?
??? latch就像是一種快速地被獲取和釋放的內(nèi)存鎖。用于防止共享內(nèi)存結(jié)構(gòu)被多個(gè)用戶同時(shí)訪問。如果latch不可用，就會(huì)記錄latch釋放失敗(latch free miss )。
?
??? 有兩種與閂有關(guān)的類型：
??? ■ 立刻。
??? ■ 可以等待。
?
??? 假如一個(gè)進(jìn)程試圖在立刻模式下獲得閂，而該閂已經(jīng)被另外一個(gè)進(jìn)程所持有，如果該閂不能立可用的話，那么該進(jìn)程就不會(huì)為獲得該閂而等待。它將繼續(xù)執(zhí)行另一個(gè)操作。
?
??? 大多數(shù)latch問題都與以下操作相關(guān)：
?
??? 沒有很好的是用綁定變量（library cache latch）、重作生成問題（redo allocation latch）、緩沖存儲(chǔ)競爭問題（cache buffers LRU chain），以及buffer cache中的存在"熱點(diǎn)"塊（cache buffers chain）。
?
??? 通常我們說，如果想設(shè)計(jì)一個(gè)失敗的系統(tǒng)，不考慮綁定變量，這一個(gè)條件就夠了，對于異構(gòu)性強(qiáng)的系統(tǒng)，不使用綁定變量的后果是極其嚴(yán)重的。
?
??? 另外也有一些latch等待與bug有關(guān)，應(yīng)當(dāng)關(guān)注Metalink相關(guān)bug的公布及補(bǔ)丁的發(fā)布。當(dāng)latch miss ratios大于0.5%時(shí)，就應(yīng)當(dāng)研究這一問題。
?
??? Oracle的latch機(jī)制是競爭，其處理類似于網(wǎng)絡(luò)里的CSMA/CD，所有用戶進(jìn)程爭奪latch， 對于愿意等待類型(willing-to-wait)的latch,如果一個(gè)進(jìn)程在第一次嘗試中沒有獲得latch,那么它會(huì)等待并且再嘗試一次,如果經(jīng)過_spin_count次爭奪不能獲得latch, 然后該進(jìn)程轉(zhuǎn)入睡眠狀態(tài)，持續(xù)一段指定長度的時(shí)間，然后再次醒來，按順序重復(fù)以前的步驟.在8i/9i中默認(rèn)值是_spin_count=2000。
?
??? 如果SQL語句不能調(diào)整，在8.1.6版本以上，Oracle提供了一個(gè)新的初始化參數(shù): CURSOR_SHARING 可以通過設(shè)置CURSOR_SHARING = force 在服務(wù)器端強(qiáng)制綁定變量。設(shè)置該參數(shù)可能會(huì)帶來一定的副作用，對于Java的程序，有相關(guān)的bug，具體應(yīng)用應(yīng)該關(guān)注Metalink的bug公告。
?
??? 以下我們簡單來看一下對栓的查詢及跟蹤:
?
??? SQL> select addr,name
?
?????? 2 from v$latch_children
?????? 3 where name like '%chain%'
?????? 4 /
?
??? ADDR???????????? NAME
??? ---------------- ------------------------------
??? 00000003C0558AE8 enqueue hash chains
??? 00000003C0558A58 enqueue hash chains
??? 00000003C05589C8 enqueue hash chains
??? 00000003C0558938 enqueue hash chains
??? 00000003C05588A8 enqueue hash chains
??? 00000003C0558818 enqueue hash chains
??? 00000003C0558788 enqueue hash chains
??? 00000003C05586F8 enqueue hash chains
??? 00000003C1FC7830 cache buffers lru chain
??? 00000003C1FC7448 cache buffers lru chain
??? 00000003C1FC7060 cache buffers lru chain
??? 00000003C1FC6C78 cache buffers lru chain
??? 00000003C247D970 cache buffers chains
??? 00000003C247C8C0 cache buffers chains
??? 00000003C247B810 cache buffers chains
??? 00000003C247A760 cache buffers chains
??? 00000003C24796B0 cache buffers chains
??? 00000003C2478600 cache buffers chains
??? 00000003C2477550 cache buffers chains
??? 00000003C24764A0 cache buffers chains
??? 00000003C24753F0 cache buffers chains?
??? 00000003C2474340 cache buffers chains
??? ………
?
??? SQL> col segment_name for a40
??? SQL> set linesize 120
??? SQL> l
?????? 1 select /*+ ordered */
?????? 2?????? e.owner ||'.'|| e.segment_name segment_name,
?????? 3?????? e.extent_id extent#,
?????? 4?????? x.dbablk - e.block_id + 1 block#,
?????? 5?????? x.tch,
?????? 6?????? l.child#
?????? 7?from
?????? 8?????? sys.v$latch_children l,
?????? 9?????? sys.x$bh x,
????? 10???????sys.dba_extents e
????? 11 where
????? 12???????l.name?= 'cache buffers chains' and
????? 13???????l.sleeps > &sleep_count and
????? 14???????x.hladdr = l.addr and
????? 15???????e.file_id = x.file# and
????? 16?????? x.dbablk between e.block_id and e.block_id + e.blocks - 1
?????
17 /
?
??? Enter value for sleep_count: 10000
?
??? old 13:???????? l.sleeps > &sleep_count and
??? new 13:????????? l.sleeps > 10000 and
?
??? SEGMENT_NAME?????????????????????????????EXTENT#????BLOCK#?????TCH??????? CHILD#
??? ---------------------------------------- ---------- ---------- ---------- ----------
??? SYS.I_ACCESS1????????????????????????????14?????????16???????? 12???????? 1001
??? SYS.I_DEPENDENCY2????????????????????????2????????? 11??????? ?25???????804
??? SYS.I_DEPENDENCY2????????????????????????2????????? 13???????? 23??????? ?806
??? SYS.I_DEPENDENCY1????????????????????????2????????? 10?????????1????????? 1019
??? SYS.I_DEPENDENCY1????????????????????????2????????? 11??????? ?2????????? 1020
??? …………
??? HSCONTENT.HSIDX_INFO_PARAM???????????????50??????? ?2????????? 35635????? 1017
??? HSCONTENT.HSIDX_INFO_PARAM???????????????50??????? ?3????????? 51269????? 1018
??? HSCONTENT.HSIDX_INFO_PARAM???????????????50??????? ?4????????? 32415????? 1019
??? HSCONTENT.HSIDX_INFO_PARAM???????????????50??????? ?5????????? 51956????? 1020
??? HSCONTENT.HSIDX_INFO_PARAM???????????????50???????? 6????????? 57509????? 1021
??? HSCONTENT.PK_HS_DLF_OBJECT???????????????11???????? 2????????? 4434?????? 809
??? HSCONTENT.PK_HS_DLF_OBJECT???????????????11??????? ?6??????????5738?????? 813
??? HSCONTENT.PK_HS_DLF_OBJECT???????????????14????????2??????????524????????1001
??? HSCONTENT.PK_HS_DLF_OBJECT???????????????23????????2????????? 4843??????1001
??? HSCONTENT.PK_HS_DLF_OBJECT???????????????24????????2???????? ?4361??????1001
??? HSCONTENT.PK_HS_DLF_OBJECT???????????????25??????? ?13???????? 2507??????804?
??? …………
?
?
6、Enqueue
?
??? enqueue是一種保護(hù)共享資源的鎖定機(jī)制。該鎖定機(jī)制保護(hù)共享資源，如記錄中的數(shù)據(jù)，以避免兩個(gè)人在同一時(shí)間更新同一數(shù)據(jù)。enqueue包括一個(gè)排隊(duì)機(jī)制，即FIFO（先進(jìn)先出）排隊(duì)機(jī)制。
?
??? Enqueue等待常見的有ST、HW 、TX 、TM等
?
??? ST enqueue，用于空間管理和字典管理的表空間(DMT)的區(qū)間分配，在DMT中典型的是對于uet$ 和fet$數(shù)據(jù)字典表的爭用。對于支持LMT的版本，應(yīng)該盡量使用本地管理表空間. 或者考慮手工預(yù)分配一定數(shù)量的區(qū)(Extent)，減少動(dòng)態(tài)擴(kuò)展時(shí)發(fā)生的嚴(yán)重隊(duì)列競爭。
?
??? 我們通過一個(gè)實(shí)例來看一下:
?
DB Name???????? DB Id??? Instance???? Inst Num Release???? Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DB????????????407573460 aaa?????????????????1 8.1.7.4.0?? NO??????server
?
??????????? Snap Id???? Snap Time??????Sessions Curs/Sess Comment
??????????? ------- ------------------- -------- --------- -------------------
Begin Snap:????2845 31-10月-03 02:10:16?????? 46?????? 4.5
? End Snap:???2848 31-10月-03 03:40:05?????? 46?????? 5.0
?? Elapsed:??????????????89.82 (mins)
?
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
?????????????? Buffer Cache:????????51M????? Std Block Size:????????16K
?????????? Shared Pool Size:???????209M????????? Log Buffer:??????2097K
………………
?
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~???????????????????????????????????????????????????? % Total
Event?????????????????????????????????????????????? Waits??? Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
enqueue????????????????????????????????????????? ??53,793??16,192,686??? 67.86
rdbms ipc message????????????????????????????????? 19,999???5,927,350??? 24.84
pmon timer???????????????????????????????????????? ?1,754?????538,797????2.26
smon timer??????????????????????????????????????????? ?17?????522,281?????2.19
SQL*Net message from client????????????????????????94,525?????520,104?????2.18
????????? -------------------------------------------------------------
??? 在Statspack分析中，Top 5等待事件是我們最為關(guān)注的部分。
?
??? 這個(gè)系統(tǒng)中，除了enqueue 等待事件以外，其他4個(gè)都屬于空閑等待事件，無須關(guān)注。我們來關(guān)注一下enqueue 等待事件，在89.82 (mins)的采樣間隔內(nèi)，累計(jì)enqueue等待長達(dá)16,192,686cs,即45小時(shí)左右。這個(gè)等待已經(jīng)太過顯著，實(shí)際上這個(gè)系統(tǒng)也正因此遭遇了巨大的困難，觀察到隊(duì)列等待以后，我們就應(yīng)該關(guān)注隊(duì)列等待在等待什么資源。快速跳轉(zhuǎn)的Statspack的其他部分，我們看到以下詳細(xì)內(nèi)容:
?
Enqueue activity for DB: DB Instance: aaa Snaps: 2716 -2718
-> ordered by waits desc, gets desc
?
Enqueue?????Gets???????Waits
---------- ------------ ----------
ST???????? 1,554??????? 1,554
-------------------------------------------------------------
?
??? 我們看到主要隊(duì)列等待在等待ST鎖定，對于DMT，我們說這個(gè)等待跟FET$,UET$的爭用緊密相關(guān)。我們在回過頭來研究捕獲的SQL語句:
?
-> End Buffer Gets Threshold:??? 10000
-> Note that resources reported for PL/SQL includes the resources used by
?? all SQL statements called within the PL/SQL code. As individual SQL
?? statements are also reported, it is possible and valid for the summed
?? total % to exceed 100
?
? Buffer Gets??? Executions? Gets per Exec? %Total?Hash Value
--------------- ------------ -------------- ------ ----------
????? 4,800,073?????? 10,268????????? 467.5???51.0 2913840444
select length from fet$ where file#=:1 and block#=:2 and ts#=:3?
??????? 803,187???????10,223??????????78.6??? 8.5?528349613
delete from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4
??????? 454,444?????? 10,300?????????? 44.1??? 4.8?1839874543
select file#,block#,length from uet$ where ts#=:1 and segfile#=:2 and segblock#=:3 and ext#=:4
???????? 23,110???????10,230??????????? 2.3??? 0.2?3230982141
insert into fet$ (file#,block#,ts#,length) values (:1,:2,:3,:4)?
???????? 21,201????????? 347?????????? 61.1??? 0.2 1705880752
select file# from file$ where ts#=:1
?
…………
?
????????? 9,505?????????? 12????????? 792.1??? 0.1?1714733582
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t where t.ts#=f.ts# and t.dflextpct!=0 and t.bitmapped=0
????????? 6,426??????????235?????????? 27.3??? 0.1 1877781575
delete from fet$ where file#=:1 and block#=:2 and ts#=:3?
??? 我們看到數(shù)據(jù)庫頻繁操作UET$,FET$系統(tǒng)表已經(jīng)成為了系統(tǒng)的主要瓶頸。至此，我們已經(jīng)可以準(zhǔn)確的為該系統(tǒng)定位問題，相應(yīng)的解決方案也很容易確定，在8.1.7中，使用LMT代替DMT，這是解決問題的根本辦法，當(dāng)然實(shí)施起來還要進(jìn)行綜合考慮，實(shí)際情況還要復(fù)雜得多。
?
?
??? HW enqueue指和段的高水位標(biāo)記相關(guān)等待；手動(dòng)分配適當(dāng)區(qū)可以避免這一等待。
?
??? TX是最常見的enqueue等待。TX enqueue等待通常是以下三個(gè)問題之一產(chǎn)生的結(jié)果。
?
??? 第一個(gè)問題是唯一索引中的重復(fù)索引，你需要執(zhí)行提交（commit）/回滾（rollback）操作來釋放 enqueue。
??? 第二個(gè)問題是對同一位圖索引段的多次更新。因?yàn)閱蝹€(gè)位圖段可能包含多個(gè)行地址（rowid），所以當(dāng)多個(gè)用戶試圖更新同一段時(shí)，可能一個(gè)用戶會(huì)鎖定其他用戶請求的記錄，這時(shí)等待出現(xiàn)。直到獲得鎖定的用戶提交或回滾， enqueue釋放。
??? 第三個(gè)問題，也是最可能發(fā)生的問題是多個(gè)用戶同時(shí)更新同一個(gè)塊。如果沒有足夠的ITL槽，就會(huì)發(fā)生塊級鎖定。通過增大initrans和/或maxtrans以允許使用多個(gè)ITL槽（對于頻繁并發(fā)進(jìn)行DML操作的數(shù)據(jù)表，在建表之初就應(yīng)該考慮為相應(yīng)參數(shù)設(shè)置合理的數(shù)值，避免系統(tǒng)運(yùn)行以后在線的更改，在8i之前，freelists等參數(shù)不能在線更改，設(shè)計(jì)時(shí)的考慮就尤為重要），或者增大表上的pctfree值，就可以很容易的避免這種情況。
?
??? TM enqueue隊(duì)列鎖在進(jìn)行DML操作前獲得，以阻止對正在操作的數(shù)據(jù)表進(jìn)行任何DDL操作(在DML操作一個(gè)數(shù)據(jù)表時(shí)，其結(jié)構(gòu)不能被更改)。
?
?
7、Log Buffer Space-日志緩沖空間
?
??? 當(dāng)你將日志緩沖（log buffer）產(chǎn)生重做日志的速度比 LGWR 的寫出速度快，或者是當(dāng)日志切換（log switch）太慢時(shí)，就會(huì)發(fā)生這種等待。這個(gè)等待出現(xiàn)時(shí)，通常表明 redo log buffer 過小，為解決這個(gè)問題，可以考慮增大日志文件的大小，或者增加日志緩沖器的大小。
?
??? 另外一個(gè)可能的原因是磁盤 I/O 存在瓶頸，可以考慮使用寫入速度更快的磁盤。在允許的條件下設(shè)置可以考慮使用裸設(shè)備來存放日志文件，提高寫入效率。在一般的系統(tǒng)中，最低的標(biāo)準(zhǔn)是，不要把日志文件和數(shù)據(jù)文件存放在一起，因?yàn)橥ǔＨ罩疚募粚懖蛔x，分離存放可以獲得性能提升。
?
??? 以下是一個(gè) log buffer 存在問題的 statspack Top5 等待事件的系統(tǒng):
Top 5 Wait Events
~~~~~~~~~~~~~~~???????????????????????????????????????????Wait????????% Total
Event??????????????????????????????????????? Waits????????Time (cs)????Wt Time
-------------------------------------------- ------------ ------------ -------
log file parallel write????????????????????? 1,436,993????1,102,188??? 10.80
log buffer space?????????????????????????? ? 16,698???????873,203????? 8.56
log file sync??????????????????????????????? 1,413,374????654,587????? 6.42
control file parallel write??????????????????329,777??????510,078????? 5.00
db file scattered read????????????????????? ?425,578??????132,537????? 1.30
-------------------------------------------------------------
?
?
8、Log File Switch-日志文件切換
?
??? 當(dāng)這個(gè)等待出現(xiàn)時(shí)，表示所有的提交(commit)的請求都需要等待"日志文件切換"的完成。
?
??? Log file Switch 主要包含兩個(gè)子事件:
?
??? log file switch (archiving needed)
??? log file switch (checkpoint incomplete)
??? log file switch (archiving needed)
?
??? 這個(gè)等待事件出現(xiàn)時(shí)通常是因?yàn)槿罩窘M循環(huán)寫滿以后，第一個(gè)日志歸檔尚未完成，出現(xiàn)該等待。出現(xiàn)該等待，可能表示 io 存在問題。
?
??? 解決辦法：
?
??? 可以考慮增大日志文件和增加日志組
??? 移動(dòng)歸檔文件到快速磁盤
??? 調(diào)整 log_archive_max_processes .
?
??? log file switch (checkpoint incomplete)-日志切換（檢查點(diǎn)未完成）
?
??? 當(dāng)你的日志組都寫完以后，LGWR 試圖寫第一個(gè) log file，如果這時(shí)數(shù)據(jù)庫沒有完成寫出記錄在第一個(gè) log file 中的 dirty 塊時(shí)（例如第一個(gè)檢查點(diǎn)未完成），該等待事件出現(xiàn)。該等待事件通常表示你的 DBWR 寫出速度太慢或者 IO 存在問題。為解決該問題，你可能需要考慮增加額外的 DBWR 或者增加你的日志組或日志文件大小。
?
?
9、log file sync-日志文件同步
?
??? 當(dāng)一個(gè)用戶提交或回滾數(shù)據(jù)時(shí)，LGWR 將會(huì)話期的重做由日志緩沖器寫入到重做日志中。日志文件同步過程必須等待這一過程成功完成。
?
??? 為了減少這種等待事件，可以嘗試一次提交更多的記錄（頻繁的提交會(huì)帶來更多的系統(tǒng)開銷）。將重做日志置于較快的磁盤上，或者交替使用不同物理磁盤上的重做日志，以降低歸檔對 LGWR的影響。
?
??? 對于軟 RAID，一般來說不要使用 RAID 5，RAID5 對于頻繁寫入得系統(tǒng)會(huì)帶來較大的性能損失，可以考慮使用文件系統(tǒng)直接輸入/輸出，或者使用裸設(shè)備（raw device），這樣可以獲得寫入的性能提高。
?
10、log file single write
?
??? 該事件僅與寫日志文件頭塊相關(guān)，通常發(fā)生在增加新的組成員和增進(jìn)序列號(hào)時(shí)。頭塊寫單個(gè)進(jìn)行，因?yàn)轭^塊的部分信息是文件號(hào)，每個(gè)文件不同。更新日志文件頭這個(gè)操作在后臺(tái)完成，一般很少出現(xiàn)等待，無需太多關(guān)注。
?
11、log file parallel write
?
??? 從 log buffer 寫 redo 記錄到 redo log 文件，主要指常規(guī)寫操作(相對于 log file sync)。
?
??? 如果你的 Log group 存在多個(gè)組成員，當(dāng) flush log buffer 時(shí)，寫操作是并行的，這時(shí)候此等待事件可能出現(xiàn)。
?
??? 盡管這個(gè)寫操作并行處理，直到所有 I/O 操作完成該寫操作才會(huì)完成(如果你的磁盤支持異步 IO 或者使用 IO SLAVE，那么即使只有一個(gè) redo log file member,也有可能出現(xiàn)此等待)。
?
??? 這個(gè)參數(shù)和 log file sync 時(shí)間相比較可以用來衡量 log file 的寫入成本。通常稱為同步成本率。
?
?
12、control file parallel write-控制文件并行寫
?
??? 當(dāng) server 進(jìn)程更新所有控制文件時(shí)，這個(gè)事件可能出現(xiàn)。如果等待很短，可以不用考慮。如果等待時(shí)間較長，檢查存放控制文件的物理磁盤 I/O 是否存在瓶頸。
?
??? 多個(gè)控制文件是完全相同的拷貝，用于鏡像以提高安全性。對于業(yè)務(wù)系統(tǒng)，多個(gè)控制文件應(yīng)該存放在不同的磁盤上，一般來說三個(gè)是足夠的，如果只有兩個(gè)物理硬盤，那么兩個(gè)控制文件也是可以接受的。在同一個(gè)磁盤上保存多個(gè)控制文件是不具備實(shí)際意義的。
?
??? 減少這個(gè)等待，可以考慮如下方法：
?
??? 減少控制文件的個(gè)數(shù)(在確保安全的前提下)
??? 如果系統(tǒng)支持，使用異步 IO
??? 轉(zhuǎn)移控制文件到 IO 負(fù)擔(dān)輕的物理磁盤
?
13、control file sequential read/ control file single write 控制文件連續(xù)讀/控制文件單個(gè)
寫
?
??? 對單個(gè)控制文件 I/O 存在問題時(shí)，這兩個(gè)事件會(huì)出現(xiàn)。
?
??? 如果等待比較明顯，檢查單個(gè)控制文件，看存放位置是否存在 I/O 瓶頸。
?
??? 使用查詢獲得控制文件訪問狀態(tài)：
?
select P1 from V$SESSION_WAIT
?where EVENT like 'control file%' and STATE='WAITING';?
??? 解決辦法：
?
??? 移動(dòng)有問題的控制文件到快速磁盤
??? 如果系統(tǒng)支持，啟用異步 I/O
?
?
14、direct path write-直接路徑寫
?
??? 該等待發(fā)生在，系統(tǒng)等待確認(rèn)所有未完成的異步 I/O 都已寫入磁盤。
?
??? 對于這一寫入等待，我們應(yīng)該找到 I/O 操作最為頻繁的數(shù)據(jù)文件(如果有過多的排序操作，很有可能就是臨時(shí)文件)，分散負(fù)載，加快其寫入操作。
?
??? 如果系統(tǒng)存在過多的磁盤排序，會(huì)導(dǎo)致臨時(shí)表空間操作頻繁，對于這種情況，可以考慮使用 Local 管理表空間，分成多個(gè)小文件，寫入不同磁盤或者裸設(shè)備。
?
??? 我們可以看一個(gè) report 的典型例子:
?
DB Name???????? DB Id??? Instance???? Inst Num Release???? Cluster Host
------------ ----------- ------------ -------- ----------- ------- ------------
DB?????????????294605295 db?????????????????1 8.1.5.0.0?? NO??????IBM
?
??????????? Snap Id???? Snap Time??????Sessions Curs/Sess Comment
??????????? ------- ------------------- -------- --------- -------------------
Begin Snap:????? 65 08-11月-03 16:32:42?????? 46?????? 4.5
? End Snap:????? 66?08-11月-03 16:54:00?????? 46?????? 5.0
?? Elapsed:??????????????21.30 (mins)
?
??? 這是一個(gè) 20 分鐘的采樣報(bào)告
?
Cache Sizes (end)
~~~~~~~~~~~~~~~~~
?????????????? Buffer Cache:????????51M????? Std Block Size:????????16K
?????????? Shared Pool Size:???????209M????????? Log Buffer:??????2097K
………………
?
Top 5 Timed Events
~~~~~~~~~~~~~~~~~~???????????????????????????????????????????????????? % Total
Event?????????????????????????????????????????????? Waits??? Time (s) Ela Time
-------------------------------------------- ------------ ----------- --------
direct path write??????????????????????????????????98,631???????3,651??? 44.44
log file switch completion????????????????????????????62?????? 2,983??? 36.31
direct path read???????????????????????????????????37,434?????? 1,413????17.20
db file sequential read????????????????????????????????86??????? ?109?????1.33
control file sequential read????????????????????????3,862???????? ?34?????.41
????????? -------------------------------------------------------------
??? 我們注意到在 Top 5 等待事件中，最為顯著的等待事件就是 direct path write。
?
??? 基于此，我們繼續(xù)向下追查相關(guān)排序部分統(tǒng)計(jì)數(shù)據(jù):
?
Instance Activity Stats for DB: DB? Instance:?db? Snaps:?65 -66
?
Statistic????????????????????????????????????? Total???? per Second??? per Trans
--------------------------------- ------------------ -------------- ------------
…………
sorts (disk)????????????????????????????????????? 64????????????0.1????????? 0.4
sorts (memory)?????????????????????????????????? 861?????????? ?0.7????????? 4.7
sorts (rows)???????????????????????????????2,804,580?????? 15,159.9?????
…………
?
??? 64次的 sort disk,相當(dāng)顯著的磁盤排序，對于這種情況，我們可以很容易的猜測到，臨時(shí)表空間的讀寫操作肯定相當(dāng)頻繁:
?
File IO Statistics for DB: GHCXSDB Instance: ghcxsdb Snaps:??65-?66
?
Tablespace?????????????????????????? Filename
------------------------ ----------------------------------------------------
Reads????????? Avg Blks?Rd Avg Rd (ms) Writes Tot?Waits?? Avg Wait (ms)
-------------- ----------- ----------- ----------------- -------------
………
PERFSTAT???????????????? D:\ORACLE\ORADATA\PERFSTAT.DBF
?????????????? 88??????????1.0???????? 12.5???????821?????0
RBS??????????????????????D:\ORACLE\ORADATA\GHCXSDB\RBS01.DBF
???????????????7???????????1.0?????????0.0??????? 1,399?? 0
SYSTEM???????????????????D:\ORACLE\ORADATA\GHCXSDB\SYSTEM01.DBF
???????????????17??????????1.0????????11.8???????50????? 0
TEMP?????????????????????D:\ORACLE\ORADATA\GHCXSDB\TEMP01.DBF
?????????????? 223,152?????1.5?????????0.2????????371,303 0
??? 對于這種情況，我們建議，可以適當(dāng)增加 sort_area_size 的大小，以縮減磁盤排序?qū)τ谟脖P的寫入，從而提高系統(tǒng)及應(yīng)用相應(yīng)。有一個(gè)測試數(shù)字可以參考:磁盤排序的時(shí)間大約是內(nèi)存排序的 14000 倍。
?
15、slave wait-從屬進(jìn)程等
?
??? Slave Wait 是 Slave I/O 進(jìn)程等待請求，是一個(gè)空閑參數(shù)，一般不說明問題。
?
16、Idle Event-空閑事件
??? 最后我們來看幾個(gè)空閑等待事件。
?
??? 一般來說，空閑等待是指系統(tǒng)因?yàn)闊o事可做的等待，或者等待用戶的請求或響應(yīng)等，通常我們可以忽略這些等待事件。
?
??? 空閑事件可以通過 stats$idle_event 表查詢得到。
?
??? 我們看一下系統(tǒng)的主要空閑等待事件，對這些事件大家應(yīng)該有個(gè)大致的印象，如果你的 Top 5 等待事件中，主要都是這些事件，那么一般來說你的系統(tǒng)是比價(jià)清閑的:?
??? SQL> select * from stats$idle_event;
??? EVENT
??? ------------------------
??? smon timer
??? pmon timer
??? rdbms ipc message
??? Null event
??? parallel query dequeue
??? pipe get
??? client message
??? SQL*Net message to client
??? SQL*Net message from client
??? SQL*Net more data from client
??? dispatcher timer
??? virtual circuit status
??? lock manager wait for remote message
??? PX Idle Wait
??? wakeup time manager
????
??? 15 rows selected.
-The End-