Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Suggestions Needed: Latch free - library cache

Re: Suggestions Needed: Latch free - library cache

From: Jonathan Lewis <>
Date: Thu, 08 Jan 2004 11:54:50 -0800
Message-ID: <>

Note in-line


Jonathan Lewis

  The educated person is not the person
  who can answer the questions, but the
  person who can question the answers -- T. Schick Jr

Next public appearance2:
 March 2004 Hotsos Symposium - Keynote
 March 2004 Charlotte NC - OUG Tutorial
 April 2004 Iceland

One-day tutorials:

Three-day seminar:
see ____UK___February

The Co-operative Oracle Users' FAQ

An interesting point there - I think we tend to include the optimisation phase in the concept of parsing; but perhaps there ought to be a breakdown in the statistics so we actually see a statistic called something like:

    "plans generated"
so that the number of optimisation events stands out from the hard parses. (I suspect a hard parse is probably synonymous with an optimize, but I'm not sure of that).

In terms of costing, then, I think we only need five or six statistics:

  1. search for text
  2. check objects
  3. check permissions
  4. generate plan
  5. use cached cursor
  6. use held cursor
  7. ???

(I'm trying to break it down into the major cost areas - obviously a 'check objects' cost would vary with the number of objects in the query, so any very fine detail wouldn't really add value).

> To the OP: Other people point out common reasons for library cache latch
> contention. A less common reason is extensive use of public synonyms. If
> the reason, you also see row cache objects latch contention.

I'm not sure that's right. If everyone uses a public synonym, then you get one sql text, and one cursor. I think the contention appears because everyone has to have a 'non-existent' reference in memory to say that they don't own an object with the same name as the public synonym - consequently if you have lots of users who have to check long chains of 'non-existent' then the latches get held for longer periods of time.

Please see the official ORACLE-L FAQ:
Author: Jonathan Lewis

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message
to: (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).
Received on Thu Jan 08 2004 - 13:54:50 CST

Original text of this message