【恩墨學院】經典故障分析 - ASSM引發的索引爭用與 enq HW -contention 等待事件

作者介紹：

孫加鵬 雲和恩墨技術顧問

六年Oracle技術顧問經驗，所服務的行業包括電信運營商、金融業、製造業等，具有很強的經驗。

擅長的故障診斷、高可用架構、升級遷移等。目前主要服務於上海金融類客戶。

1、故障概述

2017年07月24日11:58左右，客戶核心資料庫出現大量活動會話，導致資料庫負載急劇加大，從而導致業務出現延時，DBA透過檢視SESSION資訊發現有大量的“enq: HW - contention”等待事件。

一直持續約有3分鐘，資料庫負載下降：

下面是詳細的故障分析診斷過程，以及詳細的解決方案描述。

2故障分析

1故障現象

ENMODB資料庫出現大量活動會話，資料庫負載急劇加大，透過V$SESSION能看到大量enq：HW contention的活動會話資訊，客戶緊急採集了ASH資訊，方便後期故障分析。

2分析過程

從AWR和ASH兩個維度來分析此故障，先整體後區域性，首先從AWR分析入手。

1、AWR分析

首先看一下故障時間段的AWR報告：

 

半小時的取樣時間，DB Time 215mins，其中等待時間“enq: HW - contention”佔據近36%，為TOP 10 events中最主要的非空閒等待事件。

等待事件“enq: HW - contention”的解釋：

The HW enqueue is used to manage the allocation of space beyond the high water mark of a segment. The high water mark of a segment is the boundary between used and unused space in that segment. If contention is occurring for "enq: HW - contention" it is possible that automatic extension is occuring to allow the extra data to be stored since the High Water Mark has been reached. Frequent allocation of extents,reclaiming chunks,and sometimes poor I/O performance may be causing contention for the LOB segments high water mark.

The HW enqueue is used to serialize the allocation of space beyond the high water mark of a segment. If lots of data is being added to an object concurrently, then multiple processes may be trying to allocate space above the high water mark at the same time, leading to contention.

簡而言之，HW鎖是在分配高水位線以上的空閒空間時，多個程式同時為了分配高水位線上空閒空間而修改HWM，修改HWM需要持有HW鎖，該鎖又屬於排他鎖（mode=6）。

如果大量資料被併發插入某個物件時，那多個程式可能會試圖在高水位線以上同時申請可用空間，大併發的申請HW鎖，從而導致HW enqueue爭用。HW – contention等待事件的P1，P2，P3引數參考下表；

Event	P1 Parameter	P2 Parameter	Parameter 3
enq: HW - contention	name|mode	table space	# block

發現這個時間段確實有大量的INSERT操作，半小時採用中，該SQL執行了約近24w次。

下一步看看HW競爭是在表段還是在索引段上？

大量的物理讀/物理寫請求也都發生在表TAB_ENMO的索引上。這裡可以猜測HW競爭可能不在表上，而在這幾個索引上面，IO讀寫請求非常高。

2、ASH分析

透過客戶採集的ASH分析發現，等待事件“enq: HW - contention”是從07月24日11:57:12秒左右開始的，此類session全部被session id為1191的會話阻塞。

 

檢視1191會話資訊，發現11:57:12的時候沒有被任何會話阻塞（NOT IN WAIT），Session狀態是ON CPU：

這個時間點11:57:12的時候會話1191在執行以下的INSERT操作，這個就是源頭，並且這個SQL一直在執行。

INSERT操作從11:57:11開始，後續該會話一直都是HW競爭，並且跟其他SESSION爭相持有HW鎖。

這個時間點大量的SESSION都在持續申請HW鎖，因此都在相互blocking sessions。從p1，p2，p3引數中發現P3值並不代表爭用塊的RDBA（data block address）（36730這個值太小了，這是為啥？先思考下）。

既然P3找不到RDBA，那就從ash中欄位CURRENT_FILE#和CURRENT_BLOCK#上尋找爭用塊：

 

發現所有的HW競爭都發生在索引IDX_TAB_ENMO_SEQ上，該索引就是表TAB_ENMO上的索引，HW競爭的SQL語句也是上面AWR中發現的SQL。

既然是大併發持有HW鎖，多個程式是持續不斷的申請HW鎖，說明不斷的發現free space不足，一般ASSM管理都是一次性分配多個extent，根據物件大小一個extent下面又會有多個block。除非指定storage引數next size 大小：

表空間ENMO_DATA是ASSM（自動段空間管理），並且是本地管理表空間，獲取表空間的定義語句：

表空間自動擴充套件NEXT SIZE 100M。段管理方式使用自動段空間管理（ASSM）。這裡有個地方值得關注下，這個表空間屬於bigfile tablespace，這就是為什麼透過等待事件中的p1，p2，p3引數無法精確定位到具體發生爭用的block了。

具體可以參考Mos文件：ID 2098543.1。

因此上面的P3引數指的datafile中的block number，其實就是這個索引段的段頭（segment header）所在的block。

所以HW競爭還是發生在索引的段頭上，因為段頭會記錄HWM資訊，程式修改HWM就必須要持有HW鎖，並修改索引段頭上的HWM。所以P3=36730是準確的，只是這個P3引數代表是bigfile tablespace上的block number，dump出file_id=6 block_id=36730的塊，可以看出就是索引IDX_TAB_ENMO_SEQ的Segment header。

現在問題基本明朗了，所有的爭用都發生在索引的Segment Header上面，程式為了需要更多的空間（unformatted），透過持有HW鎖來修改高水位線（HWM），大量的程式併發從而導致HW鎖上的競爭。

那既然是ASSM管理，為何新的extent分配的時候還會出現HWM上的競爭呢？不都是bitmap管理了嗎？比之前freelist管理要好很多啊，看看這個索引的DDL語句：

索引的stroage引數中NETX=1M，即每次分配空間以每次1M大小來分配，8k塊大小即相當於每次分配

128個blocks。難道是客戶建立索引的時候指定extent分配大小？問題是不是發生在這個NEXT 1M上面呢？

顯然不是的，自動段管理表空間（ASSM）下這個NEXT擴充套件字句應該是不生效的，不會按照這樣來初始化extent的。

可以檢查下索引的extent分佈，看看extent下面包含多少個blocks。

上面資訊可以看出索引的extent並不是只有128個塊，跟ASSM的extent分配機制匹配的，segment後期會按照64M的大小分配extent，即每個extent有64*1024/8=8192個block。

7月24日故障之後幾天，又不間斷的出過2~3次同樣的故障，那為何不間斷的會發生這種故障？索引真的有這麼需要unformatted空間嗎？表上有大量的INSERT操作的同時也需要維護索引，同時索引也會進行分裂，不論是leaf node split還是branch node split，都需要新塊來滿足分裂，實驗證明索引分裂只請求unformatted的塊，未滿塊或空閒塊都不會使用。下面來看看索引上unformatted 塊的使用情況，這個show_space儲存過程來自TOM大師的分享：

同時12點12分左右又出現一次HW競爭嚴重的情況，導致AAS飆高，系統負載急劇升高。因此每次出現HW競爭都是因為Unformatted Blocks不夠用的時候，多個程式修改索引段頭的HWM的時候持有HW鎖。

所以問題原因主要是多個程式同時修改索引段頭上的HWM而導致的爭用，針對這種問題一般採用HASH分割槽索引，透過將索引改造成HASH分割槽索引來緩解索引段頭的爭用，這樣從原來的在單個段頭修改HWM，到現在的在多個分割槽索引的段頭上修改HWM。將原先索引從一個L3點陣圖管理塊，到多個L3層點陣圖管理塊。

先看一下ASSM的extent三層點陣圖管理結構：

整個點陣圖三級點陣圖結構是一個樹形結構，L3往往代表的就是Segment Header，L3中記錄了所包含的所有L2點陣圖塊的地址，L2點陣圖塊中又包含了所屬L1點陣圖塊地址。L1點陣圖塊中記錄了具體資料塊的地址和使用情況。

L3，L2，L1三層結構均以樹形結構，一對多的關係。

下面我們來dump出索引的段頭（Segment Header）資訊。

索引目前已經有2323個extents，現在的高水位線在extent_id 2322 block_id 8192位置。”L2 Hint for inserts”這表名INSERT插入的記錄從這個L2（後面跟的是bigfile的block_id）開始。

Dump出這個L2點陣圖塊：

 

如上，這個L2中包含有1007個L1，free L1有77個，L1共有三個狀態值，分別為Free 1，Free 3和Free 5，3和5都代表該L1下面有空塊。

ASSM管理表空間的Table Block的狀態有7種，分別是：

0 = unformatted

1 = logically full (per pctfree)

2 = 0-25% free

3 = 25-50% free

4 = 50%-75% free

5= 75-100% free

對於Block的DUMP有興趣的可以研究研究。

4故障解決

問題原因主要是多個程式同時修改索引段頭上的HWM而導致的爭用，針對這種問題一般採用HASH分割槽索引，透過將索引改造成HASH分割槽索引來緩解索引段頭的爭用，這樣從原來的在單個段頭修改HWM，到現在的在多個分割槽索引的段頭上修改HWM。

引入思考，當初設計表的時候，從業務角度出發，知道這是一個業務流水錶，流水錶的特點就是大量的DML操作，特別是INSERT操作的存在，表級別做了分割槽設計，索引上未考慮到位採用了普通索引，導致後期效能下降和故障發生。因此好的資料庫結構設計是有多麼重要。

恩墨學院隸屬於雲和恩墨（北京）資訊科技有限公司，致力於提供專業高水準的與大資料培訓服務，挖掘培養大資料與資料庫人才。恩墨學院提供包括個人實戰技能培訓、個人認證培訓、企業內訓在內的全方位大資料和資料庫技術培訓。ACE級別超強師資，配備專業實驗室，沉浸式學習與訓練，專業實驗室、配備專業助教指導訓練。能迅速融入專家圈子，業內資源豐富，迅速積累職場人脈。課程包括：Oracle DBA實戰班、Oracle 、Oracle OCP考試等。