oracle之 調整 I/O 相關的等待

I/O相關競爭等待簡介

當Oracle資料庫出現I/O相關的競爭等待的時候，一般來說都會引起Oracle資料庫的效能低下，發現資料庫存在I/O相關的競爭等待一般可以透過以下的三種方法來檢視Oracle資料庫是否存在I/O相關的競爭等待：

（1）Statpack報告中在"Top 5 Wait Events"部分中主要都是I/O相關的等待事件。

（2）資料庫的等待事件的SQL語句跟蹤中主要都是I/O相關的等待事件的限制。

（3）作業系統工具顯示儲存資料庫檔案的儲存磁碟有非常高的利用率。

 

資料庫如果發現存在I/O競爭，那我們就必須要透過各種方法來調整最佳化Oracle資料庫。在調優資料庫的過程中，其中一個重要的步驟就是對響應時間的分析，看看資料庫消耗的時間究竟是消耗在具體什麼上面了。對於Oracle資料庫來說，響應時間的分析可以用下面公式來計算：

Response Time = Service Time + Wait Time

 

Service Time是指'CPU used by this session'的統計時間。Wait Time是指所有消耗在等待事件上的總的時間。

 

因為等待事件有很多，因此我們還需要去判定哪些是真的很重要的等待事件，很多調優工具比如說statpack都是列出最重要的等待事件，statpack工具的報告中的重要的等待事件都是包含在一個叫Top 5 Wait Events的部分中。在某些情況下， Service Time會比Wait Time顯得更加重要(例如CPU使用率),此時等待事件產生的影響就顯得不是那麼重要了，重點調整的目標應該放在Service Time上。因此，我們應該先比較在Top 5 Wait Events部分中的'CPU used by this session'所佔用的時間，然後直接調整最消耗時間的等待事件。在Oracle9i的release2的版本以後，Top 5 Wait Events部分變成了Top 5 Timed Events，Service Time也由'CPU used by this session'變成了'CPU time'來衡量，這也就意味著可以更加精確的判斷在響應時間中的等待事件的影響，從而調整最需要最佳化的部分。

 

下面舉一個例子來具體說明為什麼在調整資料庫效能的時候必須同時檢視Service Time 和Wait Time，因為如果不同時都檢視這兩個方面，就往往容易走入調整的誤區。

 

上面是一個大約30分鐘的statpack收集的資訊的Top 5 Wait Events部分，如果基於上面給出的列表，我們很容易發現direct path read的wait很高，並且會試圖去調整這個等待事件，但是這樣做就沒有考慮到Service Time。我們來看看在這個statpack中關於Service Time的統計：

 

讓我們來大致的計算一下響應時間：

接著來計算一下響應時間中各個部分的比例：

從上面的計算中我們可以明顯的看出來，I/O相關的等待事件所消耗的時間在整個響應時間中佔的比例並不大，只不過是很小的一部分，而相對來說Service Time所消耗的時間遠遠大於Wait Time，因此，應該直接調整的是Service Time(CPU的使用率)而不是I/O相關的等待事件，因此，在調優資料庫的時候要儘量的避免走入這種誤區。

 

I/O相關競爭等待的處理方法

接著來具體看看對於出現的I/O問題處理的一些方法。

如果資料庫的效能主要是被一些I/O相關的等待事件所限制住了，那麼可以針對這種情況採用處理I/O問題的一些方法，下面對這些方法的一些概念和基本原理進行簡單的闡述說明。

 

方法一：最佳化Oracle資料庫的SQL語句來減少資料庫對I/O的需求

如果資料庫沒有任何使用者的SQL執行的話，一般來說只會產生很少的磁碟I/O或者幾乎沒有磁碟I/O，基本上來說資料庫產生I/O的最終原因都是直接或者間接的由於使用者執行SQL語句導致的。透過最佳化SQL語句改變其執行計劃以便讓其產生儘可能少的I/O。讓使用者執行的SQL語句最佳化產生比較好的執行計劃來減少磁碟I/O是一種非常行之有效的方法。

 

方法二：調整例項的初始化引數來減少資料庫的I/O需求：

一般來說可以透過兩種途徑：

一種途徑是透過記憶體快取來減少I/O。可以透過使用一定數量的記憶體快取來減少物理I/O(例如快取記憶體區、日誌快取區以及各種排序區等等)。

 

另外一種途徑是調整一次讀取多個BLOCK的大小。在配置資料庫初始化引數的時候，根據作業系統的I/O吞吐能力設定的一次讀取多個BLOCK的大小盡量多，以減少讀取I/O的次數。

 

方法三：在作業系統級別上最佳化I/O：

在作業系統級別上最佳化磁碟的I/O，以提高I/O的吞吐量，如果作業系統支援非同步I/O，儘量去使用非同步I/O；還可以使用高階檔案系統的一些特性，例如直接I/O讀取，忽略掉作業系統的檔案快取，也就是我們平時所說的使用裸裝置。還有一種可行的方法是增大每次傳輸的最大I/O大小的限制，以便每次能夠傳輸的I/O儘可能的大。

 

方法四：透過使用RAID、SAN、NAS來平衡資料庫的I/O：

因此，利用RAID、SAN、NAS的技術在多個物理磁碟之間平衡資料庫的I/O，儘量避免資料庫產生I/O競爭的瓶頸。

 

方法五：手工分配資料檔案到不同的檔案系統、控制器和物理裝置來重新調整資料庫I/O

  

需要注意的一點是對於大部分資料庫來說，一些I/O是一直會存在的。如果上述的方法都嘗試過但是資料庫的I/O效能還是沒有達到預定的要求，可以嘗試刪除資料庫中一些不用的舊資料或者使用效能更好的硬體設施。

 

I/O相關的等待事件介紹及解決方法

下面是總結的在Oracle資料庫中最經常出現的一些I/O相關的等待事件：

資料檔案I/O相關的等待事件：

控制檔案I/O相關的等待事件：

重做日誌檔案I/O相關的等待事件：

快取記憶體區I/O相關的等待事件：

下面來對這些I/O相關的等待事件進行具體的說明和相應的處理方法。

資料檔案相關的I/O等待事件：

db file sequential read等待事件：

這個是非常常見的I/O相關的等待事件。在大多數的情況下讀取一個索引資料的BLOCK或者透過索引讀取資料的一個BLOCK的時候都會去要讀取相應的資料檔案頭的BLOCK。在早期的版本中會從磁碟中的排序段讀取多個BLOCK到快取記憶體區的連續的快取中。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle要讀取的檔案的ABSOLUTE檔案號，P2代表Oracle從這個檔案中開始讀取的BLOCK號，P3代表Oracle從這個檔案開始讀取的BLOCK號後讀取的BLOCK數量，通常這個值為1，表明是單個BLOCK被讀取，如果這個值大於1，則是讀取了多個BLOCK，這種多BLOCK讀取常常出現在早期的Oracle版本中從臨時段中讀取資料的時候。

 

如果這個等待事件在整個等待時間中佔主要的部分，可以採用以下的幾種方法來調整資料庫。

 

方法一：從statpack的報告中的"SQL ordered by Reads"部分或者從V$SQL檢視中找出讀取物理磁碟I/O最多的幾個SQL語句，最佳化這些SQL語句以減少對I/O的讀取需求。

如果有Index Range scans，但是卻使用了不該用的索引，就會導致訪問更多的BLOCK，這個時候應該強迫使用一個可選擇的索引，使訪問同樣的資料儘可能的少的訪問索引塊，減少物理I/O的讀取；如果索引的碎片比較多，那麼每個BLOCK儲存的索引資料就比較少，這樣需要訪問的BLOCK就多，這個時候一般來說最好把索引rebuild，減少索引的碎片；如果被使用的索引存在一個很大的Clustering Factor，那麼對於每個索引BLOCK獲取相應的記錄的時候就要訪問更多表的BLOCK，這個時候可以使用特殊的索引列排序來重建表的所有記錄，這樣可以大大的減少Clustering Factor，例如：一個表有A,B,C,D,E五個列，索引建立在A,C上，這樣可以使用如下語句來重建表：

CREATE TABLE TABLE_NAME AS SELECT * FROM old ORDER BY A,C;

 

此外，還可以透過使用分割槽索引來減少索引BLOCK和表BLOCK的讀取。

 

方法二：如果不存在有問題的執行計劃導致讀取過多的物理I/O的特殊SQL語句，那麼可能存在以下的情況：

資料檔案所在的磁碟存在大量的活動，導致其I/O效能很差。這種情況下可以透過檢視Statpack報告中的"File I/O Statistics"部分或者V$FILESTAT檢視找出熱點的磁碟，然後將在這些磁碟上的資料檔案移動到那些使用了條帶集、RAID等能實現I/O負載均衡的磁碟上去。

 

使用如下的查詢語句可以得到各個資料檔案的I/O分佈：

 

從Oracle9.2.0開始，我們可以從V$SEGMENT_STATISTICS檢視中找出物理讀取最多的索引段或者是表段，透過檢視這些資料，可以清楚詳細的看到這些段是否可以使用重建或者分割槽的方法來減少所使用的I/O。如果Statpack設定的level為7就會在報告中產生"Segment Statistics"的資訊。

 

從上面的查詢可以看到相應的統計名稱，使用下面的查詢語句就能得到讀取物理I/O最多的段：

 

方法三：如果不存在有問題的執行計劃導致讀取過多的物理I/O的特殊SQL語句，磁碟的I/O也分佈的很均勻，這種時候我們可以考慮增大的快取記憶體區。

對於Oracle8i來說增大初始化引數DB_BLOCK_BUFFERS，讓Statpack中的Buffer Cache的命中率達到一個滿意值；對於Oracle9i來說則可以使用Buffer Cache Advisory工具來調整Buffer Cache；對於熱點的段可以使用多緩衝池，將熱點的索引和表放入到KEEP Buffer Pool中去，儘量讓其在緩衝中被讀取，減少I/O。

 

db file scattered read等待事件

這個也是一個非常常見的等待事件。當Oracle從磁碟上讀取多個BLOCK到不連續的快取記憶體區的快取中就會發生這個等待事件，Oracle一次能夠讀取的最多的BLOCK數量是由初始化引數DB_FILE_MULTIBLOCK_READ_COUNT來決定，這個等待事件一般伴隨著全表掃描或者Fast Full Index掃描一起出現。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle要讀取的檔案的ABSOLUTE檔案號，P2代表Oracle從這個檔案中開始讀取的BLOCK號，P3代表Oracle從這個檔案開始讀取的BLOCK號後讀取的BLOCK數量。如果這個等待事件在整個等待時間中佔了比較大的比重，可以如下的幾種方法來調整Oracle資料庫：

 

方法一：找出執行全表掃描者Fast Full Index掃描的SQL語句，判斷這些掃描是否是必要的，是否導致了比較差的執行計劃，如果是，則需要調整這些SQL語句。

從Oracle9i開始提供了一個檢視V$SQL_PLAN，可以很快的幫助找到那些全表掃描或者Fast Full Index掃描的SQL語句，這個檢視會自動忽略掉關於資料字典的SQL語句。

 

查詢全表掃描的SQL語句可以使用如下語句：

 

查詢Fast Full Index掃描的SQL語句可以使用如下語句：

 

如果是Oracle8i的資料庫，可以從V$SESSION_EVENT檢視中找到關於這個等待事件的程式sid，然後根據sid來跟蹤相應的會話的SQL。

 

或者可以檢視物理讀取最多的SQL語句的執行計劃，看是否裡面包含了全表掃描和Fast Full Index掃描。透過如下語句來查詢物理讀取最多的SQL語句：

方法二：有時候在執行計劃很好情況下也會出現多BLOCK掃描的情況，這時可以透過調整Oracle資料庫的多BLOCK的I/O，設定一個合理的Oracle初始化引數DB_FILE_MULTIBLOCK_READ_COUNT，儘量使得滿足以下的公式：

DB_BLOCK_SIZE x DB_FILE_MULTIBLOCK_READ_COUNT = max_io_size of system

 

DB_FILE_MULTIBLOCK_READ_COUNT是指在全表掃描中一次能夠讀取的最多的BLOCK數量，這個值受作業系統每次能夠讀寫最大的I/O限制，如果設定的值按照上面的公式計算超過了作業系統每次的最大讀寫能力，則會預設為max_io_size/db_block_size。例如DB_FILE_MULTIBLOCK_READ_COUNT設定為32，DB_BLOCK_SIZE為8K，這樣每次全表掃描的時候能讀取256K的表資料，從而大大的提高了整體查詢的效能。設定這個引數也不是越大越好的，設定這個引數之前應該要先了解應用的型別，如果是OLTP型別的應用，一般來說全表掃描較少，這個時候設定比較大的DB_FILE_MULTIBLOCK_READ_COUNT反而會降低Oracle資料庫的效能，因此CBO在某些情況下會因為多BLOCK讀取導致COST比較低從而錯誤的選用全表掃描。

 

此外，還可以透過對錶和索引使用分割槽、將快取區的LRU末端的全表掃描和Fast Full Index掃描的的BLOCK放入到KEEP快取池中等方法調整這個等待事件。

 

db file parallel read等待事件

當Oracle從多個資料檔案中並行讀取多個BLOCK到記憶體的不連續緩衝中(快取記憶體區或者是PGA)的時候可能就會出現這個等待事件。這種並行讀取一般出現在恢復操作中或者是從緩衝中預取資料達到最最佳化(而不是多次從單個BLOCK中讀取)。這個事件表明會話正在並行執行多個讀取的需求。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表有多少個檔案被讀取所請求，P2代表總共有多少個BLOCK被請求，P3代表總共有多少次請求。如果在等待時間中這個等待事件佔的比重比較大，可以按照處理db file sequential read等待事件的方法來處理這個事件。

 

direct path read等待事件

這個等待事件一般出現在Oracle將資料直接讀入到PGA記憶體中(而不是快取記憶體區)，如果系統使用了非同步I/O，那麼Oracle可以一邊提交請求一邊同時繼續處理請求，這樣能加速I/O請求的結果並會出現direct path read等待直到請求I/O完成。如果沒有使用非同步I/O，I/O請求會被阻塞直到之前的I/O請求完成後，但是此時不會出現I/O等待，會話稍後重新恢復並加速I/O請求的完成，此時就會出現direct path read等待。因此，對於這個等待事件容易產生兩方面的誤解：一是認為等待的總的數量不能反映出I/O請求的數量，二是消耗在這個等待事件上的總的時間不能反映出實際的等待時間。這型別的讀取請求主要是用於不在記憶體中排序的I/O、並行查詢以及預讀操作(提前請求一個程式即將使用的BLOCK)。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表等待I/O讀取請求的檔案的ABSOLUTE檔案號，P2代表等待I/O讀取請求的第一個BLOCK號，P3代表總共有多少個連續的BLOCK被請求讀取。

 

這個等待事件的等待時間是指等待BLOCK直到顯著的I/O請求完成的時間。值得注意的是對於非同步I/O來說等待時間並不是I/O本身所耗費的時間，因為此時等待並沒有開始，而且在這個等待事件中Oracle並不會出現超時的現象。在DSS型別的系統中，執行大量批處理操作的過程中出現這個等待事件屬於很正常的現象，然而如果在OLTP型別的系統中大量出現這個等待事件則是表明Oracle資料庫存在問題需要調整。

 

如果在等待時間中這個等待事件佔的比重比較大，可以從如下幾個方面來調整：

如果是等待的檔案是臨時表空間的資料檔案，那麼需要檢視是否存在大量不合理的磁碟排序，最佳化相應的存在問題的SQL語句。如果是Oracle9i可以考慮使用自動SQL執行記憶體管理，Oracle8i的話可以手工的調整各種排序區。儘量減少I/O請求的次數，透過設定初始化引數DB_FILE_DIRECT_IO_COUNT，使得滿足

DB_BLOCK_SIZE x DB_FILE_DIRECT_IO_COUNT = max_io_size of system

 

在Oracle8i中預設這個值為64個BLOCK；在Oracle9i中可以設定隱含引數_DB_FILE_DIRECT_IO_COUNT，引數的值也變成了BYTES而不是BLOCK數量了，預設值也變成了1M。

確認非同步I/O是否配置正確，使用非同步I/O不會減少這個等待事件的等待時間但是卻可以減少會話所消耗的時間。

 

檢查是否存在I/O消耗很嚴重的SQL語句，如果存在，嘗試最佳化SQL語句減少I/O的消耗。

最後確認一下是否達到了磁碟的I/O極限，如果是，則需要考慮更換效能更好的硬體裝置。

 

direct path read/write (lob) 等待事件

這個等待事件是從Oracle8.1.7開始出現的，表明在等待直接路徑讀取訪問一個LOB物件。

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表等待I/O讀取請求的檔案的ABSOLUTE檔案號，P2代表等待I/O讀取請求的第一個資料BLOCK地址，P3代表總共有多少個連續的BLOCK被請求讀取。

 

對於那些沒有cache的LOB物件，強烈建議將其所在的資料檔案放置在存在快取的磁碟上(例如檔案系統)，這樣使得直接讀取操作能夠受益於那些非Oracle的cache，加快讀取的速度。

 

控制檔案相關I/O等待事件：

control file parallel write等待事件

這個等待事件表明伺服器程式在更新所有的控制檔案的時候等待I/O的完成。因為控制檔案所在的磁碟的I/O過高引起無法完成對所有控制檔案的物理寫入，寫入控制檔案的這個會話會擁有CF佇列，因此其他的會話都會在這個佇列中等待。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，這三個引數都設定為同樣的值，代表控制檔案對I/O的請求數量。當Oracle更新控制檔案的時候是同時更新所有控制檔案並寫入同樣的資訊。

 

如果在等待時間中這個等待事件佔的比重比較大，可以從如下幾個方面來調整：在確保控制檔案不會同時都丟失的前提下，將控制檔案的數量減小到最少。如果系統支援非同步I/O，則推薦儘量使用非同步I/O，這樣可以實現真正並行的寫入控制檔案。將控制檔案移動到負載比較低，速度比較快的磁碟上去。

 

control file sequential read等待事件

讀取控制檔案的時候遇到I/O等待就會出現這個等待事件，例如備份控制檔案的時候、讀取BLOCK頭部都會引起這個等待事件，等待的時間就是消耗在讀取控制檔案上的時間。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表正在讀取的控制檔案號，透過下面的SQL語句可以知道究竟是具體是哪個控制文被讀取：

SELECT * FROM X$KCCCF WHERE INDX = <file#>；

 

P2代表開始讀取的控制檔案BLOCK號，它的BLOCK大小和作業系統的BLOCK大小一樣，通常來說是512K，也有些UNIX的是1M或者2M，P3代表會話要讀取BLOCK的數量。一般來說使用引數P1、P2來查詢BLOCK,當然也可以包括引數P3，但是那樣最終就變成了一個多BLOCK讀取，因此我們一般都忽略引數P3。

 

如果這個等待事件等待的時間比較長，則需要檢查控制檔案所在的磁碟是否很繁忙，如果是，將控制檔案移動到負載比較低，速度比較快的磁碟上去。如果系統支援非同步I/O，則啟用非同步I/O。對於並行伺服器來說，如果這種等待比較多，會造成整個資料庫效能下降，因為並行伺服器之間的一些同步是透過控制檔案來實現的。

 

control file single write等待事件

這個等待事件出現在寫控制檔案的共享資訊到磁碟的時候，這是個自動操作，並且透過一個例項來保護的，如果是並行的資料庫伺服器，那麼對於並行伺服器來說也只能有一個例項能夠執行這個操作。這個事件的等待事件就是寫操作所消耗的時間。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表正在讀取的控制檔案號，透過下面的SQL語句可以知道究竟是具體是哪個控制文被讀取：

SELECT * FROM X$KCCCF WHERE INDX = <file#>；

 

P2代表開始讀取的控制檔案BLOCK號，它的BLOCK大小和作業系統的BLOCK大小一樣，通常來說是512K，也有些UNIX的是1K或者2K，P3代表會話要讀取BLOCK的數量。一般來說使用引數P1、P2來查詢BLOCK,當然也可以包括引數P3，但是那樣最終就變成了一個多BLOCK讀取，因此我們一般都忽略引數P3。

 

儘管這個事件的是single write，事實上也會出現多BLOCK寫的情況，即P3>1。使用引數P1、P2來查詢檢測BLOCK而不用去考慮P3的值。

 

如果這個等待事件等待的時間比較長，則需要檢查控制檔案所在的磁碟是否很繁忙，如果是，將控制檔案移動到負載比較低，速度比較快的磁碟上去。如果系統支援非同步I/O，則啟用非同步I/O。對於並行伺服器來說，如果這種等待比較多，會造成整個資料庫效能下降，因為並行伺服器之間的一些同步是透過控制檔案來實現的。

 

重做日誌檔案相關的等待事件：

log file parallel write等待事件

這個等待事件出現在當LGWR後臺程式從日誌緩衝區寫日誌資訊到磁碟上的重做日誌檔案的時候。只有啟用了非同步I/O的時候LGWR程式才會並行寫當前日誌組內的重做日誌檔案，否則LGWR只會迴圈順序逐個的寫當前日誌組重做日誌檔案。LGWR程式不得不等待當前日誌組所有的重做日誌檔案成員全部寫完，因此，決定這個等待事件的等待時間長短的主要因素是重做日誌檔案所在磁碟的I/O讀寫的速度。

 

如果是當前的LGWR程式寫的速度不夠快導致了這個等待事件，可以透過檢視一些和重做日誌相關的統計值判定當前的LGWR程式是否效率很低，具體的可以檢視"redo writes"、"redo blocks written"、"redo write time"、"redo wastage"、"redo size"統計值，這些都是和LGWR程式效能直接相關的一些統計值。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表正在被寫入的重做日誌檔案組中的重做日誌檔案號，P2代表需要寫入重做日誌組中每個重做日誌檔案的重做日誌BLOCK數量，P3代表I/O請求的次數，需要被寫入的BLOCK會被分成多次分別請求。

 

如果這個等待事件佔用的等待時間比較多，可以從以下幾個方面來進行調整：

（1）對能使用UNRECOVERABLE/NOLOGGING的操作儘量使用這兩個選項來減少重做日誌的產生。

（2）在保證不會同時丟失重做日誌檔案的前提下儘量減少重做日誌組中的成員的個數，減少每次寫重做日誌組檔案的時間。

（3）除非在備份的情況下，否則不要在將表空間置於熱備的模式下，因為表空間處於熱備的模式下會產生更多的重做日誌檔案。

（4）對於使用LogMiner、Logical Standby或者Streams，在能夠滿足要求功能的前提下，儘量使用最低階別的追加日誌以減少重做日誌的產生。

（5）儘量將同一個日誌組內的重做日誌檔案分散到不同的硬碟上，減少並行寫重做日誌檔案的時候產生的I/O競爭。

（6）不要將重做日誌檔案放置在RAID-5的磁碟上，最好使用裸裝置來存放重做日誌檔案。

（7）如果設定了歸檔模式，不要將歸檔日誌的目的地設定為存放重做日誌存放的磁碟上面，避免引起I/O競爭。

 

log file sync等待事件

這個等待事件是指等待Oracle的前臺的COMMIT和ROLLBACK操作程式完成，有時候這個等待事件也會包括等待LGWR程式把一個會話事務的日誌記錄資訊從日誌緩衝區中寫入到磁碟上的重做日誌檔案中。因此，當前臺程式在等待這個事件的時候，LGWR程式同時也在等待事件log file parallel write。理解什麼造成這個等待事件的關鍵在於對比這個等待事件和log file parallel write等待事件的平均等待時間：如果它們的等待時間差不多，那麼就是重做日誌檔案的I/O引起了這個等待事件，則需要調整重做日誌檔案的I/O，這個在之後會有詳細的講述。如果log file parallel write等待事件的平均等待時間明顯小於log file sync等待事件的等待時間，那麼就是一些其他的寫日誌的機制在COMMIT和ROLLBACK操作的時候引起了等待，而不是I/O引起的等待，例如重做日誌檔案的latch的競爭，會伴隨著出現latch free或者LGWR wait for redo copy等待事件。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表在日誌緩衝區中需要被寫入到重做日誌檔案中的快取的數量，寫入的同時會確認事務是否已經被提交，並且保留提交資訊到例項意外中斷之前，因此必須等待LGWR將P1數量的快取寫入重做日誌檔案為止。P2、P3屬於無用的引數。

 

如果這個等待事件在整個等待時間中佔了比較大的比重，可以從以下三個方面來調整這個等待事件：

（1）調整LGWR程式使其具有更好的磁碟I/O吞吐量，例如不要將日誌檔案放置在RAID5的磁碟上。

（2）如果存在很多執行時間很短的事務，可以考慮將這些事務集合成一個批處理事務以減少提交的次數，因為每次提交都需要確認相關的日誌寫入重做日誌檔案，因此使用批處理事務來減少提交的次數是一種非常行之有效的減少I/O的方法。

（3）檢視是否一些操作可以安全的使用NOLOGGING或者UNRECOVERABLE選項，這樣可以減少日誌的產生。

 

log file sequential read等待事件

這個等待事件是指等待讀取重做日誌檔案中的日誌記錄，等待的時間就是耗費在完成整個讀取日誌記錄的物理I/O操作的時間。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表一個日誌組裡面所有日誌檔案的相對sequence號，P2代表日誌檔案在指定物理塊大小的偏移量，P3代表讀取BLOCK的數量，如果P3的值為1，一般來說都是在讀取日誌檔案頭。

 

log file single write等待事件

這個等待事件是指等待寫重做日誌檔案操作完成，常常是在等待寫重做日誌檔案頭，例如在增加一個新的重做日誌組成員的時候，Oracle資料庫就會往這個重做日誌檔案頭寫入相應的sequence號。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表正在被寫入的重做日誌檔案組的組號，P2代表日誌檔案在指定物理塊大小的偏移量，P3代表寫入BLOCK的數量。

 

因為single write通常都是在寫或者重寫日誌檔案頭的時候出現，因此開始的block號總是為1。一般如果出現這個等待事件，應該對重做日誌檔案儘量使用裸裝置，避免將多個日誌檔案放在同一個磁碟上，減少產生I/O競爭的可能。

 

switch logfile command等待事件

這個等待事件是指執行日誌檔案切換命令的時候等待日誌檔案切換完成，Oracle資料庫會每隔五秒鐘就檢測一次是否超時。

 

如果出現這個等待事件，表明花費了很長的時間去切換重做日誌檔案，此時我們需要去檢查資料庫的告警日誌檔案檢視Oracle後臺程式LGWR是否正常在工作。

 

log file switch completion等待事件

這個等待事件是指由於當前重做日誌檔案已經被寫滿了而Oracle後臺程式LGWR需要完成寫完當前重做日誌檔案並且要開啟一個新的重做日誌檔案而導致的重做日誌檔案切換的等待，或者是其他請求需要切換重做日誌檔案導致等待。

 

如果當前的重做日誌寫滿了，這個時候Oracle資料庫就需要切換重做日誌檔案來提供足夠的磁碟空間給重做日誌寫日誌快取。但是由於一些其他的程式也同樣可以引起重做日誌的切換，Oracle資料庫不會同時去切換重做日誌兩次，因此，就出現了這個等待事件，在Oracle資料庫早期的版本中還有log_file_switch_checkpoint_incomplete、log_file_switch_archiving_needed、log_file_switch_clearing_log_file的等待事件。

 

log file switch (checkpoint incomplete) 等待事件

這個等待事件是指由於當前重做日誌的檢查點沒有及時的完成而導致重做日誌檔案無法切換到下一個日誌檔案引起的日誌檔案切換的等待。

 

調整這個等待事件的方法一般是加速檢查點的完成，可以透過減小buffer cache緩衝區或者增加更多的DBWR程式、調整相關檢查點的初始化引數等方法來達到相應的效果。

   

log file switch (archiving needed)等待事件

這個等待事件是指當前的重做日誌檔案準備切換到下一重做日誌檔案，但是當前重做日誌檔案因為沒有被歸檔而導致等待，這個等待事件只出現於採用了歸檔方式的Oracle資料庫中。

 

如果出現這個等待事件，首先應該檢視Oracle資料庫的告警日誌檔案，看是否因為寫歸檔日誌檔案錯誤導致歸檔程式停止，其次，可以增加歸檔程式的數量或者將歸檔日誌檔案存放到I/O速度比較快的磁碟上，還可以透過增大和增加重做日誌檔案的大小和數量來給予歸檔更多的時間。

 

快取記憶體區相關的I/O等待事件：

db file parallel write等待事件

這個等待事件是指Oracle後臺程式DBWR等待一個並行寫入檔案或者是BLOCK的完成，等待會一直持續到這個並行寫入操作完成。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle正在寫入的資料檔案的數量，P2代表操作將會寫入多少的BLOCK數量，P3在Oracle9i release2版本之前代表總共有多少BLOCK的I/O請求，等於P2的值；在Oracle9i release2版本之後則代表等待I/O完成的超時的時間，單位是百分之一秒。

 

這個等待事件即使在總的等待時間中佔的比例比較大也不會對使用者的會話有很大的影響，只有當使用者的會話顯示存在大量的等待時間消耗在"write complete waits" 或者是"free buffer waits"上的時候才會影響到使用者的會話，較明顯的影響是這個寫操作的等待會影響到讀取同一個磁碟上資料的使用者會話的I/O。

 

db file single write等待事件

這個等待事件通常是表明在等待寫入資料到資料檔案頭。

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle正在寫入的資料檔案的檔案號：

SELECT * FROM v$datafile WHERE file# = <file#>;

 

P2代表Oracle正在寫入的BLOCK號，如果BLOCK號不是1，則可以透過如下查詢查出Oracle正在寫入的物件是什麼：

 

P3代表Oracle寫入file＃的資料檔案中從BLOCK#開始寫入的BLOCK的數量。

Oracle資料檔案的檔案頭一般來說都是BLOCK1，作業系統指定的檔案頭是BLOCK0，如果BLOCK號大於1，則表明Oracle正在寫入的是一個物件而不是檔案頭。

 

write complete waits等待事件

這個等待事件表明Oracle的會話在等待寫入快取，一般都是快取的正常老化或者是例項之間的互相呼叫引起的。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle正在寫入的資料檔案的檔案號，P2代表Oracle正在寫入的BLOCK號，可以透過如下查詢查出Oracle正在寫入的物件是什麼：

 

P3代表產生這個等待事件原因的id號，具體的id號所代表的原因如下：

1022 無

1027 在寫入過程中的buffer，最多等待1秒後會重新掃描cache

1029 試圖應用改變到正在寫入中的BLOCK，會一直等到BLOCK可用，每次等待的時間都是1秒

1030 無

1031 無

1033 無

1034 例項間的交叉寫：一個例項企圖去修改一個BLOCK，而另外一個例項想去獲得此BLOCK的狀態，這個例項產生最多一秒鐘的等待，1035 與1034一樣，但是產生的原因是由於資料庫出於熱備的狀態下。

 

當Oracle的後臺程式DBWR獲取可以寫入的快取並標記這些快取為正在寫入的狀態，接著這些被收集的快取中的資料將會被寫入磁碟上的資料檔案中，當所有的I/O完成後將清除在原來那些被標記的快取上的標記，這個等待事件出現意味著Oracle想獲取的buffer已經被標記為正在寫入的狀態，只有等標記被清除才能獲取到相應的buffer。在Oracle7.2以前的版本中，只有當批處理中所有的buffer都被寫入磁碟後標記才被清除，在這之後的版本，每個buffer寫入磁碟後就將清除在這個buffer上的標記了。增加更多的Oracle後臺DBWR程式或者是採用非同步I/O都將能減少這個等待事件的產生。

 

free buffer waits等待事件

這個等待事件出現的原因比較多，大致可以分為以下幾種：

（1）當一個資料檔案從只讀狀態變成為可讀寫狀態的時候，所有的buffer gets全部都被掛起了，就可能出現這個等待事件。已經存在的所有buffer都必須要失效，因為它們沒有連結到lock elements(OPS/RAC環境下時需要)。因此，只有當這些buffers失效完成後才能夠被分配給資料庫塊地址。

 

（2）當Oracle資料庫需要從系統全域性區(SGA)中讀取一個buffer給一致性讀(CR)操作，只讀操作或者是用於任何恢復模式中的操作的時候，也可能出現這個等待事件，此時可以加速Oracle後臺的DBWR程式來獲得較多的空閒buffer。在檢查了'free buffers inspected'之後也會出現這個等待事件，如果沒有找到空閒的buffer，Oracle會等待1秒鐘後繼續試圖去獲取空閒的buffer。

 

在V$SESSION_WAIT這個檢視裡面，這個等待事件有三個引數P1、P2、P3，其中P1代表Oracle讀取buffer而引起等待的資料檔案的檔案號，P2代表資料檔案中讀取buffer的BLOCK的號，P3代表要讀取buffer的快取中的BLOCK(7.3.X以上的版本)。

 

一般來說，這個等待事件都是由於Oracle的後臺程式DBWR不能及時的將buffer寫完到磁碟上的資料檔案中而引起的，儘量將I/O平均分配到各個磁碟上，減少出現某個磁碟上I/O負載很高而引起DBWR程式寫入慢的情況，可以透過作業系統上的I/O監控工具或者查詢V$FILESTAT檢視來獲取相應的資料：

 

還可以透過檢視資料檔案上是否存在全表掃描來判斷：

 

需要注意在應用中要避免漏建立了索引，這樣會引起I/O大幅度的增加，導致不必要的磁碟掃描，如果有多塊硬碟來儲存Oracle的資料檔案，儘量使用作業系統的條帶化軟體來分佈Oracle的資料檔案使得I/O分配均勻。此外，大量的磁碟排序會導致存在很多的髒快取需要寫完，因此，臨時表空間中的資料檔案最好能分配到不同的磁碟上，避免同一個磁碟上的I/O競爭。還有如果排序的BLOCK的檢查點沒有完成，將會存在於正常的快取寫批處理中，如果快取寫批處理中全部都被排序塊給佔滿了，那其他的髒資料塊就沒法被寫入導致前臺的應用不得不等待分配空閒的buffer。對於Oracle9i之後的版本，因為排序使用的塊通常都是來自臨時表空間檔案，不會進入到快取中，因此，由於大量排序引起的這種等待在9i中基本上就不會存在了。

 

瞭解了在Oracle資料庫I/O效能或者是響應時間低下的時候該如何去調整和最佳化資料庫，還有一點很重要的需要提及的是，無論是何種情況，都應該先去檢查作業系統上的日誌檔案，因為如果是本身在作業系統級別上出現了I/O問題，那不管如何調整Oracle資料庫都是徒勞的，所以必須首先要保證在作業系統級別上I/O不存在問題，然後再去Oracle資料庫中具體的檢查問題產生的原因。

 

 

結論

不管用何種方法去解決Oracle資料庫的I/O效能問題，關鍵都是先找出產生I/O效能問題的根本最終原因，然後想各種各樣的辦法去解決產生的原因就可以達到最佳化資料庫的目的了。以上所談到的都是關於Oracle資料庫I/O調整最佳化的一些基本概念和方法，希望能起到一個拋磚引玉的作用，以便能夠更好的深入理解Oracle資料庫I/O效能方面的知識。

 

--the end

轉：http://mp.weixin.qq.com/s?__biz=MjM5MDAxOTk2MQ==&mid=2650271615&idx=1&sn=1a1dbfdaf4679f00d7159a6744f21ed8&chksm=be487769893ffe7fa19abf3c3e12971810d5e367bb431b4e874d7b0c6f48d4ef18fa0e6f38fb&mpshare=1&scene=23&srcid=0830IRqfBptEbq7GS3gXN4pW#rd ， 感謝恩墨分享