Anthony as usual is right, but since I already wrote this once for
someone else I thought it might be useful to append it here.
(How many cursors do you 'really' have open):
When an application explicitly does a CLOSE, PL/SQL marks the cursor as
closed but does not close it on ORACLE side. This is to reuse the cursor
on re-execution of the statement. It's not aged out of memory. It will
be reused. A session opens cursors and closes them, but they remain
cached. If another cursor is needed, oracle doesn't check to see if one
is already open and cached, it simply opens a new one. This is done
until the limit specified by open_cursors is reached. When that limit is
reached, an identical query that requires a cursor will check for one
that is open and cached, and will reuse it.
The server caches cursors opened by the PL/SQL engine. Each time a
cursor is closed, it is really moved to a 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. Note that no optimization occurs. In other words,
the cursor caching code does not check to see if a particular query
already has a cursor open; it simply creates another one. This is done
until OPEN_CURSORS is reached. If you have OPEN_CURSORS cached as open,
however, then if an identical query occurs, the server reuses the cached
cursor.
For performance reasons, pl/sql cursors are cached for future reuse, so
once you use them up they will be added to LRU list of cursor table.
Hence you continue to see them in v$open_cursor, although they have been
closed. But once you reach max_open_cursor limit ,these "used up"
cursor in LRU list will close and a new requested cursor will be opened.
Use the following query to find the 'really' open cursors.
declare
me table_types.tvc20;
begin
get_current_appversion(me);
dbms_output.put_line(me(1));
end;
/
select * from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and a.sid = &v_sid;
-----Original Message-----
From: Anthony Molinaro
[mailto:oracledba-ezmlmshield-x85755161.[Email address protected]
Sent: Thursday, June 02, 2005 8:42 AM
To: LazyDBA Discussion
Subject: RE: Closing Cursors
Rahul,
It's an optimization to keep them open, for possible reuse
(oracle does not do this by accident).
In anycase, in older versions of oracle, there
was an hidden param called _close_cached_open_cursors (I think)
and if set to true in your session, open cursors
would be closed after you commit (even implicit selects).
I don't know if this still holds true in 9i,
but was true in 8i and 8, never tested on 7.
You can easily test by checking open cursors current from
v$mystat in your session run some sql then commit then
check open cursors again.
If it doesn't change, then the cursors closing behavior
isn't effected by this hidden param (or maybe it's for
Explicit cursors now).
Might also be worth checking v$sysstat not only vb$mystat.
Good luck,
Anthony
-----Original Message-----
From: Rahul Mehta
[mailto:oracledba-ezmlmshield-x6149836.[Email address protected]
Sent: Thursday, June 02, 2005 8:08 AM
To: LazyDBA Discussion
Subject: Closing Cursors
Can implicit cursors opened by SELECT,INSERT etc on Oracle 9.2.0.1 be
closed without closing the connection to database .These cursors remain
open even after the Stored procedure from which the SELECT,INSERT,UPDATE
and othe DML statements are executed.
Does this require tuning the database.
Please reply at the earliest.
Its Urgent.
Regards
RAHUL
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
--------
website: http://www.LazyDBA.com
Please don't reply to RTFM questions
Oracle documentation is here: http://tahiti.oracle.com
To unsubscribe: see http://www.lazydba.com/unsubscribe.html
To subscribe: see http://www.lazydba.com
By using this list you agree to these
terms:http://www.lazydba.com/legal.html
Oracle LazyDBA home page