SQL> select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event';
Enter value for event: enq: HW - contention
old   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like '&event'
new   1: select name, parameter1, parameter2, parameter3 from v$event_name where name like 'enq: HW - contention'

NAME                      PARAMETER1      PARAMETER2      PARAMETER3
------------------------- --------------- --------------- ---------------
enq: HW - contention      name|mode       table space #   block

SQL>

現象：
1.效能下降，大量等待"enq HW - contention"。

2. ASH Reports顯示等待事件"enq HW - contention"，其中包含帶有LOB物件的SQL。

原因：
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
用於管理超出分段高水位的空間分配。段的高水位標記是該段中已使用和未使用的空間之間的邊界。如果"enq：HW - contention"發生爭用，則可能會發生自動擴充套件，以允許在達到高位標記後儲存額外的資料。頻繁分配盤區，回收塊和有時較差的I / O效能可能會導致爭用LOB段高水位

解決：
1.確定正確的檔案和塊號
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(&P3) FILE#,
       DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(&P3) BLOCK#
from dual;

For bigfile tablespaces, do not use DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE, or you will get wrong results.
Reference: https://docs.oracle.com/cd/E18283_01/appdev.112/e16760/d_util.htm#i1002531
In such a case, just use the tablespace# and assume p3 is the block number (there is no relative file number).

2.確定該塊所屬的物件
select owner, segment_type, segment_name
from dba_extents
where file_id = &file_id
   and &block_id between block_id and block_id + blocks - 1
   and tablespace_name =
       (select name from ts$ where ts# = < parameter2.tablespace# >);

此外，如果鎖競爭被觀察到，我們可以使用下面的查詢來找出底層的段:
select DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE(ID2) FILE#,
       DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK(ID2) BLOCK#
from v$lock
where type = 'HW';

由於"enq HW-contention"可能由多種不同的原因引起，所以還有幾種可能的不同的解決方案來減輕或減少爭用。

要檢查的事情有：

1.確保您的吊球部分不經常延伸。
2.檢查I / O效能。
3.包含非常繁忙的lob段的表可能需要以將多個分割槽上的併發DML均勻分佈的方式進行分割槽。
4.頻繁的吊點空間/塊回收也會導致"enq HW - contention"

在第4點的情況下，可以採用一些選擇來提供臨時解決方案或解決問題的方法

a.為LOB段手動新增額外的空間可以透過向lob段分配更多的空閒空間來緩解問題，而不需要進行塊回收，直到空閒空間再次用完
ALTER TABLE <lob_table> MODIFY LOB (<column_name>) (allocate extent (size <extent size>));

b.使用shrink space命令或dbms_redefinition過程（用於SECUREFILE LOBS）可以用來釋放任何可回收的空間。
ALTER TABLE test_lob MODIFY LOB (image) (SHRINK SPACE);

c. When using Automatic Segment Space Management (ASSM), and the fix for Bug 6376915 has been applied in your database (Included in 10.2.0.4 +) it is possible to adjust the number of chunks that are cleaned up
when the chunk cleanup operation is required.使用自動段空間管理（ASSM）時，資料庫中已經應用了Bug 6376915的修復程式（包含在10.2.0.4中），可以調整清理的塊的數量
當需要清理塊操作時。

This can be enabled by setting event 44951 to a value between 1 and 1024 (default is 1). With the value between 1 and 1024 setting the number of chunks to be cleaned up each time a chunk reclaimation operation occurs. This can therefore reduce the number of requests for the High Watermark Enqueue.這可以透過將事件44951設定為1和1024之間的值（預設值為1）來啟用。用1到1024之間的值設定每次發生塊回收操作時要清理的塊的數量。這可以因此減少對高水印排隊請求的數量

EVENT="44951 TRACE NAME CONTEXT FOREVER, LEVEL < 1 - 1024 >"

~OR~

SQL> Alter system set events '44951 TRACE NAME CONTEXT FOREVER, LEVEL 1024';

Refer to NOTE 6376915.8 "Bug 6376915 HW enqueue contention for ASSM LOB segments"

With Manual Segment Space Management, this value cannot be altered and is fixed at 128.

enq: HW - contention

相關文章