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 1: In spite of the name, v$session_cursor_cache is not what we need. Note 2: In RAC, 'session cursor cache count' is problematic; it can go above the value of session_cached_cursors. Ref: http://www.itpub.net/thread-1339938-1-1.html -------------------------------------------------------------------------------- > 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. Monitoring statistics: col name for a26 select name, value from v$sesstat a, v$statname b where a.statistic#=b.statistic# and sid=&sid and (name like 'session cursor%' or name in ('cursor authentications','parse count (total)','parse count (hard)')) order by 1; After running a new SQL once (the SQL uses no bind variable), we see that hard parse and parse count total increment by 1. Session cursor cache count or hits may go up but that's due to other causes. After running it the 2nd time, cursor authentication and parse count total increment by 1. After running it the 3rd time, only parse count total increments by 1. After running it the 4th time, session cursor cache hits, session cursor cache count and parse count total increment by 1. After running it the 5th or higher time, session cursor cache hits and parse count total increment by 1. -------------------------------------------------------------------------------- > Why is 'parse count (total)' statistic always going up? That statistic includes hard and soft parses, *plus the number of times the cursor is found in session cursor cache*. It still makes sense though, if you think that the parser still has to read your SQL statement to know what it is. -------------------------------------------------------------------------------- > 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. [Note: The event stops working beginning with 10g.] In a session where session_cached_cursors is set to > 0: alter session set events '10270 trace name context forever, 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. [Update 2015-10] In 11gR2 and up, v$open_cursor.cursor_type (from x$kgllk.kgllkctp) will record "SESSION CURSOR CACHED". -------------------------------------------------------------------------------- > 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.