oracle wait event 等待事件

等待事件的源起

等待事件的概念大概是從ORACLE 7.0.12中引入的，大致有100個等待事件。在ORACLE 8.0中這個數目增大到了大約150個，在ORACLE 8I中大約有220個事件，在ORACLE 9IR2中大約有400個等待事件，而在最近ORACLE 10GR2中，大約有874個等待事件。

雖然不同版本和元件安裝可能會有不同數目的等待事件，但是這些等待事件都可以通過查詢V$EVENT_NAME檢視獲得：

SQL> select * from v$version;

BANNER

----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE 10.2.0.1.0 Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 – Production

SQL> select count(*) from v$event_name;

COUNT(*)

----------

872

ORACLE的等待事件，主要可以分為兩類，即空閒(IDLE)等待事件和非空閒(NON-IDLE)等待事件。

1). 空閒等待事件指ORACLE正等待某種工作，在診斷和優化資料庫的時候，不用過多注意這部分事件。

2). 非空閒等待事件專門針對ORACLE的活動，指資料庫任務或應用執行過程中發生的等待，這些等待事件是在調整資料庫的時候需要關注與研究的。

下面來看一下ORACLE 10GR2中主要分類及各類等待事件的個數：

SQL> select wait_class#,wait_class_id,wait_class,count(*) as "count"

2 from v$event_name

3 group by wait_class#,wait_class_id,wait_class

4 order by wait_class#;

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS count

----------- ------------- ------------------------------ ----------

0 1893977003 Other 588

1 4217450380 Application 12

2 3290255840 Configuration 23

3 4166625743 Administrative 46

4 3875070507 Concurrency 24

5 3386400367 Commit 1

6 2723168908 Idle 62

7 2000153315 Network 26

8 1740759767 User I/O 17

9 4108307767 System I/O 24

10 2396326234 Scheduler 2

11 3871361733 Cluster 47

12 rows selected.

常見的空閒事件有:

•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

一些常見的非空閒等待事件有:

•db file scattered read

•db file sequential read

•buffer busy waits

•free buffer waits

•enqueue

•latch free

•log file parallel write

• log file sync

幾個檢視的總結：

V$SESSION 代表資料庫活動的開始，視為源起。

V$SESSION_WAIT 檢視用以實時記錄活動SESSION的等待情況，是當前資訊。

V$SESSION_WAIT_HISTORY 是對V$SESSION_WAIT的簡單增強，記錄活動SESSION的最近10次等待。

V$ACTIVE_SESSION_HISTORY 是ASH的核心，用以記錄活動SESSION的歷史等待資訊，每秒取樣一次，這部分內容記錄在記憶體中，期望值是記錄一個小時的內容。

WRH#_ACTIVE_SESSION_HISTORY 是V$ACTIVE_SESSION_HISTORY在AWR的儲存地。

V$ACTIVE_SESSION_HISTORY中的資訊會被定期(每小時一次)的重新整理到負載庫中，並預設保留一個星期用於分析。

DBA_HIST_ACTIVE_SESS_HISTORY檢視是WRH#_ACTIVE_SESSION_HISTORY檢視和其他幾個檢視的聯合展現，通常通過這個檢視進行歷史資料的訪問。

V$SYSTEM_EVENT 由於V$SESSION記錄的是動態資訊，和SESSION的生命週期相關，而並不記錄歷史資訊，所以ORACLE提供檢視V$SYSTEM_EVENT來記錄資料庫自啟動以來所有等待事件的彙總資訊。通過這個檢視，使用者可以迅速獲得資料庫執行的總體概況。

V$SQLTEXT 當資料庫出現瓶頸時，通常可以從V$SESSION_WAIT找到那些正在等待資源的SESSION，通過SESSION的SID，聯合V$SESSION和V$SQLTEXT檢視就可以捕獲這些SESSION正在執行的SQL語句。

重要等待事件

1. Db file sequential read（資料檔案順序讀取）

Db file sequential read是個非常常見的I/O相關的等待事件，通常顯示與單個資料塊相關的讀取操作，在大多數情況下，讀取一個索引塊或者通過索引讀取一個資料塊時，都會記錄這個等待。

這個等待事件有3個引數P1、P2、P3，其中P1代表Oracle要讀取的檔案的絕對檔案號，P2代表Oracle從這個檔案中開始讀取的起始資料塊塊號，P3代表讀取的Block數量，通常這個值為1，表明是單個Block被讀取。

SQL> select name,parameter1,parameter2,parameter3 from v$event_name where name='db file sequential read';

NAME PARAMETER1 PARAMETER2 PARAMETER3

------------------------------ ---------- ---------- ----------

db file sequential read file# block# blocks

如果這個等待事件比較顯著，可能表示在多表連線中，表的連線順序存在問題，可能沒有正確的使用驅動表；或者可能索引的使用存在問題，並非索引總是最好的選擇。

在大多數情況下，通過索引可以更為快速地獲取記錄，所以對於一個編碼規範、調整良好的資料庫，這個等待事件很大通常是正常的。但是在很多情況下，使用索引並不是最佳的選擇，比如讀取較大表中大量的資料，全表掃描可能會明顯快於索引掃描，所以在開發中就應該注意，對於這樣的查詢應該避免使用索引掃描。

從Oracle 9iR2開始，Oracle引入了段級統計資訊收集的新特性，收集的統計資訊共有11類：

Select * from v$segstat_name;

在Oracle 10gR2中，這類統計資訊增加為15個。

對於CBO模式下的資料庫，應當及時收集統計資訊，使SQL可以選擇正確的執行計劃，避免因為統計資訊陳舊而導致的執行錯誤等。

2. Db file scattered read（資料檔案離散讀取）

SQL> select * from v$event_name where name='db file scattered read';

EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3

---------- ---------- ------------------------- ---------- ---------- ----------

188 506183215 db file scattered read file# block# blocks

從V$EVENT_NAME檢視可以看到，該事件有3個引數，分別代表檔案號、起始資料塊號、資料塊的數量。

起始資料塊號加上資料塊的數量，這意味著Oracle session正在等待多塊連續讀操作的完成。這個操作可能與全表掃描（Full table scan）或者快速全索引掃描（Index Fast Full Scan）的連續讀取相關。根據經驗，通常大量的db file scattered read等待可能意味著應用問題或者索引缺失。

在實際環境的診斷過程中，可以通過v$session_wait檢視發現session的等待，再結合其他檢視找到存在問題的SQL等根本原因，從而從根本上解決問題。當這個等待事件比較顯著時，也可結合v$session_longops動態效能檢視來進行診斷，該檢視記錄了長時間（執行時間超過6秒的）執行的事務。

從Oracle 9i開始，Oracle新增加了一個檢視V$SQL_PLAN用於記錄當前系統Library Cache中SQL語句的執行計劃，可以通過這個檢視找到存在問題的SQL語句。

通過V$SQL_PLAN檢視，可以獲得大量有用的資訊：

獲得全表掃描的物件

Select distinct object_name,object_owner from v$sql_plan p Where p.operation='TABLE ACCESS'and p.options='FULL' and object_owner='SYS';

獲得全索引掃描的物件

Select distinct object_name,object_owner from v$sql_plan p Where p.operation='INDEX' and p.options='FULL SCAN' and object_owner='SYS';

通過V$SQL_PLAN和V$SQLTEXT聯合，獲得全表掃描的SQL語句

Select sql_text from v$sqltext t,v$sql_plan p Where t.hash_value=p.hash_value And p.operation='TABLE ACCESS' And p.options='FULL' Order by p.hash_value,t.piece;

3. Direct path read/write（直接路徑讀/寫）

直接路徑讀通常發生在Oracle直接讀取資料到PGA時，這個讀取不需要經過SGA。直接路徑讀等待事件的3個引數分別是：file#（指絕對檔案號）、first block#和block數量。

這類讀取通常在以下情況被使用：

磁碟排序IO操作

並行查詢從屬程式

預讀操作

最常見的是第一種情況。在DSS系統中，存在大量的Direct path read是很正常的，但是在OLTP系統中，通常顯著的直接路徑讀都意味著系統應用存在問題，從而導致大量的磁碟排序讀取操作。

直接路徑寫通常發生在Oracle直接從PGA寫資料到資料檔案或臨時檔案，這個寫操作可以繞過SGA。直接路徑寫等待事件的3個引數分別是：file#（指絕對檔案號）、first block#和block數量。

這類讀取通常在以下情況被使用：

直接路徑載入

並行DML操作

磁碟排序

對未快取的“LOB”段的寫入，隨後會記錄為direct path write(lob)等待

最常見的直接路徑寫，多數因為磁碟排序導致。對於這一寫入等待，應該找到I/O操作最為頻繁的資料檔案（如果有過多的排序操作，很有可能就是臨時檔案），分散負載，加快其寫入操作。

如果系統存在過多的磁碟排序，會導致臨時表空間操作頻繁，對於這種情況，可以考慮為不同使用者分配不同的臨時表空間，使用多個臨時檔案，寫入不同磁碟或者裸裝置，從而降低競爭提高效能。

日誌檔案相關等待

SQL> select name from v$event_name where name like '%log%';

NAME

----------------------------------------------------------------

log switch/archive

log file sequential read

log file single write

log file parallel write

log buffer space

log file switch (checkpoint incomplete)

log file switch (archiving needed)

log file switch (clearing log file)

switch logfile command

log file switch completion

log file sync

STREAMS capture process waiting for archive log

已選擇12行。

4. Log File Switch（日誌檔案切換）

Log File Switch當日志檔案發生切換時出現，在資料庫進行日誌切換時，LGWR需要關閉當前日誌組，切換並開啟下一個日誌組，在這個切換過程中，資料庫的所有DML操作都處於停頓狀態，直至這個切換完成。

Log File Switch主要包含兩個子事件：

1. log file switch(achiving needed)，即日誌切換（需要歸檔）

這個等待事件出現時通常是因為日誌組迴圈寫滿以後，在需要覆蓋先前日誌時，發現日誌歸檔尚未完成，出現該等待。由於Redo不能寫出，該等待出現時，資料庫將陷於停頓狀態。

出現該等待，可能表示I/O存在問題、歸檔程式寫出緩慢，也有可能是日誌組設定不合理等原因導致。針對不同原因，可以考慮採用的解決方法有：

可以考慮增大日誌檔案和增加日誌組；

移動歸檔檔案到快速磁碟；

調整log_archive_max_processes引數等；

2. log file switch(checkpoint incomplete)，即日誌切換（檢查電未完成）

當所有的日誌組都寫滿之後。LGWR試圖覆蓋某個日誌檔案，如果這時資料庫沒有完成寫出由這個日誌檔案所保護的髒資料時（檢查點未完成），該等待事件出現。該等待出現時，資料庫同樣將陷於停頓狀態。

該等待事件通常表示DBWR寫出速度太慢或者I/O存在問題。為解決該問題，可能需要考慮增加額外的DBWR或者增加日誌組或日誌檔案大小。

5. Log File Sync（日誌檔案同步）

當一個使用者提交或回滾資料時，LGWR將會話期的重做由日誌緩衝區寫入到重做日誌中，LGWR完成任務以後會通知使用者程式。日誌檔案同步過程（Log File Sync）必須等待這一過程成功完成。對於回滾操作，該事件記錄從使用者發出Rollback命令道回滾完成的時間。

如果該等待過多，可能說明LGWR的寫出效率低下，或者系統提交過於頻繁。針對該問題，可以通過log file parallel write等待事件或User Commits、User Rollback等統計資訊來觀察提交或回滾次數。

可能的解決方案主要有：

1). 提高LGWR效能，儘量使用快速磁碟，不要把redo log file存放在RAID5的磁碟上；RAID5 對於頻繁寫入得系統會帶來較大的效能損失，可以考慮使用檔案系統直接輸入/輸出，或者使用裸裝置(raw device)，這樣可以獲得寫入的效能提高。

2). 使用批量提交；

3). 適當使用NOLOGGING/UNRECOVERABLE等選項

6. Log File Single Write

該事件僅與寫日誌檔案頭塊相關，通常發生在增加新的組成員和增進序列號（Log switch）時。頭塊寫單個進行，因為頭塊的部分資訊是檔案號，每個檔案不同。更新日誌檔案頭這個操作在後臺完成，一般很少出現等待，無需太多關注。

7. Log File Parallel Write

從Log Buffer寫Redo記錄到日誌檔案，主要指常規寫操作（相對於Log File Sync）。如果Log Group存在多個組成員，當Flush Log Buffer時，寫操作是並行的，這時候此等待事件可能出現。

儘管這個寫操作並行處理，直到所有I/O 操作完成該寫操作才會完成(如果你的磁碟支援非同步IO或者使用IO SLAVE，那麼即使只有一個redo log file member,也有可能出現此等待)。

這個引數和log file sync 時間相比較可以用來衡量log file 的寫入成本。通常稱為同步成本率。

8. Log Buffer Space（日誌緩衝空間）

當資料庫產生日誌的速度比LGWR的寫出速度快，或者當日志切換太慢時，就會發生這種等待。這個等待出現時，通常表明Redo log buffer過小，為解決這個問題，可以考慮增大日誌檔案的大小或者增加日誌緩衝器的大小。

另一個可能的原因是磁碟I/O存在瓶頸，可以考慮使用寫入速度更快的磁碟。在允許的條件下設定，可以考慮使用裸裝置來存放日誌檔案，提高寫入效率。在一般的系統中，最低的標準是，不要把日誌檔案和資料檔案存放在一起，因為通常日誌檔案只寫不讀，分離存放可以獲得效能提升，儘量使用RAID10而不是RAID5磁碟來儲存日誌檔案。

9. Enqueue(佇列等待)

Enqueue是一種保護共享資源的鎖定機制。該鎖定機制保護共享資源，以避免因併發操作而損壞資料，比如通過鎖定保護一行記錄，避免多個使用者同時更新。Enqueue採用排隊機制，即FIFO(先進先出)來控制資源的使用。

Enqueue是一組鎖定事件的集合，如果資料庫中這個等待事件比較顯著，還需要進一步追蹤是哪一個類別的鎖定引發了資料庫等待。

SQL> select name,wait_class from v$event_name where name like '%enq%' and rownum<11;

--這裡記錄很多 只去取出了前10條而已

NAME WAIT_CLASS

------------------------- ------------------------------------------------------

enq: PW - flush prewarm b Application

enq: RO - contention Application

enq: RO - fast object reu Application

enq: KO - fast object che Application

enq: MV - datafile move Administrative

enq: TM - contention Application

enq: ST - contention Configuration

enq: TX - row lock conten Application

enq: TX - allocate ITL en Configuration

enq: TX - index contentio Concurrency

已選擇10行。

10. Latch Free(閂鎖釋放)

Latch Free通常被稱為閂鎖釋放，這個名稱常常引起誤解，實際上應該在前面加上一個”等待(WAIT)”，當資料庫出現這個等待時，說明有程式正在等待某個Latch被釋放，也就是Waiting Latch Free。

Latch是一種低階排隊(序列)機制，用於保護SGA中共享記憶體結構。Latch就像是一種快速的被獲取和釋放的記憶體鎖，用於防止共享記憶體結構被多個使用者同時訪問。

如果latch不可用，就會記錄latch釋放失敗(latch free miss )。有兩種與閂有關的型別:

1) 立刻。

2) 可以等待。

假如一個程式試圖在立刻模式下獲得閂，而該閂已經被另外一個程式所持有，如果該閂不能立可用的話，那麼該程式就不會為獲得該閂而等待。它將繼續執行另一個操作。

大多數latch問題都與以下操作相關:

沒有很好的是用繫結變數(library cache latch)、重作生成問題(redo allocation latch)、緩衝儲存競爭問題(cache buffers LRU chain)，以及buffer cache中的存在"熱點"塊(cache buffers chain)。

通常我們說，如果想設計一個失敗的系統，不考慮繫結變數，這一個條件就夠了，對於異構性強的系統，不使用繫結變數的後果是極其嚴重的。

另外也有一些latch等待與bug有關，應當關注Metalink相關bug的公佈及補丁的釋出。當latch miss ratios大於0.5%時，就應當研究這一問題。

Oracle的latch機制是競爭，其處理類似於網路裡的CSMA/CD，所有使用者程式爭奪latch， 對於願意等待型別(willing-to-wait)的latch,如果一個程式在第一次嘗試中沒有獲得latch,那麼它會等待並且再嘗試一次,如果經過_spin_count次爭奪不能獲得latch, 然後該程式轉入睡眠狀態，持續一段指定長度的時間，然後再次醒來，按順序重複以前的步驟.在8i/9i中預設值是_spin_count=2000。

如果SQL語句不能調整，在8.1.6版本以上，Oracle提供了一個新的初始化引數: CURSOR_SHARING可以通過設定CURSOR_SHARING = force 在伺服器端強制繫結變數。設定該引數可能會帶來一定的副作用，對於Java的程式，有相關的bug，具體應用應該關注Metalink的bug公告。

11. Free Buffer-釋放緩衝區

這個等待事件表明系統正在等待記憶體中的可用空間，這說明當前Buffer 中已經沒有Free 的記憶體空間。如果應用設計良好，SQL 書寫規範，充分繫結變數，那這種等待可能說明Buffer Cache 設定的偏小，你可能需要增大DB_BUFFER_CACHE。

Free Buffer 等待可能說明DBWR 的寫出速度不夠，或者磁碟存在嚴重的競爭，可以需要考慮增加檢查點、使用更多的DBWR 程式，或者增加物理磁碟的數量,分散負載，平衡IO。

12. Buffer Busy-緩衝區忙

該等待事件表示正在等待一個以unshareable方式使用的緩衝區，或者表示當前正在被讀入buffer cache。一般來說Buffer Busy Wait不應大於1%。檢查緩衝等待統計部分(或V$WAITSTAT)，看一下等待是否位於段頭(Segment Header)。如果是，可以考慮增加自由列表(freelist，對於Oracle8i DMT)或者增加freelist groups(在很多時候這個調整是立竿見影的，在8.1.6之前，這個freelists引數不能動態修改;在8.1.6及以後版本，動態修改feelists需要設定COMPATIBLE至少為8.1.6).

如果這一等待位於undo header，可以通過增加回滾段(rollback segment)來解決緩衝區的問題。如果等待位於undo block上，我們可能需要檢查相關應用，適當減少大規模的一致性讀取，或者降低一致性讀取(consistent read)的表中的資料密度或者增大DB_CACHE_SIZE。

如果等待處於data block，可以考慮將頻繁併發訪問的表或資料移到另一資料塊或者進行更大範圍的分佈(可以增加pctfree值 ，擴大資料分佈，減少競爭)，以避開這個"熱點"資料塊，或者可以考慮增加表中的自由列表或使用本地化管理的表空間(Locally Managed Tablespaces)。

如果等待處於索引塊，應該考慮重建索引、分割索引或使用反向鍵索引。為了防止與資料塊相關的緩衝忙等待，也可以使用較小的塊:在這種情況下，單個塊中的記錄就較少，所以這個塊就不是那麼"繁忙";或者可以設定更大的pctfree,使資料擴大物理分佈，減少記錄間的熱點競爭。

在執行DML (insert/update/ delete)時，Oracle向資料塊中寫入資訊，對於多事務併發訪問的資料表，關於ITL的競爭和等待可能出現，為了減少這個等待，可以增加initrans，使用多個ITL槽。在Oracle9i 中，引入了一個新概念:ASSM(Segment Space Management Auto)。通過這個新特性Oracle 使用點陣圖來管理空間使用。

ASSM 結合LMT 徹底改變了Oracle 的儲存機制，點陣圖freelist 能夠減輕緩衝區忙等待(buffer busy wait)，這個問題在Oracle9i 以前的版本里曾是一個嚴重的問題。

Oracle 宣稱ASSM 顯著地提高了DML 併發操作的效能，因為(同一個)點陣圖的不同部分可以被同時使用，這樣就消除了尋找剩餘空間的序列化。根據Oracle 的測試結果，使用點陣圖freelist 會消除所有分段頭部(對資源)的爭奪，還能獲得超快的併發插入操作。在Oracle9i 之中，Buffer Busy wait 不再常見!

13. control file parallel write-控制檔案並行寫

當server 程式更新所有控制檔案時，這個事件可能出現。如果等待很短，可以不用考慮。如果等待時間較長，檢查存放控制檔案的物理磁碟I/O 是否存在瓶頸。

多個控制檔案是完全相同的拷貝，用於映象以提高安全性。對於業務系統，多個控制檔案應該存放在不同的磁碟上，一般來說三個是足夠的，如果只有兩個物理硬碟，那麼兩個控制檔案也是可以接受的。在同一個磁碟上儲存多個控制檔案是不具備實際意義的。減少這個等待，可以考慮如下方法:

減少控制檔案的個數(在確保安全的前提下)

如果系統支援，使用非同步IO

轉移控制檔案到IO 負擔輕的物理磁碟

14. control file sequential read/ control file single write 控制檔案連續讀/控制檔案單個寫對單個控制檔案I/O 存在問題時，這兩個事件會出現。如果等待比較明顯，檢查單個控制檔案，看存放位置是否存在I/O 瓶頸。

15. direct path write-直接路徑寫該等待發生在，系統等待確認所有未完成的非同步I/O 都已寫入磁碟。

對於這一寫入等待，我們應該找到I/O 操作最為頻繁的資料檔案(如果有過多的排序操作，很有可能就是臨時檔案)，分散負載，加快其寫入操作。

如果系統存在過多的磁碟排序，會導致臨時表空間操作頻繁，對於這種情況，可以考慮使用Local管理表空間，分成多個小檔案，寫入不同磁碟或者裸裝置。

16. Idle Event-空閒事件

一般來說，空閒等待是指系統因為無事可做的等待，或者等待使用者的請求或響應等，通常我們可以忽略這些等待事件。空閒事件可以通過stats$idle_event 表查詢得到。

我們看一下系統的主要空閒等待事件，對這些事件大家應該有個大致的印象，如果你的Top 5 等待事件中，主要都是這些事件，那麼一般來說你的系統是比價清閒的。

注： 整理自網路

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