RE: Suggestions Needed: Latch free

From: Bobak, Mark <Mark.Bobak_at_il.proquest.com>
Date: Thu, 08 Jan 2004 09:19:25 -0800
Message-ID: <F001.005DC28E.20040108091925@fatcity.com>

Jonathan,

Second session, same user: first is soft w/o authentication. Second is session cursor cache hit.
Second session, different user: first is soft w/ authentication. Second is session cursor cache hit.

So, once everything is cached, the same user case is as expected, and the different user case does even better than you predicted.

-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 11:50 AM To: Multiple recipients of list ORACLE-L

Excellent !!

I've been demonstrating in the past using v$latch that the latch costs of parsing are different on the first, second, and third parse - and I've assumed that that's why the cursor goes into the cache on the third parse. I've never thought that the 'cursor authentication' statistic might be relevant.

If you go to the other session

Where the user is the same - do you see a session cache cursor hit on the second execution, or does it still not appear until the fourth Rationale - maybe the cursor is put into the cache on the first hit after full authentication.
Where the user is different - do you see a session cache cursor hit on the THIRD execution, or does it still not appear until the fourth. Rationale - the first execution generates the in-memory permissions; the second execution finds the cursor authenticated, therefore causes a cache load.

The manual says the cursor is cached on the third execution - but maybe that's the "obvious" result from the simplest test.

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 4:34 PM

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

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

Received on Thu Jan 08 2004 - 11:19:25 CST

RE: Suggestions Needed: Latch free - library cache