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: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Wed, 1 Feb 2006 14:49:05 +0000 (UTC)
Message-ID: <drqhp1$ptu$1@nwrdmz03.dmz.ncs.ea.ibs-infra.bt.com>

"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 2006
Received on Wed Feb 01 2006 - 08:49:05 CST

Original text of this message

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