[Oracle] 常見的等待事件

db file scattered read

對於一些頻繁訪問的表，如果沒有建立索引或沒有建立合適的索引，Oracle只能對其進行全表掃描，就會導致大量該等待事件。
全表掃描時，讀取的資料在磁碟上一般是連續的，但是讀到記憶體時卻是不連續的，因此該事件命名為離散讀（scattered read)，注意不要被它的名字所迷惑。
一次多塊讀取的數量受參數DB_FILE_MULTIBLOCK_READ_COUNT的影響。
在實際診斷過程中，可以通過v$session_wait視圖發現session的等待，再結合其他視圖找到存在問題的SQL，當這個等待事件比較顯著時，使用者也可以結合v$session_longops動態效能檢視來進行診斷。

 

1)類比通過全表掃描產生db file scattered read：Oracle 11g，在大表的全表掃描演算法上有新的變化，根據表的大小、快取的大小等資訊，決定是否繞過SGA直接從磁碟讀取資料。而10g則是全部通過快取讀取資料。Oracle 11g認為大表全表時使用直接路徑讀，可能比10g中的資料檔案散列讀(db file scattered reads)速度更快，效率高。我們可以通過一個隱藏參數"_serial_direct_read"來控制這種行為

SQL> alter system set "_serial_direct_read" = false; 系統已更改。

 下面用10046事件類比：

SQL> alter session set tracefile_identifier='jujay'; 會話已更改。 SQL> alter session set events '10046 trace name context forever ,level 12' ; 會話已更改。 SQL> Select /*+full(t)*/ * from t where object_id=-1; --通過Hint強制讓最佳化器走全表掃描 SQL> alter session set events '10046 trace name context off'; 會話已更改。 SQL> select * from v$diag_info where name='Default Trace File'; INST_ID NAME---------- ----------------------------------------------------------------VALUE--------------------------------------------------------------------------------1 Default Trace Filec:\app\xianzhu\diag\rdbms\orcl\orcl\trace\orcl_ora_3744_jujay.trc

下面是10046產生的trace檔案的內容：

…….WAIT #47878792599648: nam='db file scattered read' ela= 34 file#=8 block#=131 blocks=5 obj#=23315 tim=1356470039698744WAIT #47878792599648: nam='db file scattered read' ela= 27 file#=8 block#=200 blocks=8 obj#=23315 tim=1356470039699325WAIT #47878792599648: nam='db file scattered read' ela= 20 file#=8 block#=217 blocks=7 obj#=23315 tim=1356470039699788WAIT #47878792599648: nam='db file scattered read' ela= 22 file#=8 block#=224 blocks=8 obj#=23315 tim=1356470039700179WAIT #47878792599648: nam='db file scattered read' ela= 19 file#=8 block#=241 blocks=7 obj#=23315 tim=1356470039700589…….

 2）通過索引快速掃描產生db file scattered read首先，建立索引：

SQL> create index i_t on t(object_id); 索引已建立。

SQL> alter session set tracefile_identifier='jujay'; 會話已更改。 SQL> alter session set events '10046 trace name context forever ,level 12' ; 會話已更改。 SQL> Select count(*) from t; --索引快速全掃描 SQL> alter session set events '10046 trace name context off'; 會話已更改。 SQL> select * from v$diag_info where name='Default Trace File'; INST_ID NAME---------- ----------------------------------------------------------------VALUE--------------------------------------------------------------------------------1 Default Trace Filec:\app\xianzhu\diag\rdbms\orcl\orcl\trace\orcl_ora_3744_jujay.trc

下面是10046產生的trace檔案的內容：

…….WAIT #47878792599648: nam='db file scattered read' ela= 34 file#=8 block#=131 blocks=5 obj#=23315 tim=1356470039698744WAIT #47878792599648: nam='db file scattered read' ela= 27 file#=8 block#=200 blocks=8 obj#=23315 tim=1356470039699325WAIT #47878792599648: nam='db file scattered read' ela= 20 file#=8 block#=217 blocks=7 obj#=23315 tim=1356470039699788WAIT #47878792599648: nam='db file scattered read' ela= 22 file#=8 block#=224 blocks=8 obj#=23315 tim=1356470039700179WAIT #47878792599648: nam='db file scattered read' ela= 19 file#=8 block#=241 blocks=7 obj#=23315 tim=1356470039700589…….

db file sequential read當訪問一個不在SGA的資料區塊時，就會產生該事件，一般在用索引訪問時頻繁發生。
在一個正常的OLTP系統中，該事件占的比例比較大是正常的，但是如果該事件的等待事件非常長，則表示當前有大量的索引讀取操作，可以考慮是否全表掃描更快速呢？或者磁碟I/O太慢？
首先，建立索引：

SQL> create index i_t on t(object_id); 索引已建立。

接著以索引訪問表中資料：

SQL> alter session set tracefile_identifier='jujay'; 會話已更改。 SQL> alter session set events '10046 trace name context forever ,level 12' ; 會話已更改。 SQL> select object_name from t where object_id=1000; --以索引訪問表中資料 SQL> alter session set events '10046 trace name context off' ; 會話已更改。 SQL> select * from v$diag_info where name='Default Trace File'; INST_ID NAME---------- ----------------------------------------------------------------VALUE--------------------------------------------------------------------------------1 Default Trace Filec:\app\xianzhu\diag\rdbms\orcl\orcl\trace\orcl_ora_12136_jujay.trc c:\app\xianzhu\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_12136_jujay.trc orcl_ora_12136_jujay.txt TKPROF: Release 11.2.0.1.0 - Development on 星期三 12月 26 13:23:29 2012 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved.

查看trace檔案中的內容：

SQL ID: 0bjnptjy6ctk2Plan Hash: 2928007915select object_namefromt where object_id=1000  call count cpu elapsed disk query current rows------- ------ -------- ---------- ---------- ---------- ---------- ----------Parse 2 0.01 0.00 0 0 0 0Execute 2 0.00 0.00 0 0 0 0Fetch 8 0.03 0.68 34 76 0 64------- ------ -------- ---------- ---------- ---------- ---------- ----------total 12 0.04 0.68 34 76 0 64 Misses in library cache during parse: 1Optimizer mode: ALL_ROWSParsing user id: 92 Rows Row Source Operation------- ---------------------------------------------------32 TABLE ACCESS BY INDEX ROWID T (cr=38 pr=34 pw=0 time=0 us cost=35 size=960 card=32)32 INDEX RANGE SCAN I_T (cr=6 pr=2 pw=0 time=837 us cost=3 size=0 card=32)(object id 75191)  Elapsed times include waiting on following events:Event waited on Times Max. Wait Total Waited---------------------------------------- Waited ---------- ------------SQL*Net message to client 9 0.00 0.00db file sequential read 34 0.10 0.67SQL*Net message from client 9 19.22 36.71********************************************************************************

如上所示，df file sequential read事件出現buffer busy waits & cache buffer chain

和buffer相關的等待事件是因為熱塊，根據資料區塊類型，解決方案不同：
*表資料區塊：考慮將資料分布在更多的資料區塊上，減少資料區塊被多使用者同時訪問的頻率。
*索引資料區塊：當一個表的主鍵用sequence產生索引值時，索引最右邊的葉子節點容易成為熱塊，可以考慮使用反向索引。

free buffer —— 等待空閑緩衝區

當把資料區塊從磁碟讀入記憶體時，如果記憶體已無空閑空間，則會產生該等待事件，可能的原因有：
1）Data buffer太小，需增大Data buffer
2）記憶體中髒資料太多，需增加DBWR進程數

buffer busy —— 緩衝區忙等待

當使用者頻繁地讀取或修改同樣的資料區塊時，就會出現該等待事件，如果該等待事件很長，表示資料庫中存在熱塊。

log file sync —— 記錄檔同步等待

當發出一個commit命令時，LGWR進程會將相應的redo log從log buffer寫到磁碟上，此時就會產生該事件。
如果存在大量該事件，則應檢查存在使用者在頻繁的提交，多出現在OLTP系統中。

log buffer space —— 日誌緩衝區空間等待

如果log buffer無空閑空間可用，則會出現該等待事件。
一般情況下，該等待事件出現的可能性很少，除非log buffer過小或I/O太差。

log file sequential read —— 記錄檔順序讀等待

當對記錄檔進行讀時（如日誌歸檔），會出現該等待事件。

log file switch —— 日誌切換等待

該事件又分為兩種類型：
1）checkpoint incomplete
日誌切換時，如果下一個日誌對應的某些髒資料庫還未被寫到磁碟上，則必須等待checkpoint完成，減少該等待事件的方法有：增大記錄檔大小、增加記錄檔組或提高DBWR的效能。
2）archiving needed
如果資料庫處于歸檔模式，當日誌切換時，如果下一個日誌還為被歸檔，則必須等到該日誌被歸檔後，才能完成切換。產生這種等待事件的原因一般都是arch進程死掉或太慢。

enqueue —— 鎖等待

enqueue可以等同於鎖，如果出現長時間的該等待，說明資料庫中出現阻塞和鎖。

 

和latch爭用相關的等待事件

*library cache
*library cache pin
*library cache pin allocation
*library cache load lock
和library cache相關的等待事件可以斷定是共用池出現的問題，基本上是由不良SQL語句導致，如沒有使用綁定變數等。