oracle等待事件____oracle

之前整理過一篇文章：

            Oracle 等待事件

            http://blog.csdn.net/tianlesoftware/archive/2010/05/31/5635934.aspx

 

一．等待事件的相關知識：

 

1.1 等待事件主要可以分為兩類，即空閑(IDLE)等待事件和非空閑(NON-IDLE)等待事件。

1). 空閑等待事件指ORACLE正等待某種工作，在診斷和最佳化資料庫的時候，不用過多注意這部分事件。

2). 非空閑等待事件專門針對ORACLE的活動，指資料庫任務或應用運行過程中發生的等待，這些等待事件是在調整資料庫的時候需要關注與研究的。

 

 

在Oracle 10g中的等待事件有872個，11g中等待事件1116個。 我們可以通過v$event_name 視圖來查看等待事件的相關資訊。

 

1.2 查看v$event_name視圖的欄位結構：

SQL> desc v$event_name;

 名稱                   是否為空白? 類型

 ----------------------------------------- -------- ---------------

 EVENT#                NUMBER

 EVENT_ID              NUMBER

 NAME                 VARCHAR2(64)

 PARAMETER1          VARCHAR2(64)

 PARAMETER2          VARCHAR2(64)

 PARAMETER3          VARCHAR2(64)

 WAIT_CLASS_ID        NUMBER

 WAIT_CLASS#          NUMBER

 WAIT_CLASS           VARCHAR2(64)

 

1.3 查看等待事件總數：

SQL> select count(*) from v$event_name;

  COUNT(*)

----------

      1116

 

 

1.4 查看等待事件分類情況：

/* Formatted on 2010/8/11 16:08:55 (QP5 v5.115.810.9015) */

  SELECT   wait_class#,

           wait_class_id,

           wait_class,

           COUNT ( * ) AS "count"

    FROM   v$event_name

GROUP BY   wait_class#, wait_class_id, wait_class

ORDER BY   wait_class#;

 

WAIT_CLASS# WAIT_CLASS_ID WAIT_CLASS                count

----------- ------------- -------------------- ----------

          0    1893977003 Other                       717

          1    4217450380 Application                  17

          2    3290255840 Configuration                24

          3    4166625743 Administrative               54

          4    3875070507 Concurrency                  32

          5    3386400367 Commit                        2

          6    2723168908 Idle                         94

          7    2000153315 Network                      35

          8    1740759767 User I/O                     45

          9    4108307767 System I/O                   30

         10    2396326234 Scheduler                     7

         11    3871361733 Cluster                      50

         12     644977587 Queueing                      9

 

1.5 相關的幾個視圖：

V$SESSION： 代表資料庫活動的開始，視為源起。

V$SESSION_WAIT：  視圖用以即時記錄活動SESSION的等待情況，是當前資訊。

V$SESSION_WAIT_HISTORY：  是對V$SESSION_WAIT的簡單增強，記錄活動SESSION的最近10次等待。

V$SQLTEXT：  當資料庫出現瓶頸時，通常可以從V$SESSION_WAIT找到那些正在等待資源的SESSION，通過SESSION的SID，聯合V$SESSION和V$SQLTEXT視圖就可以捕獲這些SESSION正在執行的SQL語句。

V$ACTIVE_SESSION_HISTORY: 是ASH的核心，用以記錄活動SESSION的曆史等待資訊，每秒採樣一次，這部分內容記錄在記憶體中，期望值是記錄一個小時的內容。

WRH#_ACTIVE_SESSION_HISTORY : 是V$ACTIVE_SESSION_HISTORY在AWR的儲存地。

V$ACTIVE_SESSION_HISTORY: 中的資訊會被定期(每小時一次)的重新整理到負載庫中，並預設保留一個星期用於分析。

DBA_HIST_ACTIVE_SESS_HISTORY: 視圖是WRH#_ACTIVE_SESSION_HISTORY視圖和其他幾個視圖的聯合展現，通常通過這個視圖進行曆史資料的訪問。

V$SYSTEM_EVENT 由於V$SESSION記錄的是動態資訊，和SESSION的生命週期相關，而並不記錄曆史資訊，所以ORACLE提供視圖V$SYSTEM_EVENT來記錄資料庫自啟動以來所有等待事件的匯總資訊。通過這個視圖，使用者可以迅速獲得資料庫啟動並執行總體概況。

 

 

 

二． 33個常見的等待事件

 

1.      Buffer busy waits

從本質上講，這個等待事件的產生僅說明了一個會話在等待一個Buffer（資料區塊），但是導致這個現象的原因卻有很多種。常見的兩種是：

當一個會話視圖修改一個資料區塊，但這個資料區塊正在被另一個會話修改時。

當一個會話需要讀取一個資料區塊，但這個資料區塊正在被另一個會話讀取到記憶體中時。

 

Oracle 操作的最小單位是塊（Block），即使你要修改一條記錄，也需要對這條記錄所在的這個資料區塊做操作。 當你對這個資料區塊做修改時，其他的會話將被阻止對這個資料區塊上的資料做修改（即使其他使用者修改的不是目前使用者修改的資料），但是可以以一致性的方式讀取這個資料區塊（from undo）。當前的使用者修改完這個資料區塊後，將會立即釋放掉加在這個資料區塊上的獨佔鎖定，這樣另一個會話就可以繼續修改它。 修改操作是一個非常短暫的時間，這種加鎖的機制我們叫Latch。

 

當一個會話修改一個資料區塊時，是按照以下步驟來完成的：

以排他的方式獲得這個資料區塊（Latch）

修改這個資料區塊。

釋放Latch。

 

Buffer busy waits等待事件常見於資料庫中存在的熱快的時候，當多個使用者頻繁地讀取或者修改同樣的資料區塊時，這個等待事件就會產生。 如果等待的時間很長，我們在AWR或者statspack 報告中就可以看到。

 

這個等待事件有三個參數。 查看有幾個參數我們可以用以下SQL:

            SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name='buffer busy waits';

NAME         PARAMETER1  PARAMETER2  PARAMETER3

--------------------  ----------   ----------  ----------

buffer busy waits    file#      block#     class#

 

   在下面的樣本中，查詢的方法和這個一樣，所以其他事件對參數的查詢將不做過多的說明。

 

File#: 等待訪問資料區塊所在的檔案id號。

Blocks： 等待訪問的資料區塊號。

ID： 在10g之前，這個值表示一個等待時間的原因，10g之後則表示等待事件的類別。

 

2.     Buffer  latch

記憶體中資料區塊的存放位置是記錄在一個hash列表（cache buffer chains）當中的。 當一個會話需要訪問某個資料區塊時，它首先要搜尋這個hash 列表，從列表中獲得資料區塊的地址，然後通過這個地址去訪問需要的資料區塊，這個列表Oracle會使用一個latch來保護它的完整性。 當一個會話需要訪問這個列表時，需要擷取一個Latch，只有這樣，才能保證這個列表在這個會話的瀏覽當中不會發生變化。

 

            產生buffer latch的等待事件的主要原因是：

Buffer chains太長，導致會話搜尋這個列表花費的時間太長，使其他的會話處於等待狀態。

同樣的資料區塊被頻繁訪問，就是我們通常說的熱快問題。

 

產生buffer chains太長，我們可以使用多個buffer pool的方式來建立更多的buffer chains，或者使用參數DB_BLOCK_LRU_LATCHES來增加latch的數量，以便於更多的會話可以獲得latch，這兩種方法可以同時使用。

 

這個等待事件有兩個參數：

Latch addr：會話申請的latch在SGA中的虛擬位址，通過以下的SQL語句可以根據這個地址找到它對應的Latch名稱：

            select * from v$latch a,v$latchname b where

addr=latch addr   -- 這裡的latch addr 是你從等待事件中看到的值 

and a.latch#=b.latch#;    

chain#： buffer chains hash 列表中的索引值，當這個參數的值等於s 0xfffffff時，說明當前的會話正在等待一個LRU latch。

 

3.     Control file parallel write

當資料庫中有多個控制檔案的拷貝時，Oracle 需要保證資訊同步地寫到各個控制檔案當中，這是一個並行的物理操作過程，因為稱為控制檔案並行寫，當發生這樣的操作時，就會產生control file parallel write等待事件。

控制檔案頻繁寫入的原因很多，比如：

日誌切換太過頻繁，導致控制檔案資訊相應地需要頻繁更新。

系統I/O 出現瓶頸，導致所有I/O出現等待。

 

當系統出現日誌切換過於頻繁的情形時，可以考慮適當地增大記錄檔的大小來降低日誌切換頻率。

當系統出現大量的control file parallel write 等待事件時，可以通過比如降低控制檔案的拷貝數量，將控制檔案的拷貝存放在不同的物理磁碟上的方式來緩解I/O 爭用。

 

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

            Files： Oracle 要寫入的控制檔案個數。

            Blocks： 寫入控制檔案的資料區塊數目。

            Requests：寫入控制請求的I/O 次數。

 

4.     Control file sequential read

當資料庫需要讀取控制檔案上的資訊時，會出現這個等待事件，因為控制檔案的資訊是順序寫的，所以讀取的時候也是順序的，因此稱為控制檔案順序讀，它經常發生在以下情況：

備份控制檔案

RAC 環境下不同執行個體之間控制檔案的資訊共用

讀取控制檔案的檔案頭資訊

讀取控制檔案其他資訊

 

這個等待事件有三個參數：

            File#：要讀取資訊的控制檔案的檔案號。

            Block#： 讀取控制檔案資訊的起始資料區塊號。

            Blocks：需要讀取的控制檔案資料區塊數目。

 

 

5.     Db file parallel read

這是一個很容易引起誤導的等待事件，實際上這個等待事件和並行操作（比如並行查詢，並行DML）沒有關係。 這個事件發生在資料庫恢複的時候，當有一些資料區塊需要恢複的時候，Oracle會以並行的方式把他們從資料檔案中讀入到記憶體中進行恢複操作。

 

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

            Files： 操作需要讀取的檔案個數。

            Blocks： 操作需要讀取的資料區塊個數。

            Requests：操作需要執行的I/O次數。

 

 

6.     Db file parallel write

這是一個後台等待事件，它同樣和使用者的並行操作沒有關係，它是由後台進程DBWR產生的，當後台進程DBWR想磁碟上寫入髒資料時，會發生這個等待。

 

DBWR會批量地將髒資料並行地寫入到磁碟上相應的資料檔案中，在這個批次作業完成之前，DBWR將出現這個等待事件。 如果僅僅是這一個等待事件，對使用者的操作並沒有太大的影響，當伴隨著出現free buffer waits等待事件時，說明此時記憶體中可用的空間不足，這時候會影響到使用者的操作，比如影響到使用者將髒資料區塊讀入到記憶體中。

           

當出現db file parallel write等待事件時，可以通過啟用作業系統的非同步I/O的方式來緩解這個等待。 當使用非同步I/O時，DBWR不在需要一直等到所有資料區塊全部寫入到磁碟上，它只需要等到這個資料寫入到一個百分比之後，就可以繼續進行後續的操作。

 

這個等待事件有兩個參數：

                        Requests： 操作需要執行的I/O次數。

                        Timeouts：等待的逾時時間。

 

 

7.     Db file scattered read

這個等待事件在實際生產庫中經常可以看到，這是一個使用者操作引起的等待事件，當使用者發出每次I/O需要讀取多個資料區塊這樣的SQL 操作時，會產生這個等待事件，最常見的兩種情況是全表掃描（FTS： Full Table Scan）和索引快速掃描（IFFS： index fast full scan）。

 

這個名稱中的scattered( 發散)，可能會導致很多人認為它是以scattered 的方式來讀取資料區塊的，其實恰恰相反，當發生這種等待事件時，SQL的操作都是順序地讀取資料區塊的，比如FTS或者IFFS方式（如果忽略需要讀取的資料區塊已經存在記憶體中的情況）。

 

這裡的scattered指的是讀取的資料區塊在記憶體中的存放方式，他們被讀取到記憶體中後，是以分散的方式存在在記憶體中，而不是連續的。

 

這個等待事件有三個參數：

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

Block#： 要讀取的起始資料區塊號。

Blocks：需要讀取的資料區塊數目。

 

 

8.     Db file sequential read

這個等待事件在實際生產庫也很常見，當Oracle 需要每次I/O唯讀取單個資料區塊這樣的操作時，會產生這個等待事件。 最常見的情況有索引的訪問（除IFFS外的方式），復原操作，以ROWID的方式訪問表中的資料，重建控制檔案，對檔案頭做DUMP等。

 

這裡的sequential也並非指的是Oracle 按順序的方式來訪問資料，和db file scattered read一樣，它指的是讀取的資料區塊在記憶體中是以連續的方式存放的。

 

這個等待事件有三個參數：

File#： 要讀取的資料區塊鎖在資料檔案的檔案號。

Block#： 要讀取的起始資料區塊號。

Blocks：要讀取的資料區塊數目（這裡應該等於1）。

 

 

9.     Db file single write

這個等待事件通常只發生在一種情況下，就是Oracle 更新資料檔案頭資訊時（比如發生Checkpoint）。

 

當這個等待事件很明顯時，需要考慮是不是資料庫中的資料檔案數量太大，導致Oracle 需要花較長的時間來做所有檔案頭的更新操作（checkpoint）。

 

這個等待事件有三個參數：

File#: 需要更新的資料區塊所在的資料檔案的檔案號。

Block#：需要更新的資料區塊號。

Blocks：需要更新的資料區塊數目（通常來說應該等於1）。

 

 

10.             Direct path read

這個等待事件發生在會話將資料區塊直接讀取到PGA當中而不是SGA中的情況，這些被讀取的資料通常是這個會話私人的資料，所以不需要放到SGA作為共用資料，因為這樣做沒有意義。 這些資料通常是來自與臨時段上的資料，比如一個會話中SQL的排序資料，並存執行過程中間產生的資料，以及Hash Join，merge join產生的排序資料，因為這些資料只對當前的會話的SQL操作有意義，所以不需要放到SGA當中。

 

當發生direct path read等待事件時，意味著磁碟上有大量的臨時資料產生，比如排序，並存執行等操作。 或者意味著PGA中空閑空間不足。

 

這個等待事件有三個參數：

Descriptor address:       一個指標，指向當前會話正在等待的一個direct read I/O。

First dba: descriptor address 中最舊的一個I/O資料區塊地址。

Block cnt: descriptor address上下文中涉及的有效buffer 數量。