Re: Suggestions Needed: Latch free - library cache

Note in-line

Regards

Jonathan Lewis
http://www.jlcomp.demon.co.uk

  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:
http://www.jlcomp.demon.co.uk/tutorial.html

Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html ____UK___February

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html

• Original Message -----
  > Other than the four parse invocations in your message, I think we can add
  one
  > between your first and second: Invoke a parse to create a new version of
  the
  > same cursor (same in the sense of same address and hash) due to either
  bind
  > threshold change or execution plan change.
  

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
that's
> 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: http://www.orafaq.net
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (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).