enq: HW - contention 問題的處理

東非奈及利亞某運營商，這是一個06年的系統，伴隨著業務的增長
伺服器負載越來越高，越來越吃力， 客戶提出問題已有半年，
一直沒有完全解決，最近客戶一直在投訴。

現象： 有幾個大表的資料一直錄入不完，發生資料積壓。

 awr 報告

Top 5 Timed Foreground Events

Event Waits Time(s) Avg wait (ms) % DB time Wait Class
enq: HW - contention 136 30,920 227353 54.17 Configuration
buffer exterminate 259,455 5,178 20 9.07 Other
buffer busy waits 4,894 4,763 973 8.34 Concurrency
log file sync 220,273 4,345 20 7.61 Commit
free buffer waits 142,053 3,071 22 5.38 Configuration

 

主要enq: HW - contention等待事件膠及buffer busy 等執點爭用的事件

熱點爭用的物件
主要是系統裡的大表及審計表，這裡我們也用不到審計，直接關掉

Segments by Buffer Busy Waits

    % of Capture shows % of Buffer Busy Waits for each top segment compared
    with total Buffer Busy Waits for all segments captured by the Snapshot

Owner Tablespace Name Object Name Subobject Name Obj. Type Buffer Busy Waits % of Capture
REPORT_PMDB REPORT_DB C_CELL_PS   TABLE 50 53.19
REPORT_PMDB REPORT_PMDB_INDEX C_CELL_LINK_INDEX_9   INDEX 19 20.21
REPORT_PMDB REPORT_DB C_CELL_CS   TABLE 14 14.89
SYS SYSTEM AUD$   TABLE 11 11.70

 

為防止多個程式同時修改HWM而提供的鎖稱為HW鎖。想要移動HWM的程式必須獲得HW鎖。
若在獲取HW鎖過程中發生爭用，則等待enq: HW - contention事件。
HW鎖爭用大部分是大量執行insert所引發的。
眾所周知，Oracle高水位線標誌著該線以下的block均被Oracle格式過，
通俗一點講就是該高水位線以下的block都被Oracle使用過。
通常在執行insert操作時，當高水位線以下block不夠用時，
Oracle將會推進高水位線。更進一步講，
當有多個程式在同時進行insert操作時，比較容易引起高水位線爭用，
主要表現為enq: HW - contention

 

SQL> select event#,name,parameter1,parameter2,parameter3 from v$event_name where name = 'enq: HW - contention'; 
 
    EVENT# NAME                                     PARAMETER1           PARAMETER2           PARAMETER3 

 enq: HW - contention                     name|mode            table space #    
    block  如何找到事件：'enq: HW - contention' 熱點物件：

檢視v$session_wait，應該會有如下等待事件：

SQL> select p1, p2, p3 from v$session_wait where event = 'enq: HW - contention'; 
  
        P1         P2         P3 
---------- ---------- ----------   5.1213661190          7  140003563 
1213661190          7  140003563 
1213661190          7  140003563 
1213661190          7  140003563 
1213661190          7  140003563 
1213661190          7  140003563 
1213661190          7  140003563 
  
7 rows selected  7 rows selected透過P3進行DBMS_UTILITY轉換可以獲知發生爭用的檔案和block：
SQL> select dbms_utility.data_block_address_block(140003563),dbms_utility.data_block_address_file(140003563) from dual; 
 
DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(140003563) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(140003563) 
------------------------------------------------ -----------------------------------------------   5.                                         1591531                                              33 
而透過file#和block#定位物件：                                                                                      
.SQL> select owner, segment_type, segment_name                                                                    
  from dba_extents   3.  3  where file_id = 33  
    and 1591531 between block_id and block_id + blocks - 1;

定位：幾個大表資料量較大，引起高水位擴充套件等待及熱點爭用。

措施1. 修改幾張大表為hash 分割槽表
    2. 關掉審計
   
   
插曲:

 負責運維的兄弟比較保守，一開始在每個表上劃分三個hash 分割槽
 但是效果不太明顯。（備註一般來說建2的次方個分割槽）
 
  於是又增加了13個分割槽達到16個分割槽，是4的n次方。但是反而更慢了。
 開始懷疑方案，一再分的原來增加了分割槽後，全部的index 失效。
 重建index ,速度提高了不少。

到此問題結束。.