Re: HV enqueue

  • From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
  • To: <oracle-l@xxxxxxxxxxxxx>
  • Date: Sat, 10 Feb 2007 10:50:17 -0000

According to v$lock_type, the HV enqueue is a:
   "Lock used to broker the high water mark during parallel inserts"

At a guess, this means that Oracle is sharing a single extent
across multiple PX slaves while loading instead of using one
extent per slave.

There seem to be two hidden parameters that might have an impact:

Name        Default value        Description
_ctas_hwm_brokered_loads = TRUE         Enable HWM brokered CTAS
_insert_enable_hwm_brokered = TRUE during parallel inserts high water marks are brokered

There is also a hint HWM_BROKERED which may turn it on,
but I can't see a hint that looks like the opposite.

It's only a problem if the time spent on the HV enqueue
is a significant fraction of the total time to load - bearing
in mind that the total time is (probably) shared fairly evenly
across the PX slaves - so you may want to divide by the
number of slaves before comparing.


Jonathan Lewis

Author: Cost Based Oracle: Fundamentals

The Co-operative Oracle Users' FAQ

Date: Fri, 9 Feb 2007 22:21:15 -0800 (PST)
From: Sai Selvaganesan <ssaisundar@xxxxxxxxxxxxx>
Subject: HV enqueue


i am on a 10gr2 (102.0.2) rac environment and i see enq :HV contention a lot when direct inserts happen (either ctas or insert /*+ append ). The data i am loading is into a LMT of 4m and the amount of data is about 20GB.

has anyone seen this contention? if so what does this mean and where am i really hitting a bottleneck.

 please let me know


Other related posts: