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

From: jo_holvoet@xxxxxxxx
To: jonathan@xxxxxxxxxxxxxxxxxx
Date: Mon, 10 Jan 2005 10:03:31 +0100
I'm jumping in a bit late, but last year I filed a tar due to an enqueue 
hash chains latch problem (related to a deadlock).
But you didn't mention a deadlock so this probably has nothing at all to 
do with your issue.

From the description (8.1.7.4.1 on Win2k BTW):

---
---
User runs a program to load data into the DB. At a certain point, this 
causes 
an ORA-00060 (deadlock). So far no real problem, but a few minutes later 
(while 
deadlock cleanup is still going on), every other session in the DB hangs 
until 
deadlock cleanup is completed. 10046 traces of the hanging processes show 
that 
they are all waiting on 'latch free'; trace files contain almost nothing 
but 
lines of the type :
...
WAIT #6: nam='latch free' ela= 207 p1=635234948 p2=13 p3=136
WAIT #6: nam='latch free' ela= 206 p1=635234948 p2=13 p3=137
WAIT #6: nam='latch free' ela= 206 p1=635234948 p2=13 p3=138
WAIT #6: nam='latch free' ela= 207 p1=635234948 p2=13 p3=139
WAIT #6: nam='latch free' ela= 206 p1=635234948 p2=13 p3=140
...
The latch with number p2=13 is the 'enqueue hash chains' latch.
---
---

And Oracle's answer :

---
---
The problem you see is when the ora-60 error will generate an systemstate 
dump automatically.
Then we need to take enqueue locks of the structure and this is causing 
your problems.

This behaviour can be changed by setting event 10027 in your init.ora 
file, 

Put this is your init.ora file. 
---
---

mvg/regards

Jo






"Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
01/09/2005 23:40
Please respond to jonathan

 
        To:     <oracle-l@xxxxxxxxxxxxx>
        cc: 
        Subject:        Re: what's mean of "enqueue hash chains" latch?


 
Note also how:
    enqueue gets and releases = 23M
    db block gets = 23 M
    execute count = 23M

You can't help feeling that that's not a 
complete coincidence.  I think your trigger
theory sounds quite good at the moment,
only 
    a)    I'd like to find that 23M executions in 
            v$sql.  Why isn't it appearing in statspack ?
    b)    There's no redo generated to match the
            volume of locking.

I'd like to see the snapshot of x$ksqst
One thought - for parallel DML, maybe
there are lots of DX locks being taken
and dropped.

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






----- Original Message ----- 
From: "Riyaj Shamsudeen" <rshamsud@xxxxxxxxxxxx>
To: <oracle.unix@xxxxxxxxx>; <jonathan@xxxxxxxxxxxxxxxxxx>
Cc: <oracle-l@xxxxxxxxxxxxx>
Sent: Sunday, January 09, 2005 9:09 PM
Subject: 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



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



--
//www.freelists.org/webpage/oracle-l
Re: what's mean of "enqueue hash chains" latch?

Other related posts:
