Re: v$open_cursor and session_cached_cursors

crappy-

session_cached_cursors is not intended to be normally used. That is, it serves as a sort of stop-gap when an application cannot be modified to maintain full control over cursors. It is not implemented by the pl/sql engine. It is a cache of recently used cursors for each session, and is constructed from the UGA. The UGA is, in a dedicated server environment, part of the PGA. It is specific to each session.

The idea is that if your application closes cursors, either implicitly or explicitly, you can set a non-zero value for session_cached_cursors and after a few executions a cursor will be placed in that cache and will not be closed. When you use the feature, cursors are not immediately placed in the cache. That is, you have to execute them two or three times - I forgot the exact number - before they become eligible to be cached. If the cursor is not shareable, it will be executed only once and won't be cached.

Every time you go to parse a sql statment, your code looks through its cursor cache to see if that statement is present in the cache. The cache is an array and has to be scanned in a linear fashion. So, a big cursor cache can cost you more than it might save you. Most shops I have encountered who find session_cached_cursors helpful have a setting of around 50 or less. Never more than 100 in any case.

If you don't have the cache enabled, or have it set to zero, you don't necessarly hard parse each cursor. The normal operation is to derive the hash value from a sql statement, use that value to look up the cursor in the library cache (soft parse) and lock it / execute it if it is found, otherwise construct a new cursor (hard parse). Soft parsing will happen if a cursor is closed but otherwise still in the library cache in a useable form. So, shareable sql will see lots of hard parses but probably few or no hard parses, except for perhaps reloads when older cursors get flushed out because of memory demands.

The best approach is to modify your application to keep cursors open that will be used again. That way you avoid even the soft parses. Soft parses, while much cheaper than hard parses, can still get expensive.

v$open_cursor shows cursors for each session. I am not sure what you are seeing there. Regardless of the cursor cache setting, if the cursor is open by a session it will show up in the view. Moreover, you may have the cursor open by a session other than your own and it will still be in the library cache, although the v$open_cursor view will indicate the session id of that session.

• ricky

crappy wrote:
>
> all;
>
> i've read a number of postings, documentation and such on the subject,
> but something is still ocnfusing me. please forgive if this is a dumb
> question.
>
> oracle 8.1.7: my understanding is that one is supposed to use
> session_cached_cursors to control the number of cursors that get
> cached by the plsql engine. however, it doesn't seem to be working
> the way i'm expecting it to. if you open/fetch/close a cursor, with
> session_cached_cursors set to 0, shouldn't the cursor *not* show up in
> v$open_cursor (resulting in a hard + soft parse for each execution)?
> i'm trying to differentiate which sql_text's in v$open_cursor
> represent cached cursors as opposed to cursors that are actually being
> left open by the (java) application, and i thought that i would be
> able to essentially turn off the cursor caching. what am i missing?
> thanks in advance ..
Received on Mon Oct 15 2001 - 20:44:25 CDT