Re: HV enqueue

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sat, 10 Feb 2007 10:50:17 -0000
Message-ID: <030301c74d01$40b49130$0200a8c0@Primary>

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_at_sbcglobal.net>
> 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

Received on Sat Feb 10 2007 - 04:50:17 CST