Session Cursor Cache and session_cached_cursors

Session cursor cache is a cache owned by a session and used by straight SQL 
(i.e. non-PL/SQL). If the same SQL or cursor is submitted from the session for 
the third time, this SQL is saved in session cursor cache if configured. To 
configure it, simply set session_cached_cursors to a non-zero number. This page 
http://ixora.com.au/scripts/library.htm#session_cursor_cache and the short 
description in the script give some useful advice on when you have enough 
session_cached_cursors. Since scanning this cache is strictly linear, it may 
harm performance if the size is set to an arbitrarily large number. I'm not 
aware of anybody's systematic study of performance variation with its size, 
although it's simple to do.

> How do we know session cursor cache is used?

If 'session cursor cache hits' statistic keeps going up, that cache is used. If 
it goes up in a particular session (through v$sesstat), that session is using 
it. If 'session cursor cache count' goes up, then new cursors are added to that 
cache. Note that in spite of the name, v$session_cursor_cache is not what we 
need.

> Give a demo to verify a SQL is in session cursor cache.

Set session_cached_cursors to, say, 20 (alter session set 
session_cached_cursors = 20). In another session, check the first session's 
statistics 'session cursor cache hits' and 'session cursor cache count' (select 
name, value from v$statname a, v$sesstat b where a.statistic# = b.statistic# 
and name in ('session cursor cache hits', 'session cursor cache count') and sid 
= &FirstSessionSid). In the first session, run a SQL statement. Check its 
statistics in the second session. Keep doing this with the same SQL in the 
first session. Notice that after the SQL is run for the third time, its 
statistics start to show non-zero numbers. This means that SQL is cached in 
session cursor cache.

> Why is 'parse count (total)' statistic always going up?

That statistic is a misnomer. It includes hard and soft parses, *plus the 
number of times the cursor is found in session cursor cache*!

> How do we find all the SQLs currently saved in a session's session cursor 
> cache?

There's no v$ view or x$ table I know of that says a particular SQL is, in 
addition to being in library cache, also in session cursor cache. They are in a 
session's UGA. The closest is v$sql_cursor, which I believe extracts SQLs from 
UGA rather than library cache as other v$sql% views do. Another way is to dump 
a session's UGA (oradebug dump global_area 4). Unfortunately neither method 
gives me the answer I need.

According to the text posted in Metalink forum thread 636909.995, whether 
session_cached_cursors is set to a non-zero number determines the output of the 
trace file as a result of setting event 10270. I tested it on Oracle 9.2.0.1.0. 
In a session where session_cached_cursors is set to > 0:

alter session set events '10270 trace name context, level 10';
if a SQL is already in session cache, run it >=1 times
if a SQL is not in session cache or you don't know, run it >=3 times
...
alter session set events '10270 trace name context off';

A trace file in user_dump_dest is created with lines like 

#1: child already locked 7a893b8c, checking for validity 

That hex number matches a value under kgllkadr column of x$kgllk. You find the 
row with that address and you see the SQL under kglnaobj column. X$kgllk itself 
does not tell you whether a SQL is in session cursor cache; instead it has all 
open cursors in library cache. It's the line in the 10270 trace file saying 
"child already locked" that unambiguously tells us this cursor is in session 
cursor cache.

In order to find all SQLs in any session's cursor cache, you can loop through 
all suspect sessions and set the event in it using oradebug or dbms_system. 
Make sure you turn it off after a while. Alter system should also work to set 
this event but I haven't got it to work. 

> Using the event 10270 method requires that the session run the same SQL >= 3 
> times. Any way to find the session-cached SQLs while the session is currently 
> idle?

Help us by filing a feature enhancement request.

> Any caution when setting session_cached_cursors to a non-zero value?

Read http://download-west.oracle.com/oowsf2005/003wp.pdf, pp.17-18.