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 wrote:
> 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')
> )
> )
As Jonathan pointed out, you need to look at v$latch_children. I would change the first line of your query to
select child#, gets, misses, sleeps from v$latch_children where name = 'library cache' and child# in (
and run it a couple of times during the latch contention period.
Does v$session_wait.p2 always say it's library cache latch during the period (shuold be 157 in 9i according to v$latch.latch#)? You haven't answered Jonathan's question about child cursor version count. You can get it by looking at v$sqlarea.version_count or count(*) from v$sql where hash_value=... Also, show the content of v$sql for the SQLs (only the relevant columns).
Yong Huang Received on Wed Feb 01 2006 - 13:25:45 CST