Oracle面試寶典-等待事件篇

Oracle 面試寶典 - 等待事件篇

請問Oracle 資料庫中等待事件的作用是什麼 ?

一、等待事件由來

因為指標體系的發展，才導致等待事件的引入。總結一下，Oracle 的指標體系，大致經歷了下面三個階段：

(1) 以命中率為主要參考指標

以各種命中率為主要的最佳化入口依據，常見的有” library cache hit radio “等。但這種方式弊端很大，一個命中率為 99% 的系統，不一定就比 95% 的系統最佳化的更好。在老的 Oracle 版本中，往往採用這種方式，如 8i 、 9i 等。

命中率是近20 年前系統效能最佳化的一種觀點，後來該觀點被證明有一定的偏差而被同行逐漸棄用，不能說完全錯，命中率只能說是一個參考指標。

20 年後的今天，效能最佳化的理論、實踐和手段，相比之前都有了很大的積累和發展。現在，對效能問題進行分析和診斷，更注重各方面資訊的綜合分析，而不是單純看某個指標。比較典型的，例如：系統的綜合效能情況，可以透過檢視 ash 、 awr 、 addm 或 osw 等各種報告進行分析和確定，單個 SQL 語句的效能也主要是結合具體 SQL 、執行計劃及資料環境進行分析，獲取執行計劃的方法比較多，但大同小異，本質差不多。

至於效能分析診斷的思路和方法，一般是從整體到區域性，逐漸細化的方法和步驟。

(2) 以等待事件為主要參考指標

以各種等待事件為最佳化入口依據，常見的有"db file sequential read" 等。可以較直觀的瞭解，在一段時間內，資料庫主要經歷了那些等待。這些 " 瓶頸 " ，往往就是我們最佳化的著手點。在 10g 、 11g 版本中，廣泛使用。

(3) 以時間模型為主要參考指標

以各種資源整體消耗為最佳化入口依據。可以從整體角度瞭解資料庫在一段時間內的消耗情況。較等待事件的方式，更有概括性。常見的如"DB Time" 。 Oracle 在不斷加強這個方面的工作。

從上面三個階段可見，等待事件的引入，正是為了解決以命中率為指標的諸多弊端。與後面的時間模型相比，等待事件以更加直觀、細粒度的方式觀察Oracle 的行為，往往作為最佳化的重要入口。而時間模型，更側重於整體、系統性的瞭解資料庫執行狀態。兩者的側重點不同。

請問Oracle 資料庫有哪些型別的等待事件？

等待事件可分為空閒的、非空閒的兩大部分。在非空閒的等待事件，又可進一步劃分細的類別。

空閒等待：

空閒等待事件，是指Oracle 正等待某種工作，比如用 sqlplus 登入之後，但沒有進一步發出任何命令，此時該 session 就處於 SQL*Net message from/to client 等待事件狀態，等待使用者發出命令，任何的在診斷和最佳化資料庫的時候，一般不用過多注意這部分事件。

非空閒等待：

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

等待事件分類說明：

select  wait_class ,  wait_class_id ,   count (*)

   from  v$event_name

  group   by  wait_class ,  wait_class_id

  order   by   1 ;

--- 資料庫版本 Oracle 19C, 等待事件數量 192 0 個 ( 在 Oracle 10g 等待事件有 872 個， 11g 等待事件 1116 個 ) 。

管理類-Administrative

此類等待事件是由於DBA 的管理命令引起的，這些命令要求使用者處於等待狀態（比如，重建索引） 。

例如：

應用程式類-Application

此類等待事件是由於使用者應用程式的程式碼引起的（比如，鎖等待）。

例如:

群集類-Cluster

此類等待事件和 Oracle RAC 的資源有關（比如， gc cr block busy 等待事件）。

例如：

提交確認類-Commit

此類等待事件只包含一種等待事件—— 在執行了一個 commit 命令後，等待一個重做日誌寫確認 。

例如：

併發類-Concurrency

此類等待事件是由內部資料庫資源引起的（比如閂鎖） 。

例如：

配置類-Configuration

此類等待事件是由資料庫或例項的不當配置造成的（比如，重做日誌檔案尺寸太小，共享池的大小等）。

例如：

空閒類-Idle

此類等待事件意味著會話不活躍，等待工作（比如，sql * net messages from client ）。

例如：

網路類-Network

和網路環境相關的一些等待事件（比如sql* net more data to dblink ）。

例如：

其它類-Other

此類等待事件通常比較少見。

例如：

排程類-Scheduler

此類等待事件和資源管理相關。

例如：

系統I/O 類 -System I/O

此類等待事件透過是由後臺程式的I/O 操作引起的（比如 DBWR 等待 -db file paralle write ）。

例如：

使用者I/O 類 -User I/O

此類等待事件通常是由使用者I/O 操作引起的（比如 db file sequential read ） 。

例如：

請描述下你經常遇到的10 個等待事件？

一：buffer busy wait

型別：併發類

發生原因：

當一個會話將資料塊從磁碟讀到記憶體中時，它需要到記憶體中找到空閒的記憶體空間來存放這些資料塊，當記憶體中沒有空閒的空間時，就會產生這個等待。除此之外，還有一種情況就是會話在做一致性讀時，需要構造資料塊在某個時刻的前映像。此時需要申請記憶體塊來存放這些新構造的資料塊，如果記憶體中無法找到這樣的記憶體塊，也會發生這個等待事件。

最佳化方向：

根據產生此等待事件的類別不同，最佳化方向也不太一樣。

如何找出產生此等待事件的物件和物件型別？

在出現 buffer busy waits 時查詢V$SESSION 中的 ROW_WAIT_OBJ# 值。例如 :

SELECT  row_wait_obj# FROM  V$SESSION WHERE  EVENT =   'buffer busy waits' ;

要識別爭用的物件和物件型別，可以使用從V$SESSION 返回的 ROW_WAIT_OBJ# 的值來查詢 DBA_OBJECTS 。例如 :

SELECT  owner ,  object_name ,  subobject_name ,  object_type

   FROM  DBA_OBJECTS

  WHERE  data_object_id =  &row_wait_obj ;

或者透過SID 查詢對應塊號，檔案號，型別

select event, sid, p1, p2, p3

  from v$session_wait

 where sid in (69, 75)

   and event like '%buffer busy waits%';

---

P1: File ID

P2: Block ID

P3: Class ID

p1 、 p2 引數和 dba_extents 進行聯合查詢得到 block 所在的 segment 名稱和 segment 型別

物件型別： 資料塊

某一或某些資料塊被多個程式同時讀寫，成為熱點塊，可以透過如下這些辦法來解決這個問題：

(1) 降低程式的併發度，如果程式中使用了 parallel 查詢，降低 parallel degree ，以免多個 parallel slave 同時訪問同樣的資料物件而形成等待降低效能；

(2) 調整應用程式使之能讀取較少的資料塊就能獲取所需的資料，減少 buffer gets 和 physical reads ；

(3) 減少同一個 block 中的記錄數，使記錄分佈於更多的資料塊中，這可以透過若干途徑實現：可以調整 segment 物件的 pctfree 值，可以將 segment 重建到 block size 較小的表空間中，還可以用 alter table minimize records_per_block 語句減少每塊中的記錄數；

(4) 若熱點塊物件是類似自增 id 欄位的索引，則可以將索引轉換為反轉索引，打散資料分佈，分散熱點塊 。

最佳化方向： 一般最佳化方向是最佳化SQL ，減少邏輯讀、物理讀；或者是減少單塊的儲存資料規模。

物件型別： 資料段頭

程式經常性的訪問 data segment header 通常有兩個原因

(1) 獲取或修改 process freelists 資訊

程式頻繁訪問process freelists 資訊導致 freelist 爭用，我們可以增大相應的 segment 物件的儲存引數 freelist 或者 freelist groups ；若由於資料塊頻繁進出 freelist 而導致程式經常要修改 freelist ，則可以將 pctfree 值和 pctused 值設定較大的差距，從而避免資料塊頻繁進出 freelist ；

(2) 擴充套件高水位標記

由於該segment 空間消耗很快，而設定的 next extent 過小，導致頻繁擴充套件高水位標記，解決的辦法是增大 segment 物件的儲存引數 next extent 或者直接在建立表空間的時候設定 extent size uniform ；

最佳化方向 ： 增加FREELISTS 和 FREELIST GROUPS 。確保 FCTFREE 和 PCTUSED 之間的間隙不是太小，從而可以最小化 FREELIST 的塊迴圈。

物件型別： 撤銷塊

undo block 爭用是由於應用程式中存在對資料的讀和寫同時進行，讀程式需要到 undo segment 中去獲得一致性資料，解決辦法是錯開應用程式修改資料和大量查詢資料的時間 。

最佳化方向： 應用程式，錯峰使用資料物件。

物件型別： 撤銷段頭

undo segment header 爭用是因為系統中 undo segment 不夠，需要增加足夠的 undo segment ，根據 undo segment 的   方法，若是手工管理模式，需要修改rollback_segments 初始化引數來增加 rollback segment ，若是自動管理模式，可以減小 transactions_per_rollback_segment 初始化引數的值來使 oracle 自動增多 rollback segment 的數量 。

最佳化方向： 如果是資料庫系統管理UNDO 段，一般不需要干預。如果是自行管理的，可以減少每個回滾段的事務個數 。

二：db file sequential read

型別： 使用者I/O 類

發生原因： db file sequential read 事件和 Single Block I/O 有關。

該等待事件是將資料讀到連續的記憶體( 這裡指的是讀到相連的記憶體，不是說讀取的是連續的資料塊 ) 。大多數情況下讀取一個索引塊或者透過索引讀取一個資料塊，會記錄這個等待。可能顯示錶的連線順序不佳，或者不加選擇地進行索引。對於大量事務處理、調整良好的系統，這一數值大多是很正常的，但在某些情況下，它可能暗示著系統中存在問題。應當將這一等待統計量與效能報告中的已知問題（如效率較低的 SQL ）聯絡起來。檢查索引掃描，以保證每個掃描都是必要的，並檢查多表連線的連線順序。

引數含義：

file# ： 代表oracle 要讀取的檔案的絕對檔案號

block# ： 從這個檔案中開始讀取的起始資料塊塊號

Blocks ： 讀取的block 數量。通常是 1 ，表示單個 block 讀取。

最佳化方向：

這個等待事件，不一定代表一定有問題。如果能確定是有問題，可以按照下面最佳化思路。

1 修改應用，避免出現大量IO 的 sql ，或者減少其頻率 或最佳化SQL 。

2 增加data buffer ，提高命中率。

3 採用更好的磁碟子系統，減少單個IO 的響應時間，防止物理瓶頸的出現。

三： db file scattered read

型別： 使用者I/O 類

發生原因： Oracle 在執行全表掃描 ( Full Table Scan,FTS) 、索引快速全掃描 ( Index Fast Full Scan) 時，為保障效能，儘量一次性讀取多個塊，這稱為 Multi Block I/O 。 每次執行 Multi Block I/O ，都會等待物理 I/O 結束，此時等待 db file scattered read 事件。這裡 scattered 指的是讀取的資料塊在記憶體中的存放方式。它們被讀取到記憶體中後，是以分散的方式存放在記憶體中，而不是連續的。

引數含義：

file# ： 代表oracle 要讀取的檔案的絕對檔案號。

block# ： 從這個檔案中開始讀取的起始資料塊塊號。

Blocks ： 讀取的block 數量。

最佳化方向：

這種情況通常顯示與全表掃描相關的等待。當全表掃描被限制在記憶體時，它們很少會進入連續的緩衝區內，而是分散於整個緩衝儲存器中。如果這個數目很大，就表明該表找不到索引，或者只能找到有限的索引。儘管在特定條件下執行全表掃描可能比索引掃描更有效，但如果出現這種等待時，最好檢查一下這些全表掃描是否必要。如果是某些SQL 引起的，例如統計資訊不準確，沒有索引或使用低效的索引等，可以透過最佳化 SQL ，降低 db file scattered read 。

四 ：direct path read

型別： 使用者I/O 類

發生原因：

這個等待事件發生在會話將資料塊直接讀取到PGA 當中而不是 SGA 中的情況，這些被讀取的資料通常是這個會話私有的資料，所以不需要放到 SGA 作為共享資料，因為這樣做沒有意義。這些資料通常是來自於臨時段上的資料，比如一個會話中 SQL 的排序資料，並行執行過程中間產生的資料，以及 Hash join 、 Merge join 產生的排序資料，因為這些資料只對當前會話的 SQL 操作有意義，所以不需要放到 SGA 當中。 當發生direct path read 等待事件時，意味著磁碟上有大量的臨時資料產生，比如排序、並行執行等操作，或者意味著 PGA 中空閒空間不足 。

在11g 中，全表掃描可能使用 direct path read 方式，繞過 buffer cache ，這樣的全表掃描就是物理讀了。在 10g 中，都是透過 buffer  cache 來讀的，所以不存在direct path read 的問題。

引數含義：

file# ： 檔案號

first block# ： 讀取的起始塊號

block count ： 以first block 為起點，連續讀取的物理塊數

最佳化方向：

有了這個等待事件，需要區分幾種情況。一個方向是增大排序區等手段，一個方向是減少讀取IO 量或判斷是否透過緩衝區讀的方式更加高效。

direct path read 可能出現的問題：

在Oracle 11g 中有一個新特性，為了保護已經快取在 buffer cache 的資料，當出現全表掃的查詢時會判斷該表的大小。如果該表過大，則使用直接路徑讀（ Direct Path Read ）來獲取資料。避免大量冷資料對 Buffer Cache 的衝擊。透過直接路徑讀的方式繞過 SGA 從儲存上獲取資料。由於沒有 SGA 的快取，每一次查詢都需要從儲存讀取產生了大量的物理讀，可能會導致 I/O 負載過高。

新特性中如何判斷全表掃的大小呢？

下面看一個隱含引數：_small_table_threshold

該引數預設為Buffer Cache 的 2% ，如果表大於 5 倍 _small_table_threshold 就觸發該特性。自動會使用 DPR 替代 FTS 。

可以透過設定10949 事件遮蔽這個特性，返回到 Oracle 11g 之前的模式上：

alter session set events '10949 trace name context forever, level 1';

小表受到隱含引數：_small_table_threshold 影響。如果表大於 5 倍的小表限制，則自動會使用 DPR 替代 FTS 。
可以設定初始化引數： _serial_direct_read 來禁用序列直接路徑讀。

五： db file single write

型別： 使用者I/O 類

發生原因： 其中一種情況，Oracle 更新資料檔案頭資訊時（比如發生 CheckPoint ）會出現這種等待事件。要考慮資料庫中的資料檔案數量太大，導致 Oracle 需要花較長的時間來做所有檔案頭的更新操作（ CheckPoint ）。

這個等待事件包含三個引數：

file# ：要讀取的資料塊所在資料檔案的檔案號。

block# ：讀取的起始資料塊號。

blocks ：需要讀取的資料塊數目。（通常來說在這裡應該等於 1 ）

六：direct path write

型別： 使用者I/O 類

發生原因： 這個等待事件和direct path read 正好相反， 發生在oracle 直接從 PGA 寫資料到資料檔案或臨時檔案，這個操作可以繞過 SGA 。 可以執行 direct path writes 的操作包括 磁碟排序、並行DML 操作、直接路徑插入、並行 create table as select 操作以及一些LOB 操作 。 對於這種情況應該找到操作最為頻繁的資料檔案( 如果是排序，很有可能是臨時檔案 ) ，分散負載。

引數含義：

file# ：檔案號

first block# ：讀取的起始塊號

block count ：以 first block 為起點，連續寫入的物理塊數

最佳化方向：減少IO 寫入規模。

七：log file sync

型別： 提交類

發生原因：

這是一個使用者會話行為導致的等待事件。當一個會話發出一個commit 命令時， LGWR 程式會將這個事務產生的 redo log 從 redo log buffer 裡寫到 redo log file 磁碟上，以保證使用者提交的資訊被安全地記錄到資料庫中。會話發出 commit 指令後，需要等待 LGWR 將這個事務產生的 redo 成功寫入到磁碟之後，才可以繼續進行後續的操作，這個等待事件就叫做 log file sync 。當系統中出現大量的 log file sync 等待事件時，應該檢查資料庫中是否有使用者在做頻繁的提交操作。這種等待事件通常發生在 OLTP 系統上。 OLTP 系統中存在很多小的事務，如果這些事務頻繁被提交，可能引起大量 log file sync 的等待事件。

最佳化方向：

下面最佳化建議，有助於減少log file sync 等待：

(1)  最佳化 LGWR 速度，以獲得良好的磁碟吞吐量。例如： redo log file 不要放在 RAID 5 上 ( 可以考慮 RAID 0 或 RAID 1+0) ；

(2)  如果有大量小事物，最好可以批次提交，減少提交次數；

( 3 )  特定場景可以考慮使用 NOLOGGING / UNRECOVERABLE 選項 ( 謹慎使用 ) ；

( 4 )  保證 redolog 足夠大，確保日誌切換間隔在 15-20 分鐘；

( 5 )  使用穩定版本資料庫避免 bug ，具體 bug 修復的版本參考文件；

( 6 )  在 11.2.0.3 版本中， Oracle 預設啟用 _use_adaptive_log_file_sync 引數，使得 LGWR 程式寫日誌的方式能自動在 post/wait 和 polling 兩種方式之間進行取捨，可能會導致比較嚴重的寫日誌等待（ log file sync 的平均單次等待時間較高） , 建議關閉此功能。

參考命令：alter system set "_use_adaptive_log_file_sync"=FALSE;

八： Log File Parallel Write

型別： 系統I/O

發生原因：

1 、 Log File Sync 是從提交開始到提交結束的時間。 Log File Parallel Write 是 LGWR 開始寫 Redo File 到 Redo File 寫 結束的時間。明確了這一點，可以知道，Log file sync 包含了 log file parallel write 。所以， log file sync 等待時間一出，必先看 log file parallel write 。如果 log file sync 平均等待時間（也可稱為提交響應時間）為 20ms ， log file parallel write 為 19ms ，那麼問題就很明顯了， Redo file I/O 緩慢，拖慢了提交的過程。  
2 、 Log File Sync 的時間不止 log file parallel write 。伺服器程式開始提交，到通知 LGWR 寫 Redo ， LGWR 寫完 Redo 通知程式提交完畢，來回通知也是要消耗 CPU 的。除去來回通知外， Commit 還有增加 SCN 等等操作，如果 log file sync 和 log file parallel write 差距很大，證明 I/O 沒有問題，但有可能是 CPU 資源緊張，導致程式和 LGWR 來回通知或其他的需要 CPU 的操作，得不到足夠的 CPU ，因而產生延遲 。

最佳化方向 ： 考 慮的是如何在單個LGWR 程式的前提下讓寫的日誌量不超過當前的 LGWR 寫能力。這個可以從兩個方面來考慮 ：

1 ： 考慮是否在應用中產生了太多無意義的重做日誌，導致日誌產生量太大，從而使日誌的產生量超出了LGWR 的寫能力，如果是這樣，那麼考慮透過一些方法限制重做日誌的產生。

2 ： 考慮如果日誌產生量確定的情況下，如何讓LGWR 程式寫日誌能夠寫得更多更快，這主要取決於兩個方面，一個是 LGWR 在寫日誌的時候是否發生了 I/O 競爭，另一方面是重做日誌檔案所在的磁碟速度是否過低，如果是競爭引起的，移動重做日誌檔案到其他的磁碟上，如果是磁碟速度引起的，那麼選擇高速磁碟存放重做日誌。

九 ：library cache lock

型別： 併發類

發生原因：

這個等待事件發生在不同使用者在共享池中由於併發操作同一個資料庫物件導致的資源爭用的時候。比如當一個使用者正在對一個表做DDL 操作時，其他的使用者如果要訪問這張表，就會發生 library cache lock 等待事件，它要一直等到 DDL 操作完畢後，才能繼續操作。

引數含義：

Handle address : 被載入的物件的地址。

Lock address : 鎖的地址。

Mode : 被載入物件的資料片段。

Namespace : 被載入物件在v$db_object_cache 檢視中的 namespace 的名稱。

最佳化方向：最佳化方向是檢視鎖定物件，減少爭用。

十： SQL*Net Events

型別：

應用類:

SQL*Net break/reset to client

如果執行的程式碼中包含某種可能的錯誤，且在呼叫中觸發了的話，伺服器端本地的服務程式有義務對遠端客戶端告知該資訊，這個告知的過程中服務程式就處於 SQL*Net break/reset to client 等待中，直到客戶端收到問題資訊為止。

SQL*Net break/reset to dblink

這個等待事件和SQL*Net more data to client 等待事件基本相同，只不過等待發生在分散式事務中，即本地資料庫需要將更多的資料透過 dblink 傳送給遠端資料庫。由於傳送的資料太多或者網路效能問題，就會產生 SQL*Net more data to dblink 等待事件。  

空閒類：

SQL*Net vector message from dblink

SQL*Net vector message from client

SQL*Net message from client

表示服務端等待著Cilent 發來請求讓它處理，這時就會產生 SQL*Net message from client 等待事件。

網路類：

SQL*Net more data from dblink

SQL*Net vector data to client

SQL*Net vector data from client

SQL*Net vector data to dblink

SQL*Net vector data from dblink

SQL*Net message from dblink

SQL*Net more data from client

伺服器端等待使用者端發出更多的資料以便完成操作，比如一個大的SQL 文字，導致一個 SQL*Net 資料包無法完成傳輸，這樣伺服器端會等待客戶端把整個 SQL 文字發過來在做處理。

SQL*Net more data to dblink

這個等待事件和SQL*Net more data to client 等待事件基本相同，只不過等待發生在分散式事務中，即本地資料庫需要將更多的資料透過 dblink 發生給遠端資料庫。由於傳送的資料太多或者網路效能問題導致的等待。

SQL*Net more data to client

這說明資料庫在向客戶端不停傳送 太多 的資料。如果網路狀況不好，或者網路流量過大，都可能導致這一等待非常顯著 。

SQL*Net message to client

這個等待事件發生在服務 端 向客戶端傳送訊息或資料的時候，一般意味著網路瓶頸或不正確的TCP 連線配置。當然它不能做為對網路延遲的準確評估或量化。

SQL*Net message to dblink

這個等待事件發生在會話在等待一個遠端資料庫一個確認資訊，確認其傳送的資料遠端資料庫是否收到，該資料透過dblink 傳送，一般是由於目標伺服器無法及時接受資訊。

參考：

http://www.itpub.net/thread-2102514-1-1.html

http://www.itpub.net/thread-1777234-1-1.html

歡迎關注我的微信公眾號"IT小Chen"，共同學習，共同成長！！！