Oracle面試寶典-等待事件篇

Oracle 面試寶典 - 等待事件篇

請問Oracle 數據庫中等待事件的作用是什么 ?

一、等待事件由來

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

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

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

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

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

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

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

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

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

以各種資源整體消耗為優化入口依據?？梢詮恼w角度了解數據庫在一段時間內的消耗情況。較等待事件的方式，更有概括性。常見的如"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 發送，一般是由于目標服務器無法及時接受信息。

參考：

https://dbaplus.cn/news-10-777-1.html

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

http://www.askmaclean.com/archives/db-file-sequential-read-wait-event.html

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

https://www.linuxidc.com/Linux/2015-09/122732.htm

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