Re: what's mean of "enqueue hash chains" latch?
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Sun, 9 Jan 2005 09:19:41 -0000
Following up on your wait stats as well as the
stuff you sent earlier:
It is very odd that you seem to have so many locks
requested and released (which is what the dml lock
allocation and enqueue hash chain latches are about.
Check (at the session level preferably) the statistics
with names like 'enqueue%'. You may find that you
are trying to acquire just a few enqueues but are
very unsuccessful in the attempt to acquire the
necessary memory structures. As a follow-up check
(if the numbers are high) I have loaded my 'snap_enqueues'
script onto my web site - this lets you take snapshots
of v$enqueue_stats (9i) or x$ksqst (8i) so that you
can check which type of enqueue is showing most
activity. You can then do a few repeated queries
for that type of enqueue against V$lock to see if
you get any clues.
You can check v$latch_children across the period
to see if there are multiple child latches on your system
for the two reported latches - and see if the activity
is isolated on one of them. (I think I would expect
multiple latches on the hash chain, but just one on
the dml lock allocation).
The waits show 160 seconds of latch wait time in
15 minutes of processing, so the waiting is not the
big problem - the big problem (or at least symptom)
has to be CPU. The CPU could, of course, be disappearing
on the latch spins, but the parallel activity might actually
be responsible for most of it.
It is interesting to note that you have recorded
3,200 seconds of send blocked in 15 minutes,
with only 1,500 blocks - the average wait time
is 2.05 seconds: which means that (almost all of )
these waits are parallel slaves being blocked
by a single process (which might be the query
co-ordinator, or a single PX slave at the next
level up).
As a reference point - it is always useful to
know how many partitions, and how many
indexes (and their partition counts if they are
not local).
(The script is loaded under the 'monitoring'
section)
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Dec 23rd 2004
--
//www.freelists.org/webpage/oracle-l
Other related posts:
