RE: Suggestions Needed: Latch free - library cache

Jonathan,

At least the last case you mention is accounted for by the 'session cache cursor hits' statistic. And, though it's not entirely clear to me, based on the description in the Reference manual, it seems to me that the 'cursor authentications' statistic may reflect the second case in your list?

-Mark

Mark J. Bobak
Oracle DBA
ProQuest Company
Ann Arbor, MI
"Imagination was given to man to compensate him for what he is not, and a sense of humor was provided to console him for what he is." --Unknown

-----Original Message-----
Sent: Thursday, January 08, 2004 4:54 AM To: Multiple recipients of list ORACLE-L

There's a failure in the statistics at this point. As far as I can tell, there are at least four different reasons why the stats can report a

    parse count (total)
without recording a

    parse count (hard)
and it would be nice if we could see them as four different statistics.

Code that explicit holds a cursor open need not issue a parse call at all.

Code that issues a parse call may:

    Invoke the whole parse/optimize cycle     Invoke a permissions cycle on an existing statement     Invoke a search and execute cycle on an existing statement with valid
permission

    Invoke a 'this is where it is and I know I've got permission, so just do
it' cycle

The last option can appear when you set up

    session_cached_cursors != 0.

The front-end code is still issuing an explicit parse call, which is why you see the parse count go up, but the work done is kept to near minimum.

NOTE: This description is probably not complete and I'd welcome any corrections and refinements that anyone can supply.

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 ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Thursday, January 08, 2004 1:34 AM

> if your caching the cursors, why does soft parsing still happen?
> ----- Original Message -----
> To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com>
> Sent: Wednesday, January 07, 2004 7:24 PM
>

-- 
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).
-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Bobak, Mark
  INET: Mark.Bobak_at_il.proquest.com

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).