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.
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
