Re: HV enqueue
- From: Sai Selvaganesan <ssaisundar@xxxxxxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Sat, 10 Feb 2007 09:44:46 -0800 (PST)
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@xxxxxxxxxxxxxxxxxx> 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
>
--
//www.freelists.org/webpage/oracle-l
Other related posts:
