Wait event on the segment -- enq: HW-contention

The HW lock is provided to prevent multiple processes from simultaneously modifying HWM. The process to move HWM must obtain the HW lock. If a contention occurs during the acquisition of the HW lock, the system waits for the enq: HW-contention event. HW lock contention is mostly caused by a large number of insert operations.

As we all know, the Oracle high water level line marks that all blocks below this line are in Oracle format. In other words, all blocks below this line are used by Oracle. Generally, when the insert operation is executed, Oracle will push forward the high waterline when the blocks below the high waterline are insufficient. Furthermore, when multiple processes perform the insert operation at the same time, it is easy to cause high water level contention, mainly manifested as enq: HW-contention.

1. SQL>SelectEvent #,Name, Parameter1, parameter2, parameter3FromV $ event_nameWhere Name='Enq: HW-contention';
3. EVENT #NAMEPARAMETER1 PARAMETER2 PARAMETER3
4. --------------------------------------------------------------------------------------------------------------
5. 250 enq: HW-contentionName| ModeTable Space# Block

How to find the event: 'enq: HW-contention 'hotspot object:

Check v $ session_wait. The following wait events should be displayed:

1. SQL>SelectP1, p2, p3FromV $ session_waitWhereEvent ='Enq: HW-contention';
3. P1 P2 P3
4. ------------------------------
5. 1213661190 7 140003563
6. 1213661190 7 140003563
7. 1213661190 7 140003563
8. 1213661190 7 140003563
9. 1213661190 7 140003563
10. 1213661190 7 140003563
11. 1213661190 7 140003563
13. 7RowsSelected

7 rows selected

Through the DBMS_UTILITY conversion through P3, you can know the files and blocks in which contention occurs:

1. SQL>SelectDbms_utility.data_block_address_block (140003563), dbms_utility.data_block_address_file (140003563)FromDual;
3. DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (140003563) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (140003563)
4. -----------------------------------------------------------------------------------------------
5. 1591531 33

Then, the object is located through file # And block:

1. SQL>SelectOwner, segment_type, segment_name
2. 2FromDba_extents
3. 3WhereFile_id = 33
4. 4And1591531BetweenBlock_idAndBlock_id + blocks-1;