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

  • From: eygle <oracle.unix@xxxxxxxxx>
  • To: jonathan@xxxxxxxxxxxxxxxxxx
  • Date: Sun, 9 Jan 2005 19:58:56 +0800

thanks Jonathan;

I've upload the full statspack report to my site,you can get it from :

And I've dump systemstate the trace file is :

And the output of V$LATCH_CHILD is :

We have close parallel insert for  temporary.
So, statspack and systemstate is the last  stuff we have at that situation.


On Sun, 9 Jan 2005 09:19:41 -0000, Jonathan Lewis
<jonathan@xxxxxxxxxxxxxxxxxx> wrote:
> 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
> The Co-operative Oracle Users' FAQ
> Public Appearances - schedule updated Dec 23rd 2004
> --
> //

eygle,a beginer of Oracle  from China.
my site:

Other related posts: