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.
- SQL>SelectEvent #,Name, Parameter1, parameter2, parameter3FromV $ event_nameWhere Name='Enq: HW-contention';
- EVENT #NAMEPARAMETER1 PARAMETER2 PARAMETER3
- --------------------------------------------------------------------------------------------------------------
- 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:
- SQL>SelectP1, p2, p3FromV $ session_waitWhereEvent ='Enq: HW-contention';
- P1 P2 P3
- ------------------------------
- 1213661190 7 140003563
- 1213661190 7 140003563
- 1213661190 7 140003563
- 1213661190 7 140003563
- 1213661190 7 140003563
- 1213661190 7 140003563
- 1213661190 7 140003563
- 7RowsSelected
7 rows selected
Through the DBMS_UTILITY conversion through P3, you can know the files and blocks in which contention occurs:
- SQL>SelectDbms_utility.data_block_address_block (140003563), dbms_utility.data_block_address_file (140003563)FromDual;
- DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (140003563) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (140003563)
- -----------------------------------------------------------------------------------------------
- 1591531 33
Then, the object is located through file # And block:
- SQL>SelectOwner, segment_type, segment_name
- 2FromDba_extents
- 3WhereFile_id = 33
- 4And1591531BetweenBlock_idAndBlock_id + blocks-1;