第8章等待事件
當一個進程連接到event, '%CA%FD%BE%DD%BF%E2');" target="_self">數(shù)據(jù)庫之后，進程所經(jīng)歷的種種等待就開始被記錄，并且通過一系列的動態(tài)性能視圖進行展現(xiàn)。通過等待事件用戶可以很快地發(fā)現(xiàn)數(shù)據(jù)庫的性能瓶頸，從而進行針對性能的優(yōu)化和分析。
等待事件可以通過v$event_name視圖獲得，該視圖記錄著當前數(shù)據(jù)庫支持的等待事件及其基本信息。
Oracle的等待事件主要可以分為兩類，即空閑等待事件和非空閑等待事件?？臻e事件指Oracle正等待某種工作，在診斷和優(yōu)化數(shù)據(jù)庫的時候不用過多注意這部分事件。非空閑等待事件專門針對Oracle的活動，指數(shù)據(jù)庫任務或應用運行過程中發(fā)生的等待，這些等待事件是在調整數(shù)據(jù)庫的時候應該關注與研究的。
v$event_name視圖中的parameter1，parameter2，parameter3非常重要，對于不同的等待事件參數(shù)的意義各不相同。
SQL> select name,parameter1,parameter2,parameter3 from v$event_name
2 where name='db file scattered read';
NAME
----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
db file scattered read
file#
block#
blocks
V$SESSION視圖記錄的是數(shù)據(jù)庫當前連接的session信息。V$SESSION_WAIT視圖記錄的是當前數(shù)據(jù)庫連接的活動session正在等待的資源或事件信息。由于V$SESSION記錄的是動態(tài)信息，和session的生命周期相關，而并不記錄歷史信息，所以Oracle提供另外一個視圖V$SYSTEM_EVENT來記錄數(shù)據(jù)庫自啟動以來所有等待事件的匯總信息。通過V$SYSTEM_EVENT視圖，用戶可以迅速地獲得數(shù)據(jù)庫運行的總體概況。
以下是Oracle 9iR2中v$session_wait視圖的結構：
SQL> desc v$session_wait
名稱 是否為空?類型
----------------------------------------- -------- ----------------------------
SID NUMBER
SEQ# NUMBER
EVENT VARCHAR2(64)
P1TEXT VARCHAR2(64)
P1 NUMBER
P1RAW RAW(4)
P2TEXT VARCHAR2(64)
P2 NUMBER
P2RAW RAW(4)
P3TEXT VARCHAR2(64)
P3 NUMBER
P3RAW RAW(4)
WAIT_TIME NUMBER
SECONDS_IN_WAIT NUMBER
STATE VARCHAR2(19)
其中Event代表等待事件的名稱，p<n>text用以描述具體的參數(shù)，p<n>分別代表以十進制定義參數(shù)的參數(shù)值，p<n>raw是以十六進制表示的參數(shù)值。
在數(shù)據(jù)庫出現(xiàn)瓶頸時，通?？梢詮?/span>v$session_wait找到那些正在等待的session，通過session的sid，聯(lián)合v$session和v$sqltext視圖就可以捕獲這些session正在執(zhí)行的sql語句。
數(shù)據(jù)庫運行緩慢，可以判斷數(shù)據(jù)庫可能經(jīng)歷了等待，那么可以通過v$session_wait視圖入手。查詢v$session_wait獲取各進程等待信息：
Select sid,event,p1,p1text from v$session_wait;
Db file scattered read(DB文件分散讀取)：這種情況通常顯示與全表掃描相關的等待。當數(shù)據(jù)庫進行全表掃描時，基于性能的考慮，數(shù)據(jù)會分散讀入buffer cache。如果這個等待事件比較顯著，可能說明對于謀略寫全表掃描的表沒有創(chuàng)建索引或者沒有創(chuàng)建合適的索引，可能需要檢查這些數(shù)據(jù)表是否進行了正確的設置。然而這個等待事件不一定意味著性能低下，在某些條件下Oracle會主動使用全表掃描來替換索引掃描以提高性能，這和訪問的數(shù)據(jù)量有關，在CBO下Oracle會進行更為智能的選擇，在RBO下Oracle更傾向于使用索引。
確定了進程等待事件之后，考慮捕獲這些SQL以發(fā)現(xiàn)問題：
Select sql_text from v$sqltext a
Where a.hash_value=(select sql_hash_value from v$session b where b.sid=’&sid’)
Order by piece asc
獲得了產生等待的sql語句之后，可以使用應用用戶連接數(shù)據(jù)庫，檢查以上事件的執(zhí)行計劃。
檢查表的索引和索引鍵值：
Select index_name,index_type from user_indexes where table_name=’EMP’;.
Select index_name,column_name from user_ind_columns where table_name=’EMP’;
可以考慮在區(qū)分度高的字段上創(chuàng)建索引以消除全表掃描。
Oracle
雖然V$SESSION_WAIT記錄的信息如此重要，但是這些重要的信息是隨session而消失的，如果希望獲得數(shù)據(jù)庫的歷史狀態(tài)及session的歷史等待信息等數(shù)據(jù)，那是不可得的。
Top等待事件
V$SYSTEM_EVENT視圖記錄的是數(shù)據(jù)庫自啟動以來等待事件的匯總。通過查詢該視圖，可以快速獲得數(shù)據(jù)庫等待事件的總體概況，了解數(shù)據(jù)庫的基本狀態(tài)（Top 10 wait event）：
Select * from (
Select event,time_waited from v$system_event order by time_waited desc)
Where rownum<10;
Statspack Report中Top sql部分導致的Buffer Gets非常高，這使我們有理由懷疑索引存在問題，甚至缺少必要的索引。Statspack中記錄的是SQL片段，通過hash value值再結合V$SQL_TEXT，可以獲得完整的SQL語句。
接著分組查詢V$SESSION_LONGOPS視圖：
Select target,count(*) from v$session_longops group by target;
V$SESSION_LONGOPS
This view displays the status of various operations that run for longer than 6 seconds (in absolute time). These operations currently include many backup and recovery functions, statistics gathering, and query execution, and more operations are added for every Oracle release.
To monitor query execution progress, you must be using the cost-based optimizer and you must:
Set the TIMED_STATISTICS or SQL_TRACE parameter to true
Gather statistics for your objects with the ANALYZE statement or the DBMS_STATS package
重要等待事件
Db file sequential read（數(shù)據(jù)文件順序讀取）
Db file sequential read是個非常常見的I/O相關的等待事件，通常顯示與單個數(shù)據(jù)塊相關的讀取操作，在大多數(shù)情況下，讀取一個索引塊或者通過索引讀取一個數(shù)據(jù)塊時，都會記錄這個等待。
這個等待事件有3個參數(shù)P1、P2、P3，其中P1代表Oracle要讀取的文件的絕對文件號，P2代表Oracle從這個文件中開始讀取的起始數(shù)據(jù)塊塊號，P3代表讀取的Block數(shù)量，通常這個值為1，表明是單個Block被讀取。
SQL> select name,parameter1,parameter2,parameter3
2 from v$event_name where name='db file sequential read';
NAME
----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
db file sequential read
file#
block#
blocks
如果這個等待事件比較顯著，可能表示在多表連接中，表的連接順序存在問題，可能沒有正確的使用驅動表；或者可能索引的使用存在問題，并非索引總是最好的選擇。
在大多數(shù)情況下，通過索引可以更為快速地獲取記錄，所以對于一個編碼規(guī)范、調整良好的數(shù)據(jù)庫，這個等待事件很大通常是正常的。但是在很多情況下，使用索引并不是最佳的選擇，比如讀取較大表中大量的數(shù)據(jù)，全表掃描可能會明顯快于索引掃描，所以在開發(fā)中就應該注意，對于這樣的查詢應該避免使用索引掃描。
從Oracle 9iR2開始，Oracle引入了段級統(tǒng)計信息收集的新特性，收集的統(tǒng)計信息共有11類：
Select * from v$segstat_name;
在Oracle 10gR2中，這類統(tǒng)計信息增加為15個。
對于CBO模式下的數(shù)據(jù)庫，應當及時收集統(tǒng)計信息，使SQL可以選擇正確的執(zhí)行計劃，避免因為統(tǒng)計信息陳舊而導致的執(zhí)行錯誤等。
Db file scattered read（數(shù)據(jù)文件離散讀取）
SQL> select * from v$event_name where name='db file scattered read';
EVENT# NAME
---------- ----------------------------------------------------------------
PARAMETER1
----------------------------------------------------------------
PARAMETER2
----------------------------------------------------------------
PARAMETER3
----------------------------------------------------------------
188 db file scattered read
file#
block#
blocks
從V$EVENT_NAME視圖可以看到，該事件有3個參數(shù)，分別代表文件號、起始數(shù)據(jù)塊號、數(shù)據(jù)塊的數(shù)量。
起始數(shù)據(jù)塊號加上數(shù)據(jù)塊的數(shù)量，這意味著Oracle session正在等待多塊連續(xù)讀操作的完成。這個操作可能與全表掃描（Full table scan）或者快速全索引掃描（Index Fast Full Scan）的連續(xù)讀取相關。根據(jù)經(jīng)驗，通常大量的db file scattered read等待可能意味著應用問題或者索引缺失。
在實際環(huán)境的診斷過程中，可以通過v$session_wait視圖發(fā)現(xiàn)session的等待，再結合其他視圖找到存在問題的SQL等根本原因，從而從根本上解決問題。當這個等待事件比較顯著時，也可結合v$session_longops動態(tài)性能視圖來進行診斷，該視圖記錄了長時間（運行時間超過6秒的）運行的事務。
從Oracle 9i開始，Oracle新增加了一個視圖V$SQL_PLAN用于記錄當前系統(tǒng)Library Cache中SQL語句的執(zhí)行計劃，可以通過這個視圖找到存在問題的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=’SCOTT’;
獲得全索引掃描的對象
Select distinct object_name,object_owner from v$sql_plan p
Where p.operation=’INDEX’ and p.options=’FULL SCAN’ and object_owner=’SCOTT’;
通過V$SQL_PLAN和V$SQLTEXT聯(lián)合，獲得全表掃描的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;
Direct path read/write（直接路徑讀/寫）
直接路徑讀通常發(fā)生在Oracle直接讀取數(shù)據(jù)到PGA時，這個讀取不需要經(jīng)過SGA。直接路徑讀等待事件的3個參數(shù)分別是：file#（指絕對文件號）、first block#和block數(shù)量。
這類讀取通常在以下情況被使用：
磁盤排序IO操作
并行查詢從屬進程
預讀操作
最常見的是第一種情況。在DSS系統(tǒng)中，存在大量的Direct path read是很正常的，但是在OLTP系統(tǒng)中，通常顯著的直接路徑讀都意味著系統(tǒng)應用存在問題，從而導致大量的磁盤排序讀取操作。
直接路徑寫通常發(fā)生在Oracle直接從PGA寫數(shù)據(jù)到數(shù)據(jù)文件或臨時文件，這個寫操作可以繞過SGA。直接路徑寫等待事件的3個參數(shù)分別是：file#（指絕對文件號）、first block#和block數(shù)量。
這類讀取通常在以下情況被使用：
直接路徑加載
并行DML操作
磁盤排序
對未緩存的“LOB”段的寫入，隨后會記錄為direct path write(lob)等待
最常見的直接路徑寫，多數(shù)因為磁盤排序導致。對于這一寫入等待，應該找到I/O操作最為頻繁的數(shù)據(jù)文件（如果有過多的排序操作，很有可能就是臨時文件），分散負載，加快其寫入操作。
如果系統(tǒng)存在過多的磁盤排序，會導致臨時表空間操作頻繁，對于這種情況，可以考慮為不同用戶分配不同的臨時表空間，使用多個臨時文件，寫入不同磁盤或者裸設備，從而降低競爭提高性能。
日志文件相關等待
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行。
Log File Switch（日志文件切換）
Log File Switch當日志文件發(fā)生切換時出現(xiàn)，在數(shù)據(jù)庫進行日志切換時，LGWR需要關閉當前日志組，切換并打開下一個日志組，在這個切換過程中，數(shù)據(jù)庫的所有DML操作都處于停頓狀態(tài)，直至這個切換完成。
Log File Switch主要包含兩個子事件：
log file switch(achiving needed)，即日志切換（需要歸檔）
這個等待事件出現(xiàn)時通常是因為日志組循環(huán)寫滿以后，在需要覆蓋先前日志時，發(fā)現(xiàn)日志歸檔尚未完成，出現(xiàn)該等待。由于Redo不能寫出，該等待出現(xiàn)時，數(shù)據(jù)庫將陷于停頓狀態(tài)。
出現(xiàn)該等待，可能表示I/O存在問題、歸檔進程寫出緩慢，也有可能是日志組設置不合理等原因導致。針對不同原因，可以考慮采用的解決方法有：
可以考慮增大日志文件和增加日志組；
移動歸檔文件到快速磁盤；
調整log_archive_max_processes參數(shù)等；
log file switch(checkpoint incomplete)，即日志切換（檢查電未完成）
當所有的日志組都寫滿之后。LGWR試圖覆蓋某個日志文件，如果這時數(shù)據(jù)庫沒有完成寫出由這個日志文件所保護的臟數(shù)據(jù)時（檢查點未完成），該等待事件出現(xiàn)。該等待出現(xiàn)時，數(shù)據(jù)庫同樣將陷于停頓狀態(tài)。
該等待事件通常表示DBWR寫出速度太慢或者I/O存在問題。為解決該問題，可能需要考慮增加額外的DBWR或者增加日志組或日志文件大小。
Log File Sync（日志文件同步）
當一個用戶提交或回滾數(shù)據(jù)時，LGWR將會話期的重做由日志緩沖區(qū)寫入到重做日志中，LGWR完成任務以后會通知用戶進程。日志文件同步過程（Log File Sync）必須等待這一過程成功完成。對于回滾操作，該事件記錄從用戶發(fā)出Rollback命令道回滾完成的時間。
如果該等待過多，可能說明LGWR的寫出效率低下，或者系統(tǒng)提交過于頻繁。針對該問題，可以通過log file parallel write等待事件或User Commits、User Rollback等統(tǒng)計信息來觀察提交或回滾次數(shù)。
可能的解決方案主要有：
提高LGWR性能，盡量使用快速磁盤，不要把redo log file存放在RAID5的磁盤上；
使用批量提交；
適當使用NOLOGGING/UNRECOVERABLE等選項
Log File Single Write
該事件僅與寫日志文件頭塊相關，通常發(fā)生在增加新的組成員和增進序列號（Log switch）時。頭塊寫單個進行，因為頭塊的部分信息是文件號，每個文件不同。
Log File Parallel Write
從Log Buffer寫Redo記錄到日志文件，主要指常規(guī)寫操作（相對于Log File Sync）。如果Log Group存在多個組成員，當Flush Log Buffer時，寫操作是并行的，這時候此等待事件可能出現(xiàn)。
Log Buffer Space（日志緩沖空間）
當數(shù)據(jù)庫產生日志的速度比LGWR的寫出速度快，或者當日志切換太慢時，就會發(fā)生這種等待。這個等待出現(xiàn)時，通常表明Redo log buffer過小，為解決這個問題，可以考慮增大日志文件的大小或者增加日志緩沖器的大小。
另一個可能的原因是磁盤I/O存在瓶頸，可以考慮使用寫入速度更快的磁盤。在允許的條件下設置，可以考慮使用裸設備來存放日志文件，提高寫入效率。在一般的系統(tǒng)中，最低的標準是，不要把日志文件和數(shù)據(jù)文件存放在一起，因為通常日志文件只寫不讀，分離存放可以獲得性能提升，盡量使用RAID10而不是RAID5磁盤來存儲日志文件。
