Re: session cursor cache

All documents I find about session cursor cache say the same words. Some summarize the points better than others. But none answer Brian's question on how to find the cursors in that cache. There's no indicator in any v$sql% view that says a particular SQL is, in addition to being in library cache, also in session cursor cache. OEM won't do magic; if no v$ or x$ view has it, OEM won't have it either. v$sql_cursor may be able to reveal session specific SQLs, i.e. those in UGA, unlike other v$sql% views, which show SQLs in library cache. But I've had no success in using it.

I think I find something close. According to the text posted in 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' creates a file with lines like

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

That hex number is x$kgllk.kgllkadr. When you find the row with that address, you see the SQL under kglnaobj column. Isn't that easy?

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 can also be used to set this event but I haven't got it to work.

(Setting events generally should be done after Oracle's approval.)

Yong Huang Received on Wed Oct 19 2005 - 23:28:24 CDT