Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: How to tune LIBRARY CACHE latch contention

Re: How to tune LIBRARY CACHE latch contention

From: <yong321_at_yahoo.com>
Date: 1 Feb 2006 11:25:45 -0800
Message-ID: <1138821945.285410.320630@z14g2000cwz.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US