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

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Sun, 9 Jan 2005 22:40:11 -0000
Message-ID: <002401c4f69c$3169e0d0$6702a8c0@Primary>

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

I'd like to find that 23M executions in v$sql. Why isn't it appearing in statspack ?
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_at_jcpenney.com> To: <oracle.unix_at_gmail.com>; <jonathan_at_jlcomp.demon.co.uk> Cc: <oracle-l_at_freelists.org> 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

--

http://www.freelists.org/webpage/oracle-l Received on Sun Jan 09 2005 - 16:35:34 CST