Oracle等待事件以及解決方案

Oracle等待事件以及解決方案

我們可以通過視圖v$session_wait來檢視系統目前的等待事件，以及與等待事件相對應的資源的相關資訊，進而可确定出産生瓶頸的類型及其對象。 v$session_wait的p1、p2、p3告訴我們等待事件的具體含義，根據事件不同其内容也不相同，下面就一些常見的等待事件如何處理，以及如何定位熱點對象和阻塞會話作一些介紹。

<1> db file scattered read DB 檔案分散讀取 (太多索引讀,全表掃描-----調整代碼,将小表放入記憶體)

這種情況通常顯示與全表掃描相關的等待。當全表掃描被限制在記憶體時，它們很少會進入連續的緩沖區内，而是分散于整個緩沖存儲器中。如果這個數目很大，就表明該表找不到索引，或者隻能找到有限的索引。盡管在特定條件下執行全表掃描可能比索引掃描更有效，但如果出現這種等待時，最好檢查一下這些全表掃描是否必要。因為全表掃描被置于LRU(Least Recently Used，最近最少使用)清單的冷端（cold end），是以應盡量存儲較小的表，以避免一次又一次地重複讀取它們。

==================================================

該類事件的p1text=file#,p1是file_id，p2是block_id,通過dba_extents即可确定出熱點對象(表或索引)

select owner,segment_name,segment_type

from dba_extents

where file_id = &file_id

and &block_id between block_id and block_id + &blocks - 1;

==================================================

<2> db file sequential read DB 檔案順序讀取 (表連接配接順序不佳-----調整代碼,特别是表連接配接)

這一事件通常顯示單個塊的讀取(如索引讀取)。這種等待的數目很多時，可能顯示表的連接配接順序不佳，或者不加選擇地進行索引。對于大量事務處理、調整良好的系統，這一數值大多是很正常的，但在某些情況下，它可能暗示着系統中存在問題。你應當将這一等待統計量與Statspack 報告中的已知問題（如效率較低的SQL）聯系起來。檢查索引掃描，以保證每個掃描都是必要的，并檢查多表連接配接的連接配接順序。DB_CACHE_SIZE 也是這些等待出現頻率的決定因素。有問題的散列區域（Hash-area）連接配接應當出現在PGA 記憶體中，但它們也會消耗大量記憶體，進而在順序讀取時導緻大量等待。它們也可能以直接路徑讀／寫等待的形式出現。

===================================================

該類事件的p1text=file#,p1是file_id，p2是block_id,通過dba_extents即可确定出熱點對象(表或索引)

select owner,segment_name,segment_type

from dba_extents

where file_id = &file_id

and &block_id between block_id and block_id + &blocks - 1;

==================================================

<3> free buffer waits 釋放緩沖區等待 (增大DB_CACHE_SIZE,加速檢查點,調整代碼)

這種等待表明系統正在等待記憶體中的緩沖，因為記憶體中已經沒有可用的緩沖空間了。如果所有SQL 都得到了調優，這種等待可能表示你需要增大DB_BUFFER_CACHE。釋放緩沖區等待也可能表示不加選擇的SQL 導緻資料溢出了帶有索引塊的緩沖存儲器，沒有為等待系統處理的特定語句留有緩沖區。這種情況通常表示正在執行相當多數量的DML（插入／更新／删除），并且資料庫書寫器(DBWR)寫的速度不夠快，緩沖存儲器可能充滿了相同緩沖器的多個版本，進而導緻效率非常低。為了解決這個問題，可能需要考慮增加檢查點、利用更多的DBWR 程序，或者增加實體磁盤的數量。

<4> buffer busy waits 緩沖區忙等待 (BUFFER熱塊)

這是為了等待一個以非共享方式使用的緩沖區，或者正在被讀入緩沖存儲器的緩沖區。緩沖區忙等待不應大于1%。檢查緩沖等待統計部分（或V$WAITSTAT）:

A、如果等待處于字段頭部，應增加自由清單（freelist）的組數，或者增加pctused到pctfree之間的距離。

B、如果等待處于回退段（undo）頭部塊，可以通過增加復原段(rollback segment)來解決緩沖區的問題；

C、如果等待處于回退段（undo）非頭部塊上，就需要降低驅動一緻讀取的表中的資料密度,或者增大DB_CACHE_SIZE；

D、如果等待處于資料塊，可以将資料移到另一資料塊以避開這個"熱"資料塊、增加表中的自由清單或使用LMT表空間；

E、如果等待處于索引塊，應該重建索引、分割索引或使用反向鍵索引。

為了防止與資料塊相關的緩沖忙等待，也可以使用較小的塊：在這種情況下，單個塊中的記錄就較少，是以這個塊就不是那麼"繁忙"。在執行DML(插入/更新 /删除)時,Oracle DBWR就向塊中寫入資訊，包括所有對塊狀态"感興趣"的使用者(感興趣的事務表，ITL)。為減少這一區域的等待，可以增加initrans，這樣會在塊中建立空間，進而使你能夠使用多個ITL槽。你也可以增加該塊所在表中的pctfree(當根據指定的initrans 建立的槽數量不足時，這樣可以使ITL 資訊數量達到maxtrans 指定的數量）。

<6> enqueue

enqueue 是一種保護共享資源的鎖定機制。該鎖定機制保護共享資源，如記錄中的資料，以避免兩個人在同一時間更新同一資料。enqueue 包括一個排隊機制，即FIFO(先進先出)排隊機制。注意：Oracle 的latch 機制不是FIFO。Enqueue 等待通常指的是ST enqueue、HW enqueue、TX4 enqueue 和TM enqueue。

A、ST enqueue 用于空間管理和字典管理的表空間的配置設定。利用LMT，或者試圖對區域進行預配置設定，或者至少使下一個區域大于有問題的字典管理的表空間。

B、HW enqueue 與段的高水位标記一起使用；手動配置設定區域可以避免這一等待。

C、TX4 enqueue是最常見的enqueue 等待，通常是以下三個問題之一産生的結果:

第一個問題是唯一索引中的重複索引，需要執行送出（commit）/復原（rollback）操作來釋放enqueue。第二個問題是對同一位圖索引段的多次更新。因為單個位圖段可能包含多個行位址(rowid),是以當多個使用者試圖更新同一段時,你需要執行送出或復原操作,以釋放enqueue。

第三個問題，也是最可能發生的問題是多個使用者同時更新同一個塊。如果沒有自由的ITL槽，就會發生塊級鎖定。通過增大initrans 和/或maxtrans以允許使用多個ITL槽，或者增大表上的pctfree 值，就可以很輕松地避免這種情況。

D、TM enqueue 在DML 期間産生，以避免對受影響的對象使用DDL。如果有外來關鍵字，一定要對它們進行索引，以避免這種常見的鎖定問題。

<7> log buffer space 日志緩沖空間 (寫REDO慢-----增大log_buffer,redo log file放到快速磁盤上)

當日志緩沖(log buffer)寫入重做日志(redo log)的速度比LGWR 的寫入速度慢，或者是當日志轉換(log switch)太慢時，就會發生這種等待。為解決這個問題，可以增大日志檔案的大小，或者增加日志緩沖器的大小，或者使用寫入速度更快的磁盤。甚至可以考慮使用固态磁盤，因為它們的速度很高。

<8> log file switch 日志檔案轉換 (歸檔慢-----增加或者擴大重做日志)

有兩種情況：

A、log file switch (archiving needed)

當日志切換的時候由于日志組循環使用了一圈但日志歸檔還沒有完成，通常是io有嚴重問題，可增大日志檔案和增加日志組，調整log_archive_max_processes

B、log file switch (checkpoint incomplete)

當日志切換的時候由于日志組循環使用了一圈但将被使用的日志組中的checkpoint還沒有完成造成，通常是io有嚴重問題，可增大日志檔案和增加日志組

<9> log file sync 日志檔案同步 (送出太頻繁----批量送出)

當使用者commit的時候通知lgwr寫日志但lwgr正忙，造成的可能原因是commit太頻繁或者lgwr一次寫日志時間太長（可能是因為一次log io size 太大），可調整 _log_io_size，結合log_buffer,使得 (_log_io_size*db_block_size)*n = log_buffer,這樣可避免和增大log_buffer引起沖突;放置日志檔案于高速磁盤上

<10> library cache pin

該事件通常是發生在先有會話在運作PL/SQL,VIEW,TYPES等object時,又有另外的會話執行重新編譯這些object,即先給對象加上了一個共享鎖,然後又給它加排它鎖,這樣在加排它鎖的會話上就會出現這個等待。P1,P2可與x$kglpn和x$kglob表相關

X$KGLOB (Kernel Generic Library Cache Manager Object)

X$KGLPN (Kernel Generic Library Cache Manager Object Pins)

-- 查詢X$KGLOB,可找到相關的object,其SQL語句如下

(即把V$SESSION_WAIT中的P1raw與X$KGLOB中的KGLHDADR相關連)

select kglnaown,kglnaobj from X$KGLOB

where KGLHDADR =(select p1raw from v$session_wait

where event='library cache pin')

-- 查出引起該等待事件的阻塞者的sid

select sid from x$kglpn , v$session

where KGLPNHDL in

(select p1raw from v$session_wait

where wait_time=0 and event like 'library cache pin%')

and KGLPNMOD <> 0

and v$session.saddr=x$kglpn.kglpnuse

-- 查出阻塞者正執行的SQL語句

select sid,sql_text

from v$session, v$sqlarea

where v$session.sql_address=v$sqlarea.address

and sid=<阻塞者的sid>

這樣,就可找到"library cache pin"等待的根源，進而解決由此引起的性能問題。

<11> library cache lock

該事件通常是由于執行多個DDL操作導緻的,即在library cache object上添加一個排它鎖後,

中查找其對應的對象。

-- 查詢引起該等待事件的阻塞者的sid、會話使用者、鎖住的對象

select b.sid,a.user_name,a.kglnaobj

from x$kgllk a , v$session b

where a.kgllkhdl in

(select p1raw from v$session_wait

where wait_time=0 and event = 'library cache lock')

and a.kgllkmod <> 0

and b.saddr=a.kgllkuse

當然也可以直接從v$locked_objects中檢視，但沒有上面語句直覺根據sid可以到v$process中查出pid，然後将其kill或者其它處理。

<12> latch free (等待LATCH FREE)

latch 是一種低級排隊機制(它們被準确地稱為互相排斥機制),用于保護系統全局區域(SGA)中共享記憶體結構。latch 就像是一種快速地被擷取和釋放的記憶體鎖。latch 用于防止共享記憶體結構被多個使用者同時通路。如果latch 不可用，就會記錄latch 釋放失敗。大多數latch 問題都與以下操作相關：不能使用邦定變量（庫緩存latch）、重複生成問題（重複配置設定latch）、緩沖存儲器競争問題（緩沖器存儲LRU 鍊），以及緩沖存儲器中的"熱"塊（緩沖存儲器鍊）。也有一些latch 等待與bug（程式錯誤）有關，如果懷疑是這種情況，可以檢查MetaLink 上的bug 報告。

該事件的熱點對象可通過以下語句查找，其中&2值是v$session_wait中的P1RAW，x$bh中的字段Hladdr表示該block buffer在哪個cache buffer chain latch 上，可以通過v$latch_children定位哪些segment是熱點塊。

===================================================

select a.hladdr, a.file#, a.dbablk, a.tch, a.obj, b.object_name

from x$bh a, dba_objects b

where (a.obj = b.object_id or a.obj = b.data_object_id)

and a.hladdr = &2

union

select hladdr, file#, dbablk, tch, obj, null

from x$bh

where obj in

(select obj from x$bh

where hladdr = &2

minus

select object_id from dba_objects

minus

select data_object_id from dba_objects)

and hladdr = &2

order by 4;

====================================================

***Latch 問題及可能解決辦法

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

* Library Cache and Shared Pool (未綁定變量---綁定變量,調整shared_pool_size)

每當執行SQL或PL/SQL存儲過程,包,函數和觸發器時,這個Latch即被用到.Parse操作中此

又從另一個會話給它添加一個排它鎖,這樣在第二個會話就會生成等待。可通過到基表x$kgllkLatch也會被頻繁使用.

* Redo Copy (增大_LOG_SIMULTANEOUS_COPIES參數)

重做拷貝Latch用來從PGA向重做日志緩沖區拷貝重做記錄.

* Redo Allocation (最小化REDO生成,避免不必要送出)

此Latch用來配置設定重做日志緩沖區中的空間,可以用NOLOGGING來減緩競争.

* Row Cache Objects (增大共享池)

資料字典競争.過度parsing.

* Cache Buffers Chains (_DB_BLOCK_HASH_BUCKETS應增大或設為質數)

"過熱"資料塊造成了記憶體緩沖鍊Latch競争.

* Cache Buffers Lru Chain (調整SQL，設定DB_BLOCK_LRU_LATCHES,或使用多個緩沖區池)

掃描全部記憶體緩沖區塊的LRU(最近最少使用)鍊時要用到記憶體緩沖區LRU鍊Latch.太小記憶體緩沖區、過大的記憶體緩沖區吞吐量、過多的記憶體中進行的排序操作、DBWR速度跟不上工作負載等會引起此Latch競争。

<13> db file parallel write

與DBWR程序相關的等待,一般代表了I/O能力出現了問題. 通常與配置的多個DBWR程序或者DBWU的I/O slaves個數有關.當然也可能意味着裝置上存在着I/O競争

<14> db file single write

表示在檢查點發生時與檔案頭寫操作相關的等待.通常與檢查點同步資料檔案頭時檔案号的紊亂有關.

<15> direct path read 和 direct path write

表示與直接I/O讀相關的等待.當直接讀資料到PGA記憶體時,direct path read 出現.這種類型的讀請求典型地作為:排序IO(為排序不能在記憶體中完成的時候),并行Slave查詢或者預先讀請求等. 通常這種等待與I/O能力或者I/O競争有關.

<16> free buffer inspected

表示在将資料讀入資料調整緩存區的時候等待程序找到足夠大的内在空間通常這類等待表示資料調整緩存區偏小.

<17> library cache load lock

表示在将對象裝載到庫高速緩存時出現了等待.這種事件通常代表着發生了負荷爾蒙很重的語句重載或者裝載,可能由于SQL語句沒有共享或者共享池區域編小造成的.

<18> log file parallel write

表示等待LGWR向作業系統請求I/O開始直到完成IO.在觸發LGWR寫的情況下如3秒、1/3、1MB、DBWR寫之前可能發生.這種事件發生通常表示日志檔案發生了I/O競争或者檔案所在的驅動器較慢

<19> log file single write

表示寫日志檔案頭塊時出現了等待.一般都是發生在檢查點發生時.

<20> transaction

表示發生了一個阻塞復原操作的等待

<21> undo segment extension

表示在等待復原段的動态擴充.這表示可能事務量過大,同時也意味着可能復原段的寝大小不是最優,MINEXTENTS設定得偏小.考慮減少事務,或者使用最小區數更大的復原段.

注明：整理自網絡