as a new oracle dba you might come across production problems like "max open cursors exceeded" . This article will explain about what the parameter is and what you can do to fix this error. The main thing to remember is the open_cursors parameter is applicable to individual session.
Statement Handles or Cursors A cursor is a handle or name for a private SQL area inwhich a parsed statement and other information for processing the statement are kept.(Oracle Call Interface, OCI, refers to these as statement handles.) Although mostOracle users rely on automatic cursor handling of Oracle utilities, the programmaticinterfaces offer application designers more control over cursors.
The management of private SQL areas is the responsibility of the user process. The
allocation and deallocation of private SQL areas depends largely on which application
tool you are using, although the number of private SQL areas that a user process can
allocate is always limited by the initialization parameter OPEN_CURSORS. The defaultvalue of this parameter is 50.
A private SQL area continues to exist until the corresponding cursor is closed or the
statement handle is freed. Although Oracle frees the runtime area after the statement
completes, the persistent area remains waiting. Application developers close all open
cursors that will not be used again to free the persistent area and to minimize the
amount of memory required for users of the application.
Each user session can open multiple cursors up to the limit set by the initialization
parameter OPEN_CURSORS. However, applications should close unneeded cursors toconserve system memory. If a cursor cannot be opened due to a limit on the number ofcursors, then the database administrator can alter the OPEN_CURSORS initializationparameter.
Some statements (primarily DDL statements) require Oracle to implicitly issue
recursive SQL statements, which also require recursive cursors. For example, a
CREATE TABLE statement causes many updates to various data dictionary tables to
record the new table and columns. Recursive calls are made for those recursive
cursors; one cursor can run several recursive calls. These recursive cursors also use
shared SQL areas.
Scrollable Cursors
Execution of a cursor puts the results of the query into a set of rows called the result
set, which can be fetched sequentially or nonsequentially. Scrollable cursors are
cursors in which fetches and DML operations do not need to be forward sequential
only. Interfaces exist to fetch previously fetched rows, to fetch the nth row in the resultset, and to fetch the nth row from the current position in the result set.
OPEN_CURSORS
Parameter type Integer
Default value 50
Modifiable ALTER SYSTEM
Range of values 0 to 65535
Basic Yes
OPEN_CURSORS specifies the maximum number of open cursors (handles to private
SQL areas) a session can have at once. You can use this parameter to prevent a sessionfrom opening an excessive number of cursors.
It is important to set the value of OPEN_CURSORS high enough to prevent your
application from running out of open cursors. The number will vary from one
application to another.
**you can increase the open_cursors value and it does not have any impact unless some session really wants to open that many number of cursors.In that case anyway you can check whther there is a need to increase the size of the shared pool**.
Assuming that a session does not open the number of cursors
specified by OPEN_CURSORS, there is no added overhead to setting this value higher than actually needed.
OPEN_CURSORS Limit on the maximum number of cursors (active SQL statements) for each session. The setting is application-dependent; 500 is recommended.
To take advantage of additional memory available for shared SQL areas, you might
also need to increase the number of cursors permitted for a session. You can do this by increasing the value of the initialization parameter OPEN_CURSORS.
So summary is that if open_cursors is 300 then every session can have 300 cursors open and you can find the number of cursors currently opened by a session by looking into the v$open_cursor view and providing the sid.
Example
select * from v$open_cursor where sid = 663 and I saw that for this session we currently have 53 open cursor records.
Below statement gives you all relevant info regarding the current open cursors, shared session cursors etc for your database.
select
‘session_cached_cursors’ parameter,
lpad(value, 5) value,
decode(value, 0, ‘ n/a’, to_char(100 * used / value, ‘990’) || ‘%’) usage
from
( select
max(s.value) used
from
v$statname n,
v$sesstat s
where
n.name = ‘session cursor cache count’ and
s.statistic# = n.statistic#
),
( select
value
from
v$parameter
where
name = ‘session_cached_cursors’
)
union all
select
‘open_cursors’,
lpad(value, 5),
to_char(100 * used / value, ‘990’) || ‘%’
from
( select
max(sum(s.value)) used
from
v$statname n,
v$sesstat s
where
n.name in (‘opened cursors current’, ‘session cursor cache count’) and
s.statistic# = n.statistic#
group by
s.sid
),
( select
value
from
v$parameter
where
name = ‘open_cursors’
)
/
SQL> select
2 to_char(100 * sess / calls, ‘999999999990.00’) || ‘%’ cursor_cache_hits,
3 to_char(100 * (calls – sess – hard) / calls, ‘999990.00’) || ‘%’ soft_parses,
4 to_char(100 * hard / calls, ‘999990.00’) || ‘%’ hard_parses
5 from
6 ( select value calls from sys.v_$sysstat where name = ‘parse count (total)’ ),
7 ( select value hard from sys.v_$sysstat where name = ‘parse count (hard)’ ),
8 ( select value sess from sys.v_$sysstat where name = ‘session cursor cache hits’ )
9 /
SET PAGESIZE 10000
col machine for a19
col osuser for a12
col username for a11
compute sum of open_cursors on report
--
SPOOL opencur.$ORACLE_SID
select count(*) from v$open_cursor;
select
a.sid
,machine
,osuser
,username
,count(*) “OPEN_CURSORS”
from
v$open_cursor a
,v$session b
where
a.sid = b.sid
group by
a.sid
,osuser
,username
,machine
having count(*) > 0
order by count(*)
/
select
a.sid
,machine
,osuser
,username
,count(*) “OPEN_CURSORS”
from
v$open_cursor a,
v$session b
where
a.sid = b.sid
and b.type = ‘USER’
group by
a.sid
,osuser
,username
,machine
having count(*) > 0
order by count(*)
/
spool off
