Re: what's mean of "enqueue hash chains" latch?

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

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