OWI效能診斷與調整實踐指南（1~4）

第1章、    介紹Oracle Wait Interface 

在v$sesstat 和v$sysstat 檢視中有關於CPU used by this session 和CPU used when call started 的統計資料，但是推薦使用後者，因為後者比前者要穩定。

第2章、  Oracle Wait Interface 元件 

1.          等待事件：當一個會話沒有使用CPU 時，他可能正在等待一個資源、一個待完成的動作、或者僅僅是更多的工作，所有與這類等待相關的事件都被稱為等待事件。

2.        OWI 中有三個最為重要的效能檢視：v$session_wait ，v$session_event ，v$system_event ，他們之間的關係是：v$session_wait < v$session_event < v$system_event 。

3.        V$event_name 顯示了每個等待事件的屬性和他的類別，該檢視不是動態的，不會隨時間變化，其中event_id ，wait_class_id ，wait_class# ，wait_class 列屬於10g 中新增的列，id 列式相應name 列的hash 值，即使版本升級了，但是隻要相應的name 沒有變化，則該列的值也不會變化。

4.        V$system_event 中time_waited 列的單位是釐秒（1/100 秒），他是通過time_waited_micro 除以10000 得到的，time_waited_micro 的單位是微秒（1/1000000 秒）。

5.        V$session_wait 中seq# 是與會話有關的事件的內部序列號，每次會話等待時，他會遞增，wait_time 記錄等待事件已等待的時間量（單位：s），負表示未知、0 表示任在等待、正表示實際等待時間，seconds_in_wait 在等待事件時用秒為單位的等待時間。State 、wait_time 、seconds_in_wait 這三列應該統一起來看。

6.        V$session_wait 檢視具有實時特性，該檢視在任何時間，只包含每個會話的一行活動或不活動的資訊，可能在每次查詢中都會顯示不同的結果，比較適合檢視某個特定時刻會話的等待資訊。

7.        追蹤事件10046 能夠提供大量更加豐富的資訊，他是SQL 追蹤的超集。他的級別分為：0 、關閉；1 、標準SQL 追蹤；4 、標準SQL 追蹤+繫結變數；8 、標準SQL 追蹤+等待資訊；12 、1+4+8 。影響該事件的引數有：timed_statistics 、max_dump_file_size 。

8.        啟用10046 事件方法：

u  例項級：pfile 中新增event= ”10046 trace name context forever,level 12 ”;

u  會話級：

1)         alter session set tracefile_identifier= ’xxx ’;（可選）

alter session set events  ”10046 trace name context forever,level 12 ”;

alter session set events  ”10046 trace name context off ”;

2)      dbms_support 包，此包需要需要執行rdbms 中 的dbmssupp.sql 建立。

Exec dbms_support.start_trace;

Exec dbms_support.stop_trace;

3)      Exec dbms_support.start_trace_in_session

(sid=>123,serial#=>4567,waits=>true,binds=>true);

Exec dbms_support.stop_trace_in_session

(sid=>123,serial#=>4567);

4)      Exec dbms_system.set_ev(123,4567,10046,12, ’xxx ’);

Exec dbms_system.set_ev(123,4567,10046,0, ’xxx ’);

5)      SQL > Oradebug setospid 23423

SQL > Oradebug unlimit;

SQL > Oradebug event 10046 trace name context forever,level 8;

SQL > Oradebug event 10046 trace name context off;

SQL > Oradebug tracefile_name

6)      Exec dbms_monitor.session_trace_enable

(session_id=>123,serial_num=>4567,waits=>true,binds=>true);

Exec dbms_monitor.session_trace_disable

(session_id=>123,serial_num=>4567);

7)      Exec dbms_monitor.serv_mod_act_trace_enable

(service_name=> ’xxx ’,module_name=> ’xxx ’,action_name=> ’xxx ’,waits=>true,binds=>true,instance_name=>null);

Exec dbms_monitor.serv_mod_act_trace_disable

(service_name=> ’xxx ’,module_name=> ’xxx ’,action_name=> ’xxx ’);

9.        Oracle 10g 中新的OWI 檢視：

u  v$session_wait_history ：每個sid 提供10 條等待資訊，其中seq#=10 的最老，seq#=1 的最新。

u  V$system_wait_class ：等待類在例項級別的總等待和等待時間。

u  V$session_wait_class ：與v$system_wait_class 類似作用，只是粒度不同。

u  V$event_histogram ：每個等待事件的直方圖資訊，但是其中的桶大小是固定了，不可改變，通過wait_time_milli 列可以檢視桶大小。

 

第3章、  常見的等待事件

1.          Buffer busy wait 等待事件（屬於concurrency 類）：

l  產生原因：當會話想要訪問緩衝儲存器中的資料塊，而該資料塊正在被其他會話使用時，將產生buffer busy wait 等待事件。

u  在10g 之前：其他會話可能正在從資料檔案中讀入該塊，或者正在對資料快取中的該塊進行修改。

u  在10g 之後：其他會話從資料檔案中讀入該塊作為read by other session 事件，此事件只是單純的等待其他會話對資料塊的修改。

l  10g 中還有一個buffer busy 等待事件，當會話在使用ASM 的資料庫中訪問快取記憶體的後設資料時提交buffer busy 事件（屬於other 類）。

l  該等待的引數中：P1 表示絕對檔案號；P2 表示資料所在的塊號；P3 在10g 之前表示等待原因碼，10g 之後表示v$waitclass 中的類。

l  該等待的時間為100 釐秒（1秒）。

2.        Control file parallel write 等待事件：

l  產生原因：當前會話等待對所有控制檔案的寫入請求完成時產生此事件。CKPT 每3秒觸發一次，當會話執行nolog 或unrecoverable 選項的DML 時，Oracle 需要在控制檔案中記錄不可恢復的scn ，RMAN 會話也會在控制檔案中記錄資訊，這樣就會遇到等待控制檔案寫入的等待事件。

l  引數：P1 表示伺服器正在寫入的控制檔案號碼；P2 寫入的總塊數；P3 表示I/O 請求的號碼。

3.        Db file parallel read 等待事件：

l  產生原因：作為恢復的一部分而需要更改的資料庫塊從資料檔案中並行讀取時產生該事件，當一個程式從一個資料檔案中讀取多個非連續的單獨資料塊時也產生該事件。該事件並不與任何並行操作（並行DML 、DQL ）相關。

l  引數：P1 、讀取檔案號；P2 、讀取塊總數；P3 、I/O 請求數（多塊讀取中=P2 ）。

4.        Db file parallel write 等待事件：

l  產生原因：不與任何並行操作相關，屬於DBWR 程式。當DBWR 將一組髒資料塊編譯成批。他釋出多個I/O 請求將“寫入批量”寫入到資料檔案，然後以此事件等待直到I/O 請求都完成。

l  引數：P1 、檔案號；P2 、塊數；P3 、9.2 之前=P2, 在9.2 以後表示等待I/O 完成的超時時間（以釐秒為單位）。

5.        Db file scattered read 等待事件：

l  產生原因：會話釋出一個讀入多個資料塊的I/O 請求時，提交此事件。多發生在全表掃描或索引快速掃描。

l  引數：P1 、檔案號；P2 、讀取的起始塊號；P3 、讀取的塊數。

6.        Db file sequential read 等待事件：

l  產生原因：當程式等待順序完成的I/O 完成時產生此等待。實際是是單塊的讀取，當從索引、回滾或撤銷段、rowid 訪問表、重建控制檔案、轉儲資料檔案時提交該等待事件。

l  引數：P1 、檔案號；P2 、讀取的起始塊號；P3 、讀取的塊數，大多數情況 =1，在臨時段中可能 >1 。

7.        Db file single write 等待事件：

l  產生原因：該事件有DBWR 提交，主要是在檢查點期間Oracle 更新資料檔案標題時產生。

l  引數：P1 、檔案號；P2 、起始塊號；P3 、寫入的塊數，通常 =1 。

8.        Direct path read 等待事件：

l  產生原因：Oracle 直接將資料塊讀入PGA 而不經過SGA 時產生的事件。通常是用於訪問磁碟上的臨時段，包括排序、並行查詢、雜湊連線等。

l  引數：P1 、絕對檔案號；P2 、起始塊號；P3 、讀取的塊數。

9.        Direct path write 等待事件：

l  產生原因：不經過SGA ，直接將資料從會話的PGA 寫回資料檔案時產生。通常出現於直接資料載入（APPEND 提示、CTAS ）或並行DML 中臨時段的寫入。

l  引數：P1 、絕對檔案號；P2 、起始塊號；P3 、寫入的塊數。

10.     Enqueue 等待事件：

l  產生原因：排隊是指用來序列訪問資料庫資源的一種記憶體結構。當排隊請求因其他會話以不相容模式持有物件鎖而失敗時，程式以此事件一直等待。

l  引數：P1 、請求的排隊名稱和模式，ASCII 編碼（ST ：空間管理事務的排隊；SQ ：序列號排隊；TX ：一個事務的排隊）；P2 、請求鎖的資源識別符號ID1 = v$lock.id1 ；P3 、請求鎖的資源識別符號ID2 = v$lock.id2 。

l  P1 列的檢視：col name for a10

Select sid,chr(bitand(p1,-16777216)/16777215)||

chr(bitand(p1,16711680)/65535)  “Name ”,bitand(p1,65535) “Mode ” from v$session_wait where event= ’enqueue ’;

11.        Free buffer waits 等待事件：

l  產生原因：會話在資料快取中找不到空閒空間來讀入資料塊或建立資料塊的CR （一致讀）映象時，程式會通知DBWR 寫髒資料，並且以此事件等待。通常表明Buffer 太小或者髒資料太多。

l  引數：P1 、檔案號；P2 、塊號；P3 、10g 之前不可用，之後顯示LRU 和LRUW 列表的set_id# 。

l  等待時間：Oracle 用最多一秒的時間等待，然後再次嘗試。

12.     Latch free 等待事件：

l  產生原因：程式想要獲取鎖存器而此鎖存器正在被其他程式持有時觸發此事件。一次只能一個程式獲得鎖存器，如果獲取失敗，不用佇列中等待，而是進行spin 。

l  常見的鎖存器：cache buffer chains 、library cache 、shared pool 。

l  引數：P1 、鎖存器地址；P2 、鎖存器號=v$latchname.latch# ；P3 、嘗試次數 。

13.     Library cache pin 等待事件：

l  產生原因：與庫快取記憶體的併發性有關，當會話嘗試在庫快取記憶體中釘住物件或檢查物件時產生該事件。當會話正在編譯或分析PL/SQL 過程或檢視時，Oracle 提交該事件。

l  引數：P1 、被檢查或載入的物件的地址；P2 、載入鎖的地址；P3 、包含模式加上名稱空間。

l  等待時間：PMON 為1s ，其他程式為3s 。

14.     Library cache lock 等待事件：

l  產生原因：與庫快取記憶體的併發性有關，當會話嘗試修改或檢查給定物件時，必須獲取物件控制程式碼的庫快取記憶體鎖，阻止其他會話的訪問，即有可能產生此事件。

l  引數：P1 、被檢查或載入的物件的地址；P2 、載入鎖的地址；P3 、包含模式加上名稱空間。

l  等待時間：PMON 為1s，其他程式為3s。

15.     Log buffer space 等待事件：

l  產生原因：當會話必須要等待log buffer 中的空間變成可用時，就產生此事件，這表明redo 生成的速度大於LGWR 寫redo 的速度，要麼是log buffer 太小，要麼是online log 所在的磁碟上存在I/O 爭用。

l  引數：無引數。

l  等待時間：通常是1s ，如果必須等待一個日誌檔案轉換完成則是5s 。

16.     Log file parallel write 等待事件：

l  產生原因：當會話等待LGWR 將重做資訊寫入到日誌成員時產生此等待，有LGWR 提交此等待。僅當使用非同步I/O 時並行寫入日誌，否則順序寫入。此等待事件通常表明online log 磁碟裝置緩慢或存在爭用。

l  引數：P1 、日誌檔案號；P2 、OS 塊號；P3 、I/O 請求的號碼。

17.     Log file sequential read 等待事件：

l  產生原因：當程式等待從聯機日誌讀入塊時產生此事件，ARCH 程式在讀取日誌檔案時會遭遇此等待。

l  引數：P1 、重做日誌的相對序列號；P2 、開始讀入的塊號；P3 、從P2 開始讀入的OS 塊號。

18.     Log file switch （archive needed ）等待事件：

l  產生原因：在寫入重做日誌檔案時ARCH 程式跟不上LGWR 程式時產生此等待。

l  引數：沒有引數

l  等待時間：1s 。

19.     Log file switch （checkpoint incomplete ）等待事件：

l  產生原因：在日誌轉換的過程中，由於檢查點未完成而使得日誌檔案的轉換變得不可能，則會產生此事件。

l  引數：沒有引數

l  等待時間：1s 。

20.   Log file switch completion 等待事件：

l  產生原因：程式在等待日誌檔案轉換完成的過程中，有可能產生此事件。

l  引數：沒有引數

l  等待時間：1s 。

21.     Log file sync 等待事件：

l  產生原因：當使用者會話結束一個事務時，LGWR 必須將redo buffer 資訊寫入日誌檔案中，才能繼續。程式以此等待事件等待LGWR 程式對日誌檔案的I/O 。

l  引數：P1 、需要同步的日誌緩衝區的緩衝區號；P2 、P3 未使用。

l  等待時間：1s 。

22.   SQL*NET message from client 等待事件：

l  產生原因：會話等待一個來自客戶端的訊息時的等待事件。一般意味著會話處於空閒狀態，在批處理中此等待太多則有可能是網路層面的問題。

l  引數：P1 、（ASCII 碼）客戶端使用的網路驅動器型別，如：bequeath 、TCP ；P2 、會話接受來自客戶端的位元組數（通常是1，即使資料包中的位元組大於1）；P3 、未使用。

23.   SQL*NET message to client 等待事件：

l  產生原因：會話傳送訊息至客戶端，而客戶端可能太忙或者網路的延遲等而不能接收訊息的傳送，則產生了此事件。

l  引數：P1 、（ASCII 碼）客戶端使用的網路驅動器型別，如：bequeath 、TCP ；P2 、會話接受來自客戶端的位元組數（通常是1，即使資料包中的位元組大於1）；P3 、未使用。

 

RAC 環境中常見的等待事件：

1.          Global cache cr request （gc cr request ）等待事件：

l  產生原因：會話等待遠端例項上的高速緩衝區的一致讀副本時，產生此等待。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

l  常見塊類：0 、系統回滾段；1 、資料塊；2 、排序塊；3 、延遲迴滾段塊；4 、段標題塊；5 、延遲迴滾段標題塊；6 、空閒列表塊；7 、區對映塊；8 、位對映空間管理塊；9 、空間管理索引塊；10 未使用。

2.        Buffer busy global cache （gc buffer busy ）等待事件：

l  產生原因：類似於單例項中的buffer busy wait ，想修改遠端buffer cache 中的塊時產生的事件。

l  引數：P1 、檔案號；P2 、塊號；P3 、表示原因的數值型程式碼。

3.        Buffer busy global cr （gc cr block busy ）等待事件：

l  產生原因：當一個以上的會話在同一個例項中排隊等待遠端例項中的一個CR 副本時，就會在此事件上等待。

l  引數：P1 、檔案號；P2 、塊號；P3 、表示原因的數值型程式碼。

4.        Global cache busy 等待事件：

l  產生原因：會話想要修改以共享模式持有的塊時，必須在此事件上等待。通常global cache s to x 緊跟在此事件之後發生，如果這些等待過長，則表示節點之間的網路互聯太慢。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

5.        Global cache null to x 等待事件：

l  產生原因：當會話要修改塊時，必須以獨佔模式在本地快取記憶體中持有該塊。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

l  等待事件：1s 。

6.        Global cache null to s 等待事件：

l  產生原因：當會話要讀取塊時，必須從空模式轉換到共享模式。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

l  等待事件：1s 。

7.        Global cache s to x 等待事件：

l  產生原因：會話以共享模式持有緩衝區而想要轉換到獨佔模式時。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

l  等待事件：1s 。

8.        Global cache open x 等待事件：

l  產生原因：該塊沒有以任何模式快取本地例項，是一個新塊時，通常在此塊進行插入或批量載入。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

l  等待事件：1s 。

9.        Global cache open s 等待事件：

l  產生原因：第一次將塊讀入本地磁碟。

l  引數：P1 、檔案號；P2 、塊號；P3 、塊類別。

l  等待事件：1s 。

10.     Row cache lock 等待事件：

l  產生原因：字典的快取記憶體是行快取，以行級別儲存資訊。DDL 操作請求row cache lock ，用此事件來鎖定資料字典資訊。

l  引數：P1 、行快取記憶體的ID 號，可從v$rowcache 獲得；P2 、持有鎖的模式；P3 、請求鎖的模式。

OWI 不統計CPU 資訊，但是關於CPU 的資訊可以由v$sysstat 和v$sesstat 檢視顯示出來，v$statname 顯示了相關統計的列表。

第4章、  OWI 監控和收集方法

l  將database logoff trigger 用作資料收集器：

Create or replace trigger xxx before logoff on database …;

在其中填充兩張歷史資料儲存表（必須預先建立），儲存v$session_event 和v$sesstat 中相關的會話資訊，這樣就能在每個會話登出時，自動收集該會話相關的資訊，留作以後效能診斷的參考資料。

l  利用PL/SQL 過程進行效能資料取樣：

1)         資料來源：v$session_wait ，該檢視實時性比較高，非常適合定時資料的收集。

2)      取樣頻率：可以適量考慮（例如：1分鐘）。

3)      儲存庫：需要儲存等待事件儲存庫和SQL 語句儲存庫。

4)      監控的事件：可以先監控部分常見等待事件，然後再後續新增其他等待事件。

l  對於不同的等待事件，要同時儲存等待事件涉及到的相關物件資訊和SQL 語句，其中可能涉及到的檢視有：v$session_wait 、v$session 、v$sql 、dba_extents 、dba_segments 、v$sql_text 、dba_objects 、v$latchname 、v$datafile 、v$tempfile(file# 的檢視需要加上v$parameter 中的db_files) 、v$lock 等。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24465008/viewspace-688031/，如需轉載，請註明出處，否則將追究法律責任。