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 :
http://www.eygle.com/tools/sp_150_151.log
And I've dump systemstate the trace file is :
http://www.eygle.com/tools/qcb_ora_21123.trc.gz
And the output of V$LATCH_CHILD is :
http://www.eygle.com/tools/latch_children.xls
We have close parallel insert for temporary.
So, statspack and systemstate is the last stuff we have at that situation.
thanks.
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
>
> 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
>
--
----------------------------------------
eygle,a beginer of Oracle from China.
my site: http://www.eygle.com
--
//www.freelists.org/webpage/oracle-l
Other related posts:
