Re: How to tune LIBRARY CACHE latch contention

What is the version count on that specific statement ? If there are lot of copies - for whatever reason - the competition for the latch it is using could become severe.

Which version of Oracle is this - if it's 9+, have you checked v$sql.child_latch to see if you have a skewed distribution on the amount of latch activity on the latch covering that statement -- excessive execution counts, loads, invalidations or parse calls ?

Have you checked v$latch for sleeps that correspond to that latch, and v$latch_children to see if the problem is focused on a single child latch, and v$latch_misses to see if there is one particular function where most of the problem occurs.

-- 
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/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 10th Jan 2006

"Dusan Bolek" <spambin_at_seznam.cz> wrote in message 
news:1138207345.821463.244810_at_z14g2000cwz.googlegroups.com...


> Hello,

>

> the problem is that in waves we're encountering quite a lot of sessions

> waiting on latch wait event. The problematic latch is the library cache

> latch and our sufferer is a simple SQL statement.

>

> The facts are:

>

> 1) everything is happening in a wave pattern, each minute about twenty

> seconds of waiting (60+ sessions on latch wait) and forty seconds of

> working

>

> 2) all queries are using BIND variables, the particular has only one

> expression in WHERE clause and this expression is properly BINDed

>

> 3) during the last two hours, we had 2631562 parses (3425 hard parses)

> and 8034543 executions of SQL statements happened

>

> 4) the problematic statement was issued 1.2 times per second

>

> 5) the problem has started five days ago, but we were unable to find

> any change in the system that could cause this

>

> 6) AIX 5.2 64bit, 9.2.0.6, connection pooling, about 100 concurrent DB

> calls

>

> 7) during latch contention our CPUs were not stressed more than usual

> (not typical behaviour while encountering a latch problem)

>

> 8) we're using synonym in this statement, but the same applies also for

> the rest of our statements and they are having no problem even the ones

> that are executed more frequently

>

> I have looked through Metalink, but regarding library cache contention,

> there is no much information. Any ideas?

>

> Thank you

> --

> Dusan Bolek

>