Re: HV enqueue

thx for the reply
  the number of slaves it very high(32) for a table creation (table size at the end of the creation is 20GB)   will reducing this amount of parallelsim benefit.   regarding the question about how big a problem this HV contention is, it is the biggest wait event during CTAS.   would you recommend disabling the HWM through underscore parameter or would you see if reducing the parallelsim along with the number of nodes this can run help this or increase the extent size.   now CTAS runs on all nodes(it is a 10 node rac) with this hig a parallelism (i personally feel this is a baad way to do but i would love your recommendation)   again
  thx a lot for your help
  sai

Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk> wrote:   

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
> 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




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