Re: How to tune LIBRARY CACHE latch contention

From: Dusan Bolek <spambin_at_seznam.cz>
Date: 10 Feb 2006 06:45:55 -0800
Message-ID: <1139582754.953750.216650@g44g2000cwa.googlegroups.com>

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

Received on Fri Feb 10 2006 - 08:45:55 CST