Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune LIBRARY CACHE latch contention
"Dusan Bolek" <spambin_at_seznam.cz> wrote in message
news:1138700872.514206.191680_at_g47g2000cwa.googlegroups.com...
> So finally it reappeared again, this time it has an impact on a
> different statement. I have used this query:
>
> select * from v$latch where latch# 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')
> )
> )
>
The child_latch in v$sql is the child# in v$latch_children for the library cache latch.
I would join v$sql to v$session by hash_value (and address) as it allows an efficient access path.
You only need to find the child_latch to find out if that latch is under pressure. But you might do that by taking snapshots of v$latch_children on the library cache latch.
Things to check if you have the latch_child: statements
on that child with:
dozens (or more) of versions
a very large number of executes - extreme compared to the rest of the
system
a large number of parse calls - compared to the rest of the system
-- 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 2006Received on Wed Feb 01 2006 - 08:49:05 CST