| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Suggestions Needed: Latch free - library cache
Ok, I did a little experiment. Here are my results:
In session A, I do:
I did 'select sid from v$mystat where rownum=1;' I did 'alter session set session_cached_cursors=100;' I did 'alter system flush shared_pool;'
In session B, I ran the following:
select my.statistic#, sn.name, my.value from v$sesstat my, v$statname sn
where sn.statistic#=my.statistic#
and sn.statistic# in(179,180,191,193)
and my.sid=62;
Which yielded the baseline stats:
STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
179 parse count (total) 60
180 parse count (hard) 9
191 session cursor cache hits 6
193 cursor authentications 6
Now, session A:
Select /* this is my unique sql */ * from dual;
Session B shows:
STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
179 parse count (total) 62
180 parse count (hard) 10
191 session cursor cache hits 6
193 cursor authentications 6
Two more total parses, one hard. (The extra soft parse due to recursive sql?)
Now, session A:
/
(Re-execute query)
Session B:
STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
179 parse count (total) 63
180 parse count (hard) 10
191 session cursor cache hits 6
193 cursor authentications 7
Hmm...no hard parse, soft parse and 'cursor authentication'.
Session A:
/
(execute a third time)
Session B:
STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
179 parse count (total) 64
180 parse count (hard) 10
191 session cursor cache hits 6
193 cursor authentications 7
Hmm...soft parse, NO cursor authentication. This is just the third exec, so no session cursor cache hit, but we should be in the session cursor cache now.
Session A:
/
(Fourth execution)
Session B:
STATISTIC# NAME VALUE
---------- -------------------------------------------------- ----------
179 parse count (total) 65
180 parse count (hard) 10
191 session cursor cache hits 7
193 cursor authentications 7
There's our session cursor cache hit!
So, it seems that this is happening:
1.) Hard parse. 2.) Soft parse w/ 'cursor authentication'. 3.) Soft parse w/o 'cursor authentication'. 4.) Soft parse w/ session cursor cache hit.All subsequent executions are same as #4.
Two more quick tests:
I tried connecting another session, as the same user, and there was no
'cursor authentication'. So, I tried ano connection as a *different*
user, and, voila!, cursor authentication was done on the first
execution!
So, I'll think about this a bit more, but I think one can distinguish between all the cases you mentioned. What do you think, Jonathan?
-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 10:10 AM
To: Multiple recipients of list ORACLE-L
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
> 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-LReceived on Thu Jan 08 2004 - 10:34:26 CST
(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). -- 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).
 |
 |