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

Eygle

        Looking at the excel spreadsheet, one of the child latch has 2.4 Billion gets and other child latches have less then 76K latch gets. So, sessions are contending to lock one table. Looking at the statspack report, you have numerous enqueue gets and releases too.

        Is there any foreign key constraint on this table ? If yes, do you have any index on that foreign key column ? Further, what mode locks are being acquired ?

        do you have any triggers on this table, by any chance ?         

Thanks
Riyaj "Re-yas" Shamsudeen
Certified Oracle DBA

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of eygle Sent: Sunday, January 09, 2005 5:59 AM
To: jonathan_at_jlcomp.demon.co.uk
Cc: oracle-l_at_freelists.org
Subject: Re: what's mean of "enqueue hash chains" latch?

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_at_jlcomp.demon.co.uk> 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
>
> --
> http://www.freelists.org/webpage/oracle-l
>

-- 
----------------------------------------
eygle,a beginer of Oracle  from China.
my site: http://www.eygle.com
--
http://www.freelists.org/webpage/oracle-l



-- Attached file included as plaintext by Ecartis --
-- Desc: Signature

The information transmitted is intended only for the person or entity to
which it is addressed and may contain confidential and/or privileged
material.  If the reader of this message is not the intended recipient,
you are hereby notified that your access is unauthorized, and any review,
dissemination, distribution or copying of this message including any
attachments is strictly prohibited.   If you are not the intended
recipient, please contact the sender and delete the material from any
computer.


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