Re: open and closed cursors
Date: Tue, 12 Feb 2013 15:35:10 -0700
Message-ID: <511AC39E.6020204_at_gmail.com>
On 12/02/2013 2:16 PM, Orlando L wrote:
> All,
> I am trying to find the definition of open vs closed (session_cached)
> cursors. I understand that if the cursor was open it would be in the
> library cache. If it was closed, like when we close using the 'close
> <cursor_name>', I thought the cursor would be flushed out of memory, but
> apparently that is not the case. Even after the cursor is closed it can be
> accessed from memory, the session_cached_cursor gives information about
> closed cursors. what is the difference between an open and closed cursor.
>
> Orlando.
In general, Oracle uses 'lazy' techniques whenever possible.
What is the value to the current session to flush the buffer when you close the cursor?
If, however, you mark the cursor as closed, then a session looking for free space knows it can free that specific area. And a session that happens to have the same cursor could 'reinstate' that closed cursor fairly easily, if there is no space pressure, significantly reducing overhead in parsing and potentially latch waits.
This concept happens fairly often in various areas - for example, a commit does not 'update' row level locks, but rather simply updates the transaction header. This is just as good and much faster than forcing the transaction-owning session to wait while the locks are cleared . The next session that wants to see the row can then clear the lock as part of doing something useful (such as reading the block anyway ...)
So from my memory, and not having a test bed handy to verify this, I would think the basic answer internally is that Oracle took the lazy approach here. I'm sure others will be able to confirm, or correct, the above.
/Hans
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Feb 12 2013 - 23:35:10 CET