Re: Library Cache Latch statistics from StatsPack

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 11 Feb 2004 16:16:15 -0000
Message-ID: <007201c3f0ba$5ff215f0$6702a8c0@Primary>

You haven't given a time-period for the snapshot, so we don't have a clue about whether the problem is causing real hardship.

However, your comment about 'executions are high' matches the statistics.

If you have a cursor held open (x$kgllk - lock mode = null), and want to execute it, you have to create a pin (x$kglpn - lock mode = share, I think).

If you are doing extreme amounts of very short executions, than I guess you will be busy pinning and unpinning - and that's the general hint we might get from looking at the locations where the laching is going on.

Do you have a small number of very large packages which have very popular procedures - is there a package with a handful of very popular procedures that keeps getting hit ? Or perhaps a couple of SQL statements that are executed an extreme number of times ?

And, as Mark says, you could be seeing a problem that is being exaggerated by a bug.

You get some idea of the benefit of the session_cached_cursors by checking a couple of stats in v$sesstat . The exact names escape me, but they are something like:

session cursors cached
session cursor cache hits.

I think caching would just consume CPU at the client end, though, rather than cause latching directly. (Though if the client is running on the server, the extra CPU usage might exacerbate a latching problem).

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: Wednesday, February 11, 2004 3:44 PM Subject: RE: Library Cache Latch statistics from StatsPack

Thanks Mark. I know I haven't put much information in my email. I am hoping that someone can explain which of the "Where"s for the Library Cache Latch should I worry about and *why* [ie , what does "kgllkdl: child: cleanup" or "kgllkdl: child: free pin" mean !!]

I do have SESSION_CACHED_CURSORS -- and I think it is too high at 400. {progressively increased from 0 to 100 to 400 over the past year}.

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 Wed Feb 11 2004 - 10:16:15 CST