enq: HW - contention

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.  

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/31397003/viewspace-2149622/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/31397003/viewspace-2149622/