Oracle 等待事件

    oracle等待事件是衡量oracle執行狀況的重要依據及指示，等待事件分為兩類：空閒等待事件和非空閒等待事件, TIMED_STATISTICS = TRUE 那麼等待事件按等待的時間排序，= FALSE那麼事件按等待的數量排序。執行statspack期間必須session上設定TIMED_STATISTICS = TRUE，否則統計的資料將失真。空閒等待事件是oracle正等待某種工作，在診斷和優化資料庫時候，不用過多注意這部分事件，非空閒等待事件專門針對oracle的活動，指資料庫任務或應用程式執行過程中發生的等待，這些等待事件是我們在調整資料庫應該關注的。
ojtJb;@;dr,[9390331    對於常見的等待事件，說明如下：
KOL kV93903311)    db file scattered read
Rf!Py(M!r-O"FK.A _s9390331該事件通常與全表掃描或者fast full index scan有關。因為全表掃描是被放入記憶體中進行的進行的，通常情況下基於效能的考慮，有時候也可能是分配不到足夠長的連續記憶體空間，所以會將資料塊分散(scattered)讀入Buffer Cache中。該等待過大可能是缺少索引或者沒有合適的索引(可以調整optimizer_index_cost_adj) 。這種情況也可能是正常的，因為執行全表掃描可能比索引掃描效率更高。當系統存在這些等待時，需要通過檢查來確定全表掃描是否必需的來調整。因為全表掃描被置於LRU(Least Recently Used，最近最少適用)列表的冷端(cold end)，對於頻繁訪問的較小的資料表，可以選擇把他們Cache 到記憶體中，以避免反覆讀取。當這個等待事件比較顯著時，可以結合v$session_longops 動態效能檢視來進行診斷，該檢視中記錄了長時間(執行時間超過6 秒的)執行的事物，可能很多是全表掃描操作(不管怎樣，這部分資訊都是值得我們注意的)。ITPUB個人空間7Wg.M#M.nf4D Jx
關於引數OPTIMIZER_INDEX_COST_ADJ＝n：該引數是一個百分比值，預設值為100，可以理解為FULL SCAN COST/INDEX SCAN COST。當n%* INDEX SCAN COSTITPUB個人空間;u8Sy:J K SL?2Y8}
2)    db file sequential read：該事件說明在單個資料塊上大量等待，該值過高通常是由於表間連線順序很糟糕（沒有正確選擇驅動行源），或者使用了非選擇性索引。通過將這種等待與statspack報表中已知其它問題聯絡起來(如效率不高的sql),通過檢查確保索引掃描是必須的，並確保多表連線的連線順序來調整。
!Cf5G-~b*` r93903313)    buffer busy wait：當緩衝區以一種非共享方式或者如正在被讀入到緩衝時,就會出現該等待。該值不應該大於1%。當出現等待問題時，可以檢查緩衝等待統計部分(或V$WAITSTAT)，確定該等待發生在什麼位置：
0A4mU?'yg\/}z9390331a)    如果等待是否位於段頭(Segment Header)。這種情況表明段中的空閒列表（freelist）的塊比較少。可以考慮增加空閒列表(freelist，對於Oracle8i DMT)或者增加freelist groups(在很多時候這個調整是立竿見影的(alter table tablename strorage(freelists 2))，在8.1.6之前，這個freelists引數不能動態修改;在8.1.6及以後版本，動態修改feelists需要設定COMPATIBLE至少為8.1.6)。也可以增加PCTUSED與PCTFREE之間距離（PCTUSED-to-pctfree gap），其實就是說降低PCTUSED的值，儘快使塊返回freelist列表被重用。如果支援自動段空間管理（ASSM），也可以使用ASSM模式，這是在ORALCE 920以後的版本中新增的特性。
{ dJM!I;`9390331b)    如果這一等待位於undo header，可以通過增加回滾段(rollback segment)來解決緩衝區的問題。ITPUB個人空間$Nt.h5qxh
c)    如果等待位於undo block上，我們需要增加提交的頻率，使block可以儘快被重用；使用更大的回滾段；降低一致讀所選擇的表中資料的密度；增大DB_CACHE_SIZE。ITPUB個人空間'Mj P*h6N
d)    如果等待處於data block，表明出現了hot block，可以考慮如下方法解決： ①將頻繁併發訪問的表或資料移到另一資料塊或者進行更大範圍的分佈(可以增大pctfree值 ，擴大資料分佈，減少競爭)，以避開這個"熱點"資料塊。②也可以減小資料塊的大小，從而減少一個資料塊中的資料行數，降低資料塊的熱度，減小競爭；③檢查對這些熱塊操作的SQL語句，優化語句。④增加hot block上的initrans值。但注意不要把initrans值設定的過於高了，通常設定為5就足夠了。因為增加事務意味著要增加ITL事務槽，而每個ITL事務槽將佔用資料塊中24個位元組長度。預設情況下，每個資料塊或者索引塊中是ITL槽是2個，在增加initrans的時候，可以考慮增大資料塊所在的表的PCTFREE值，這樣Oracle會利用PCTFREE部分的空間增加ITL slot數量，最大達到maxtrans指定。ITPUB個人空間5^Q0p8Z{/T8W
e)    如果等待處於index block，應該考慮重建索引、分割索引或使用反向鍵索引。為了防止與資料塊相關的緩衝忙等待，也可以使用較小的塊，在這種情況下，單個塊中的記錄就較少，所以這個塊就不是那麼"繁忙"。或者可以設定更大的PCTFREE,使資料擴大物理分佈，減少記錄間的熱點競爭。在執行DML (insert/update/ delete)時，Oracle向資料塊中寫入資訊，對於多事務併發訪問的資料表，關於ITL的競爭和等待可能出現，為了減少這個等待，可以增加initrans，使用多個ITL槽。在Oracle9i 中，可以使用ASSM這個新特性Oracle 使用點陣圖來管理空間使用，減小爭用。ITPUB個人空間i+]dW;{)] i_
4)    latch free：當閂鎖丟失率高於0.5%時，需要調整這個問題。詳細的我們在後面的Latch Activity for DB部分說明。ITPUB個人空間 J4q6ATG r E
5)    Enqueue 佇列是一種鎖，保護一些共享資源，防止併發的DML操作。佇列採用FIFO策略，注意latch並不是採用的FIFO機制。比較常見的有3種型別的佇列：ST佇列，HW佇列，TX4佇列。ITPUB個人空間oboA6f3[ g;e
ST Enqueue的等待主要是在字典管理的表空間中進行空間管理和分配時產生的。解決方法：1）將字典管理的表空間改為本地管理模式 2）預先分配分割槽或者將有問題的字典管理的表空間的next extent設定大一些。ITPUB個人空間 R At[%Ah9y$\
HW Enqueue是用於segment的HWM的。當出現這種等待的時候，可以通過手工分配etents來解決。
yYF0lEv*B9390331TX4 Enqueue等待是最常見的等待情況。通常有3種情況會造成這種型別的等待：1）唯一索引中的重複索引。解決方法：commit或者rollback以釋放佇列。 2）對同一個點陣圖索引段（bitmap index fragment）有多個update，因為一個bitmap index fragment可能包含了多個rowid，所以當多個使用者更新時，可能一個使用者會鎖定該段，從而造成等待。解決方法同上。3）有多個使用者同時對一個資料塊作update，當然這些DML操作可能是針對這個資料塊的不同的行，如果此時沒有空閒的ITL槽，就會產生一個block-level鎖。解決方法：增大表的initrans值使建立更多的ITL槽；或者增大表的pctfree值，這樣oracle可以根據需要在pctfree的空間建立更多的ITL槽；使用smaller block size，這樣每個塊中包含行就比較少，可以減小衝突發生的機會。ITPUB個人空間0dB K ]P#Kn
6)    Free Buffer：這個等待事件表明系統正在等待記憶體中的可用空間，這說明當前Buffer 中已經沒有Free 的記憶體空間。如果應用設計良好，SQL 書寫規範，充分繫結變數，那這種等待可能說明Buffer Cache 設定的偏小，你可能需要增大DB_CACHE_SIZE。該等待也可能說明DBWR 的寫出速度不夠，或者磁碟存在嚴重的競爭，可以需要考慮增加檢查點、使用更多的DBWR 程式，或者增加物理磁碟的數量,分散負載，平衡IO。ITPUB個人空間:m/CRm2HeU
7)    Log file single write：該事件僅與寫日誌檔案頭塊相關，通常發生在增加新的組成員和增進序列號時。頭塊寫單個進行，因為頭塊的部分資訊是檔案號，每個檔案不同。更新日誌檔案頭這個操作在後臺完成，一般很少出現等待，無需太多關注。ITPUB個人空間a{F&rv7NU"yaA
8)    log file parallel write：從log buffer 寫redo 記錄到redo log 檔案，主要指常規寫操作(相對於log file sync)。如果你的Log group 存在多個組成員，當flush log buffer 時，寫操作是並行的，這時候此等待事件可能出現。儘管這個寫操作並行處理，直到所有I/O 操作完成該寫操作才會完成(如果你的磁碟支援非同步IO或者使用IO SLAVE，那麼即使只有一個redo log file member,也有可能出現此等待)。這個引數和log file sync 時間相比較可以用來衡量log file 的寫入成本。通常稱為同步成本率。改善這個等待的方法是將redo logs放到I/O快的盤中，儘量不使用raid5，確保表空間不是處在熱備模式下，確保redo log和data的資料檔案位於不同的磁碟中。
0cPQ Eq^#X*G93903319)    log file sync：當一個使用者提交或回滾資料時，LGWR將會話的redo記錄從日誌緩衝區填充到日誌檔案中，使用者的程式必須等待這個填充工作完成。在每次提交時都出現，如果這個等待事件影響到資料庫效能，那麼就需要修改應用程式的提交頻率, 為減少這個等待事件，須一次提交更多記錄，或者將重做日誌REDO LOG 檔案訪在不同的物理磁碟上，提高I/O的效能。
\O,f2Y-Z5^h[l939033110)  log buffer space：日誌緩衝區寫的速度快於LGWR寫REDOFILE的速度,可以增大日誌檔案大小，增加日誌緩衝區的大小，或者使用更快的磁碟來寫資料。ITPUB個人空間p'm0{a(MP ^(h!f"K
11)  logfile switch：通常是因為歸檔速度不夠快。表示所有的提交(commit)的請求都需要等待"日誌檔案切換"的完成。Log file Switch 主要包含兩個子事件:
P u5RGT.s%]-o9~!k9390331log file switch (archiving needed) 這個等待事件出現時通常是因為日誌組迴圈寫滿以後，第一個日誌歸檔尚未完成，出現該等待。出現該等待，可能表示io 存在問題。解決辦法:①可以考慮增大日誌檔案和增加日誌組；②移動歸檔檔案到快速磁碟；③調整log_archive_max_processes。
U W`(tV9390331log file switch (checkpoint incomplete) 當日志組都寫完以後，LGWR 試圖寫第一個log file，如果這時資料庫沒有完成寫出記錄在第一個log file 中的dirty 塊時(例如第一個檢查點未完成)，該等待事件出現。該等待事件通常表示你的DBWR 寫出速度太慢或者IO 存在問題。為解決該問題，你可能需要考慮增加額外的DBWR 或者增加你的日誌組或日誌檔案大小，或者也可以考慮增加checkpoint的頻率。
2Y6\7U |2pz!L2b5cR4}939033112)  DB File Parallel Write：檔案被DBWR並行寫時發生。解決辦法：改善IO效能。ITPUB個人空間Q"G.VBp!B'vI.E
13)  DB File Single Write：當檔案頭或別的單獨塊被寫入時發生，這一等待直到所有的I/O呼叫完成。解決辦法：改善IO效能。
!_ `G6u1F3jMvIhFV2R939033114)  DB FILE Scattered Read：當掃描整個段來根據初始化引數db_file_multiblock_read_count讀取多個塊時發生，因為資料可能分散在不同的部分，這與分條或分段）相關，因此通常需要多個分散的讀來讀取所有的資料。等待時間是完成所有I/O呼叫的時間。解決辦法：改善IO效能。ITPUB個人空間9T4W#Iz [O%]K
15)  DB FILE Sequential Read：當前臺程式對資料檔案進行常規讀時發生，包括索引查詢和別的非整段掃描以及資料檔案塊丟棄等待。等待時間是完成所有I/O呼叫的時間。解決辦法：改善IO效能。ITPUB個人空間/b9U3Jp8Rs
16)  Direct Path Read：一般直接路徑讀取是指將資料塊直接讀入PGA中。一般用於排序、並行查詢和read ahead操作。這個等待可能是由於I/O造成的。使用非同步I/O模式或者限制排序在磁碟上，可能會降低這裡的等待時間。
K iI0H2o%N O;f{939033117)  direct path write：直接路徑寫該等待發生在，系統等待確認所有未完成的非同步I/O 都已寫入磁碟。對於這一寫入等待，我們應該找到I/O 操作最為頻繁的資料檔案(如果有過多的排序操作，很有可能就是臨時檔案)，分散負載，加快其寫入操作。如果系統存在過多的磁碟排序，會導致臨時表空間操作頻繁，對於這種情況，可以考慮使用Local管理表空間，分成多個小檔案，寫入不同磁碟或者裸裝置。ITPUB個人空間 t1?0JfWHs6Y
18)  control file parallel write：當server 程式更新所有控制檔案時，這個事件可能出現。如果等待很短，可以不用考慮。如果等待時間較長，檢查存放控制檔案的物理磁碟I/O 是否存在瓶頸。ITPUB個人空間^8T)jt a
多個控制檔案是完全相同的拷貝，用於映象以提高安全性。對於業務系統，多個控制檔案應該存放在不同的磁碟上，一般來說三個是足夠的，如果只有兩個物理硬碟，那麼兩個控制檔案也是可以接受的。在同一個磁碟上儲存多個控制檔案是不具備實際意義的。減少這個等待，可以考慮如下方法:①減少控制檔案的個數(在確保安全的前提下)。②如果系統支援，使用非同步IO。③轉移控制檔案到IO 負擔輕的物理磁碟。
5E"u)y)w,a[:?9ok9[$l2_:R939033119)  control file sequential readITPUB個人空間` H)TPe"Qy
control file single write ：控制檔案連續讀/控制檔案單個寫對單個控制檔案I/O 存在問題時，這兩個事件會出現。如果等待比較明顯，檢查單個控制檔案，看存放位置是否存在I/O 瓶頸。

對於常見的一些IDLE wait事件舉例：ITPUB個人空間#`mZ;h i.\4e"h6E|
dispatcher timer                  ITPUB個人空間9DCy*r7{ D
lock element cleanup              ITPUB個人空間@;O7E.o1p6Y \
Null event                        ITPUB個人空間X Y"{,|.yn*_$p,[
parallel query dequeue wait       ITPUB個人空間2I,]r4P ~g0x`J
parallel query idle wait - Slaves 
#d4cD9k1d1R'd9390331pipe get                          ITPUB個人空間q J1Il{ l^qvV)C)D
PL/SQL lock timer                 
%YypV&z&F%iup9{ e-K9390331pmon timer- pmon                  
vA\h1Vj9390331rdbms ipc message                 
[b`~([ ]9390331slave wait                        ITPUB個人空間g Gn|MZEn
smon timer                        ITPUB個人空間Ab1Q.r!u3^iQ
SQL*Net break/reset to client     ITPUB個人空間!u b)sw_#^j
SQL*Net message from client       ITPUB個人空間] kYOM m ~ K
SQL*Net message to client         ITPUB個人空間 m*OZO4]@I
SQL*Net more data to client       ITPUB個人空間!q8k0K([T
virtual circuit status            ITPUB個人空間5R1y[[%a8K
client message                    ITPUB個人空間g@tR6KCG6J
SQL*Net message from client  ITPUB個人空間(AmKZ;\!hm
下面是關於這裡的常見的等待事件和解決方法的一個快速預覽ITPUB個人空間h ]i^P+O3MB?{
等待事件 一般解決方法ITPUB個人空間.vH|Rv p'p7Sx:|
Sequential Read 調整相關的索引和選擇合適的驅動行源ITPUB個人空間FUMX+rw
Scattered Read  表明出現很多全表掃描。優化code，cache小表到記憶體中。ITPUB個人空間q1K.J e:nu,L,TA
Free Buffer  增大DB_CACHE_SIZE，增大checkpoint的頻率，優化程式碼ITPUB個人空間5V4x%f._,J8]d5|
Buffer Busy Segment header 增加freelist或者freelistgroups
X8},v.C {hfc9390331Buffer Busy Data block 隔離熱塊；使用反轉索引；使用更小的塊；增大表的initrans
vP.xB"sH_.`^9390331Buffer Busy Undo header 增加回滾段的數量或者大小ITPUB個人空間g#C.tI@B_$cG3}
Buffer Busy Undo block Commit more；增加回滾段的數量或者大小ITPUB個人空間;s]-bxOd:R
Latch Free  檢查具體的等待latch型別，解決方法參考後面介紹
'O,[MCC5v0N.f^0j{ ]9390331Enqueue–ST  使用本地管理的表空間或者增加預分配的盤區大小
(_-a],Xn)n4B9390331Enqueue–HW  在HWM之上預先分配盤區
*Ej%N4?Z,C9390331Enqueue–TX4  在表或者索引上增大initrans的值或者使用更小的塊
x-|9e}6Wf+M8k O9390331Log Buffer Space  增大LOG_BUFFER，改善I/OITPUB個人空間 Mry#i"D~"q'Ja#l
Log File Switch  增加或者增大日誌檔案
)p#Xm \^/gr vU9390331Log file sync  減小提交的頻率；使用更快的I/O；或者使用裸裝置
Af*W#L8`p${}9390331Write complete waits 增加DBWR；提高CKPT的頻率；

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/9390331/viewspace-692192/，如需轉載，請註明出處，否則將追究法律責任。