Re: More Latch Stats : was re Fwd: Re: Library Cache Latch statistics

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 13 Feb 2004 17:04:46 -0000
Message-ID: <011901c3f253$7dd40480$6702a8c0@Primary>

Based on the information you've posted so far, your problem seems to be exclusively with library cache latches, and not row
cache latches or shared pool latches.
Since the load is fairly evenly spread across the library cache latches, I think you may have a number of very popular items being executed extremely frequently.

Check with Oracle about bugs with library cache latching on execution for your version, of course, but otherwise I think I'd go for bumping the _kgl_latch_count up to a much higher value to shorten latch chains in general with the hope that this shortens latch hold times and therefore reduces the number of sleeps. Again, check with Oracle support about bugs with this parameter in your version.

I have to stress that statistics like the ones you've reported don't show up often, and I don't have access to the sort of equipment needed to generate a reproducible test case, so I'm really having to guess here - which I don't like doing, especially when you can only bounce the database once every two months.

One stray thought - you don't have a couple of people running one of those performance tools that does a 'Top 10' SQL statements every 15 seconds - that would be a really good way to kick the library cache latches to death.

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

The educated person is not the person who can answer the questions, but the person who can question the answers -- T. Schick Jr

Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland

One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Original Message ----- From: "Hemant K Chitale" <hkchital_at_singnet.com.sg> To: <oracle-l_at_freelists.org> Sent: Friday, February 13, 2004 3:45 PM Subject: More Latch Stats : was re Fwd: Re: Library Cache Latch statistics

Jonathan,
More information on Latch statistics :
SQL> select child#, gets, misses, sleeps

   2 from v$latch_children
   3 where name = 'library cache'
   4 order by latch#, child#
   5 /

CHILD# GETS MISSES SLEEPS --------------- --------------- --------------- ---------------

               1     423,328,364      16,726,978      41,653,237
               2     277,975,674       8,689,048      20,557,142
               3     363,865,543      18,702,438      45,141,452
               4     337,035,589      17,647,934      43,325,116
               5     286,648,335       7,916,652      19,205,209



----------------------------------------------------------------

Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line.

--
Archives are at http://www.freelists.org/archives/oracle-l/
FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html
-----------------------------------------------------------------

Received on Fri Feb 13 2004 - 11:04:46 CST