| Closed Cursor query in procedure - listed in v$open_cursor [message #37157] |
Mon, 21 January 2002 19:07  |
Rajee
Messages: 17
Registered: June 2001
|
Junior Member |
|
|
Hi,
I have problem in cursors. After executing the procedure i am getting the query used in the cursor getting listed in v$open_cursor table. But i have closed the cursor in the procedure. I tried with 'FOR <cursor_name> IN <query>' also. SQL_TEXT field V$open_cursor include the cursor query. How to solve this. Even if i give "select 'x' from dual" in the cursor that is getting listed in v$open_cursor. I could n't find out the reason. Please update me regarding this.
Thanks and regards,
rajee
|
|
|
|
| Re: Closed Cursor query in procedure - listed in v$open_cursor [message #37165 is a reply to message #37157] |
Tue, 22 January 2002 00:02  |
pratap kumar tripathy
Messages: 660
Registered: January 2002
|
Senior Member |
|
|
Hi,
Do not worry about it, it is not unusual behaviour.
The cursors listed in V$OPEN_CURSORS are, in fact, still open. This is a performance optimization method used by the server. Even though you have CLOSED the cursors, they are still cached in Server memory.
This is not a bug. The code leaves cursors open and caches them, as intended. The CLOSE_CACHED_OPEN_CURSORS parameter in init.ora forces all of these cached cursors to be closed at the end of each transaction, if that is desired.
The server caches cursors opened by the PL/SQL engine. Each time a cursor is closed, it is really moved to an Least Recently Used (LRU) list of open cursors and left open. This is done as a
performance improvement by saving a 'round_trip' from Client to Server each time a cursor is opened.
Hope it helps!!!
cheers
pratap
|
|
|
|
| Re: Closed Cursor query in procedure - listed in v$open_cursor [message #37932 is a reply to message #37165] |
Thu, 07 March 2002 05:49 |
Gaetano Giunta
Messages: 1
Registered: March 2002
|
Junior Member |
|
|
|
How does this interact with the SESSION_CACHED_CURSORS parameter? In my case the number of cursor closed by PL/SQL stored procedures but kept open by the DB seem to grow without an end, even though SESSION_CACHED_CURSORS is set to 0 (I have PRO*C apps that run a never-ending session). Will eventually cached cursors fill up the SGA and put the db to a crawl?
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
