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

Source: Internet
Author: User

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';
  2. EVENT #NAMEPARAMETER1 PARAMETER2 PARAMETER3
  3. --------------------------------------------------------------------------------------------------------------
  4. 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';
  2. P1 P2 P3
  3. ------------------------------
  4. 1213661190 7 140003563
  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. 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;
  2. DBMS_UTILITY.DATA_BLOCK_ADDRESS_BLOCK (140003563) DBMS_UTILITY.DATA_BLOCK_ADDRESS_FILE (140003563)
  3. -----------------------------------------------------------------------------------------------
  4. 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;

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.