Oracle open cursors ....

Some problems exists regarding max open cursors. The question is its an cursor leak or does my application need so many cursors ?
Parameters:
open_cursors (how many concurrently opened cursors you can have per session)
session_cached_cursors (how many cached CLOSED cursors you can have per session)

Quickfix: increase value for open_cursors; 500-1000 is not too large.

View:
v$open_cursors: Attention, lists also _closed_ (cached) cursors.

to get the actual opened statements/cursors use v$mystat or v$sessstat for all sessions:

-- retrieves stat.val for stat. "opened cursors current"
select a.sid, a.value, b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
order by value desc


in case of a cursor leak, how to get the statement ? with the help of v$open_cursors:


select sql_text, count(*) from v$sqlarea va,
(
select distinct address, hash_value
from v$open_cursor oc
where oc.SID in (
select a.sid
from v$sesstat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3
and value > 280
)) vb
where vb.address=va.ADDRESS and vb.hash_value=va.HASH_VALUE
group by sql_text
order by 2 desc


(but attention, v$open_cursor also lists closed ones)

Just read intersting informations:

from the 9.2.0.5 patchset release notes:

"PL/SQL Cursor Caching

Prior to release of the 9.2.0.5.0 patch set, the maximum number of cursors that
could be cached for fast lookup by PL/SQL was bounded by the value of the
init.ora parameter open_cursors. If you currently have open_cursors set to a
high value (for example, greater than 1000), it is likely that this is causing
large numbers of PL/SQL cursors to be cached in the shared pool. This could lead
to issues with memory management, frequent reloading of library cache objects
and ORA-04031 errors.

Patch set 9.2.0.5.0 alleviates the issue by changing the init.ora parameter
which determines the upper bound for PL/SQL cursor caching from open_cursors to
session_cached_cursors.

Most users will not need to modify the value of either of these parameters. If
you already have session_cached_cursors set to a value greater than the
open_cursors parameter, then this change will have no performance impact upon
your system.

However, if you have session_cached_cursors set to zero, or set at a value
significantly lower than the open_cursors parameter, and you are concerned that
PL/SQL cursors need to be cached for optimal performance, then you should ensure
that the session_cached_cursors parameter is increased appropriately.

This issue is bug number 3150705. "