Re: HV enqueue


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.


Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html



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

hi
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
 thx
 sai



--
http://www.freelists.org/webpage/oracle-l

Other related posts: