Skip to content

Oracle Max number of open cursors – complete reference for the new oracle dba




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
VN:F [1.9.22_1171]
Rating: 0.0/10 (0 votes cast)
VN:F [1.9.22_1171]
Rating: 0 (from 0 votes)

Post a Comment

You must be logged in to post a comment.