Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune LIBRARY CACHE latch contention
You can't get library cache latch contention from freelists.
However, the affected statement could be suffering because of the high version counts on OTHER statements.
If you have a statement with a high version count, sharing the library cache hash bucket of the critical statement, then any attempt to execute statements in that bucket will result in a relatively long latch hold time as Oracle attempts to identify the specific child cursor that is required. This could result in other sessions expending a lot of CPU spinning as they tried to acquire the same latch.
Can you pin down why the other statements have high version counts and do something about it. (One possibility is having lots of long varchar2() columns, and bind variable mismatches due to significant differences in sizes of incoming values).
-- Regards Jonathan Lewis http://www.oracle.com/technology/community/oracle_ace/ace1.html#lewis The Co-operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html Cost Based Oracle: Fundamentals http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html "Dusan Bolek" <spambin_at_seznam.cz> wrote in message news:1139582754.953750.216650_at_g44g2000cwa.googlegroups.com...Received on Fri Feb 17 2006 - 14:39:48 CST
> So it reappeared again. The first problem is one insert and we think it
> is caused by not enough freelists, because it is a massively inserted
> table with auto space management. Another problems are causing two
> queries, that are accessed via synonyms, which could be the reason for
> our problems too.
> The version count seems OK, well there are pretty high version counts,
> but not on the problematic SQLs.
> The outcome of proposed query (not very effectively written, but
> working):
>
> select child#, gets, misses, sleeps from v$latch_children where name =
> 'library cache' and child# in (
> select child_latch from v$sql
> where address in (select sql_address from v$session
> where sid in (select sid from v$session_wait
> where event = 'latch free')
> )
> )
> /
>
> is:
>
> CHILD# GETS MISSES SLEEPS
> ---------- ---------- ---------- ----------
> 3 183365095 852475 165140
>
> which doesn't look so bad. Only one sleep for every thousand of gets.
>
> --
> Dusan Bolek
>