[20140311]等待事件enq HW - contention

[20140311]等待事件enq HW - contention.txt

生產系統業務高峰時出現enq: HW - contention，一般這個主要是插入記錄非常密集的情況下出現，自己對系統分析看看主要是那些物件
引起的問題。

SQL> @ver

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi

SELECT event,
         sql_id,
         TO_CHAR (sample_time, 'hh24') hours,
         COUNT (*)
    FROM DBA_HIST_ACTIVE_SESS_HISTORY
   WHERE event LIKE 'enq: HW - contention'
GROUP BY event, sql_id, TO_CHAR (sample_time, 'hh24')
ORDER BY COUNT (*) DESC;

EVENT                 SQL_ID        HO     COUNT(*)
--------------------- ------------- -- ------------
enq: HW - contention dhs7skn7kdxdr 11           53
enq: HW - contention dhs7skn7kdxdr 10           39
enq: HW - contention dhs7skn7kdxdr 16           23
enq: HW - contention dhs7skn7kdxdr 17           23
enq: HW - contention dhs7skn7kdxdr 09           12
enq: HW - contention 66mu5zrxm7u61 11            3
enq: HW - contention dhs7skn7kdxdr 15            3
enq: HW - contention dhs7skn7kdxdr 12            3
enq: HW - contention 66mu5zrxm7u61 16            2
enq: HW - contention dhs7skn7kdxdr 08            2
enq: HW - contention 66mu5zrxm7u61 15            1
enq: HW - contention 66mu5zrxm7u61 09            1
enq: HW - contention 66mu5zrxm7u61 08            1

13 rows selected.

--很明顯主要出現在10,11,16,17點的業務高峰，sql_id='dhs7skn7kdxdr'.
--但是奇怪我查詢相關檢視v$sql,dba_hist_sql*都沒有查詢到對應的sql語句。
SQL> select * from v$sql where sql_id in ('dhs7skn7kdxdr','66mu5zrxm7u61');
no rows selected

SQL> select * from v$event_name where name='enq: HW - contention';
EVENT# EVENT_ID NAME PARAMETER1 PARAMETER2 PARAMETER3 WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS
------------ ------------ -------------------- -------------------- -------------------- -------------------- ------------- ------------ --------------------
180 1645217925 enq: HW - contention name|mode table space # block 3290255840 2 Configuration

SELECT event, sql_id, sample_time, p2, p3 FROM DBA_HIST_ACTIVE_SESS_HISTORY
WHERE event LIKE 'enq: HW - contention' AND sql_id = 'dhs7skn7kdxdr' AND ROWNUM <= 1;

EVENT SQL_ID SAMPLE_TIME P2 P3
--------------------- ------------- -------------------------- ------------ ------------
enq: HW - contention dhs7skn7kdxdr 04-MAR-14 11.51.44.145 AM 6 25176955

--看了一些文件才知道引數3是指塊地址RBA而非塊。

select dbms_utility.data_block_address_file(&1) rfile#, dbms_utility.data_block_address_block(&&1) block# from dual;

RFILE# BLOCK#
------------ ------------
6 11131

SQL> select segment_name,file_id,block_id from dba_extents where file_id = 6 and 11131 between block_id and block_id + blocks - 1;
SEGMENT_NAME FILE_ID BLOCK_ID
-------------------------- ------------ ------------
SYS_LOB0000059813C00002$$ 6 11129

SQL> select segment_name,header_file,header_block from dba_segments where segment_name='SYS_LOB0000059813C00002$$';
SEGMENT_NAME HEADER_FILE HEADER_BLOCK
-------------------------- ------------ ------------
SYS_LOB0000059813C00002$$ 6 11131

--很明顯是lob段的段頭在增加分配空間時存在爭用。

--換一個思路查詢，V$open_cursor檢視看看。
SQL> select distinct sql_id,sql_text from V$OPEN_CURSOR where sql_id in ('dhs7skn7kdxdr','66mu5zrxm7u61');
SQL_ID SQL_TEXT
------------- ------------------------------------------------------------
66mu5zrxm7u61 table_e_a_e981_5_0_0
dhs7skn7kdxdr table_e_a_e9a5_2_0_0

SQL> select count(*),KGLNAOBJ,kglobt03 from x$kglcursor where kglobt03 in ('dhs7skn7kdxdr','66mu5zrxm7u61') group by
KGLNAOBJ,kglobt03;
    COUNT(*) KGLNAOBJ                                 KGLOBT03
------------ ---------------------------------------- -------------
           5 table_e_a_e981_5_0_0                     66mu5zrxm7u61
           5 table_e_a_e9a5_2_0_0                     dhs7skn7kdxdr

-- 確實是lob型別的資料。而且在業務高峰插入密集。

SYS@test> @16to10 e981
16 to 10 DEC
------------
59777

SYS@test> @16to10 e9a5
16 to 10 DEC
------------
59813

--使用select * from dba_objects where object_id in (59777,59813);可以確定是那個表。table_e_a_e981_5_0_0 ，5表示欄位的順序。
--僅僅分析其中一個看看。

SELECT * FROM dba_lobs WHERE (OWNER, TABLE_NAME) IN (SELECT owner, object_name FROM dba_objects WHERE object_id = 59813);

SQL> SELECT SEGMENT_NAME,INDEX_NAME FROM dba_lobs WHERE (OWNER, TABLE_NAME) IN (SELECT owner, object_name FROM
dba_objects WHERE object_id = 59813);

SEGMENT_NAME INDEX_NAME
------------------------------ ------------------------------
SYS_LOB0000059813C00002$$ SYS_IL0000059813C00002$$

--64k=>1M->8M->64M.

--解決方法也許只能預先給這個段分配好空間，其他好像也沒有什麼好方法。

alter table .

modify lob() (allocate extent (size 64M));

google 找到如下連線：
http://dbasolved.com/2014/03/05/combat-with-an-enqueue-wait-event-enq-hwcontention/
http://www.oracledatabase12g.com/archives/%E5%88%A9%E7%94%A844951-event%E8%A7%A3%E5%86%B3lob-space-enq-hw-contention%E7%AD%89%E5%BE%85%E4%BA%89%E7%94%A8.html

Opening the SR has lead me to dig a bit and do some research on extent allocation and LOBs while waiting on Oracle's
answer. Prior to Oracle Database 11g (11.2.0.1), there is a known bug (6376915). This bug is related to high watermark
enqueue contention for ASSM LOB segments (Note ID: 6376915.8). This bug was actually fixed in release 11.2.0.1 but it
needs to be "turned on" in later releases. To turn the fix for this bug on in 11.2.0.2+, an event needs to be set in the
spfile.

SQL> ALTER SYSTEM SET EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL <1 .. 1024>" scope=spfile;

By setting this event between 1 and 1024 will identify the number of chunks to be cleaned up each time a reclamation
operation is performed. In turn this reduces the number of requests against the high watermark enqueue.

Once the event has been set in the spfile, the database instance needs to be rebooted to make the change effective.

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