Re: open and closed cursors
- From: Hans Forbrich <fuzzy.graybeard@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Tue, 12 Feb 2013 15:35:10 -0700
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
