ORA-1000 Maximum number of open cursors exceeded

 
    These are the contents from metalink.

Bookmark Fixed font  Go to End

Doc ID:  Note:1007806.6
Subject:  HOLD_CURSOR AND RELEASE_CURSOR OPTIONS
Type:  PROBLEM
Status:  PUBLISHED
 Content Type:  TEXT/PLAIN
Creation Date:  23-FEB-1995
Last Revision Date:  24-APR-2000
 

What are the Oracle Precompiler options HOLD_CURSOR and RELEASE_CURSOR  
and how do they affect the performance of a PCC program?
 
First of all, these options apply to implicit and explicit cursors in
all precompiled languages except Pro*Ada.  They apply ONLY to implicit
cursors associated with INSERT, UPDATE, DELETE, or a single row SELECT
in Pro*Ada.  An explicit cursor in a program is one explicitly created
with EXEC SQL DECLARE C1 CURSOR FOR...
 
This bulletin discusses what these options do internally and how changing  
them affects program performance.

Solution: Precompiler HOLD_CURSOR and RELEASE_CURSOR Options

 
The HOLD_CURSOR and RELEASE_CURSOR Options
------------------------------------------  
 
What are the Oracle Precompiler options HOLD_CURSOR and RELEASE_CURSOR
and how do they affect the performance of a PCC program?
 
First of all, these options apply to implicit and explicit cursors in
all precompiled languages except Pro*Ada.  They apply ONLY to implicit
cursors associated with INSERT, UPDATE, DELETE, or a single row SELECT
in Pro*Ada.  An explicit cursor in a program is one explicitly created  
with EXEC SQL DECLARE C1 CURSOR FOR...
 
Following will be a discussion of what these options do internally and
how changing them affects program performance.
 
What exactly do we mean by CURSOR?  Unfortunately, we mean two differ-
ent things:
 
    1.  The program cursor - A data structure associated with a SQL
        statement.  
 
        A program cursor is declared for each SQL statement that the
        precompiler finds in your program.  For the statements
 
        EXEC SQL DECLARE SEL_EMP_CURS CURSOR FOR...
        EXEC SQL INSERT...  
 
        PCC will declare two program cursors, say c1 and c2.
 
    2.  The Oracle cursor (also called the context area) - The work
        area created dynamically at run time; this area contains the
        parsed statement, the addresses of the host variables, and
        other information necessary to execute the SQL statement.  
 
These two cursors are linked together via the cursor cache.  The ini-
tial size of the cursor cache is determined by the MAXOPENCURSORS op-
tion.  
 
The following diagram illustrates the relationship described above af-
ter an insert and an update have been executed in your program:
 
                                  CURSOR CACHE
                               +-----------------+  
EXEC SQL INSERT...             |   Cache entry   |            Oracle  
Program cursor P(1)   <---->   |      C(1)       |   <---->   cursor
                               +-----------------+  
EXEC SQL UPDATE...             |   Cache entry   |            Oracle
Program cursor P(2)   <---->   |      C(2)       |   <---->   cursor
                               +-----------------+
    .                          .                 .  
    .                          .                 .
                               +-----------------+
EXEC SQL DELETE...             |   Cache entry   |
Pgm cursor P(MAXOPENCURSORS)   |P(MAXOPENCURSORS)|
                               +-----------------+  
EXEC SQL SELECT...  
Pgm cursor P(MAXOPENCURSORS+1)
 
etc...  
 
 
How are the HOLD_CURSOR and RELEASE_CURSOR options related to this
view?
 
The HOLD_CURSOR option deals with the link between the program cursor
and its cache entry.
 
The RELEASE_CURSOR option deals with the link between the Oracle cur-
sor and the cache entry.
 
For SQL statements that are FREQUENTLY RE-EXECUTED, the bottom line is
this:  if you want to maximize performance, make sure these SQL state-
ments stay "glued" to their respective Oracle cursor.
 
What does it mean when a SQL statement is "glued" to its Oracle cur-
sor?  It means that both links between the SQL statement and its Ora-
cle cursor are made permanent.
 
Why would you want to keep a statement "glued" to its context area?
Because the context area contains the parsed statement and other in-
formation necessary to execute the statement, such as the addresses of
the host variables.  Maintaining access to this information makes sub-
sequent execution of the statement much faster.
 
How do you "glue" a statement to a cache entry?  By correct use of the
HOLD_CURSOR and RELEASE_CURSOR options via the PCC command line or in-
line with EXEC ORACLE OPTION(...).  
 
For instance, with HOLD_CURSOR=YES as the Oracle option, a cache entry
cannot be flagged for reuse.  This has important implications.  If all
cache entries have been used up and a new cache entry is needed for a
new SQL statement such that the number of cache entries would now ex-
ceed the number specified in MAXOPENCURSORS, Oracle will use the first
cache entry marked reuseable.
 
For example, in the above diagram, if the cache entry C(1) is marked
reusable, and the program is about to execute the EXEC SQL SELECT...  
(program cursor P(MAXOPENCURSORS+1),  and the number of cache entries
in use already equals MAXOPENCURSORS, cache entry C(1) and its Oracle  
cursor will now be linked to the select statement.  A subsequent exe-
cution of the insert statement would require pre-empting a cache entry
and its Oracle cursor from another SQL statement and performing a re-
parse.
 
Correspondingly, with the default RELEASE_CURSOR=NO as the Oracle op-
tion, the link between the cache entry and the Oracle cursor (the con-
text area) is maintained after the statement is executed so that the
parsed statement and, more importantly, the allocated memory stay
available.
 
The freeing up of this memory by RELEASE_CURSOR=YES means that the
next statement that gets linked to this cache entry will require an
expensive reallocation of memory in addition to a reparse.  Ugh!  Why
would anybody want RELEASE_CURSOR=YES?  We will see later on.
 
 Program cursor - - - - - [ Cursor cache entry ] - - - - -  Oracle  
for SQL statement                                           cursor  
                    ^                                ^  
            HOLD_CURSOR=YES                   RELEASE_CURSOR=NO
      program cursor is permanently       cache entry maintains the
        linked to its cache entry.       address of its context area.
 
So the HOLD_CURSOR option is intimately tied to the MAXOPENCURSORS op-
tion.  What exactly is the MAXOPENCURSORS option?  First of all, MAX-
OPENCURSORS is a misnomer.  It should more appropriately be called
INITIAL_CURSOR_CACHE_SIZE.  (Okay, so it's a mouthful.)  Anyway, if  
all cursor cache entries are currently marked "not reusable" either  
because of the HOLD_CURSOR option or because the associated statement
is currently being executed (an explicitly opened cursor is still be-
ing fetched on and hasn't been closed yet), then a request for a new
cursor will actually result in the extension of the cursor cache at
runtime (i.e. if MAXOPENCURSORS=10, and all 10 entries are active,
then an 11th will be created).  Just letting the precompiler reuse the
oldest cache entry won't always work, as the following example illus-
trates:  Imagine the case where the user has ten explicitly declared
cursors opened, and wants to execute an eleventh.  If the program ac-
tually reuses the oldest program cursor, the user would lose his cur-
rent position in the first cursor and would not be able to fetch fromThe
freeing up of this memory by RELEASE_CURSOR=YES means that the
next statement that gets linked to this cache entry will require an
expensive reallocation of memory in addition to a reparse.  Ugh!  Why
would anybody want RELEASE_CURSOR=YES?  We will see later on.
 
 Program cursor - - - - - [ Cursor cache entry ] - - - - -  Oracle  
for SQL statement                                           cursor  
                    ^                                ^  
            HOLD_CURSOR=YES                   RELEASE_CURSOR=NO
      program cursor is permanently       cache entry maintains the
        linked to its cache entry.       address of its context area.
 
So the HOLD_CURSOR option is intimately tied to the MAXOPENCURSORS op-
tion.  What exactly is the MAXOPENCURSORS option?  First of all, MAX-
OPENCURSORS is a misnomer.  It should more appropriately be called
INITIAL_CURSOR_CACHE_SIZE.  (Okay, so it's a mouthful.)  Anyway, if  
all cursor cache entries are currently marked "not reusable" either  
because of the HOLD_CURSOR option or because the associated statement
is currently being executed (an explicitly opened cursor is still be-
ing fetched on and hasn't been closed yet), then a request for a new
cursor will actually result in the extension of the cursor cache at
runtime (i.e. if MAXOPENCURSORS=10, and all 10 entries are active,
then an 11th will be created).  Just letting the precompiler reuse the
oldest cache entry won't always work, as the following example illus-
trates:  Imagine the case where the user has ten explicitly declared
cursors opened, and wants to execute an eleventh.  If the program ac-
tually reuses the oldest program cursor, the user would lose his cur-
rent position in the first cursor and would not be able to fetch from
it anymore.      
 
By the way, if an eleventh cache entry is created, when that cursor is
closed the eleventh entry is not removed.  Setting MAXOPENCURSORS low
saves memory, but causes potentially expensive dynamic allocations of
new cache entries if they're needed.  Setting it high assures quick  
execution, but may use more memory than necessary.  
 
What if a statement is not executed repeatedly in a program?  Then you
could go with the other options HOLD_CURSOR=NO and RELEASE_CURSOR=YES.
With the HOLD_CURSOR=NO option, the link between a program cursor and
its cache entry is not permanent.  The cache entry is automatically
marked reusable in case it is needed.  With the RELEASE_CURSOR=YES op-
tion, the Oracle cursor (the context area) is automatically freed and
the parsed statement lost.  A reason you might use this option is if
you are limited by the number of Oracle cursors (MAXOPENCURSORS) at
your site due to memory issues.  You may want to incur the cost of re-
allocating memory and reparsing in order to manage memory more effec-
tively.
 
An advantage of setting RELEASE_CURSOR=YES is that until the link be-
tween the cache entry and the Oracle cursor (context area) is removed,
ORACLE keeps parse locks on any tables referenced in the SQL state-
ment.  These parse locks prevent other users and you from ALTERing or
DROPping the tables (does ORA-0057 sound familiar?).  Also, in Version
5, it will free up the read-consistent image of the referenced tables
stored in ORACLE's Before Image file.
 
What do we mean when we say that RELEASE_CURSOR=YES takes precedence
over HOLD_CURSOR=YES?  With RELEASE_CURSOR=YES, the link between the
Oracle cursor and the cache entry is cut and the Oracle cursor is
freed (closed), so even if your program cursor is permanently linked
to the cache entry because HOLD_CURSOR=YES, you will still have to re-
allocate memory and reparse the statement.  So subsequent executions
of a statement don't benefit from the HOLD_CURSOR=YES option because
RELEASE_CURSOR=YES.
 
For programmers experienced with OCI, here's the OCI equivalent of
what's happening:
 
#define MAXOPENCURSORS 5
 
char     *sql_stmts[10];  
curs_def cursor[MAXOPENCURSORS];
 
oopen(cursor[0],...);
osql3(cursor[0],...,sql_stmts[0],...);  
 
An example of a "cache entry" being linked to another SQL statement
later on in the program is as follows:
 
osql3(cursor[0],...,sql_stmts[5],...);  
 
I am forced to reuse one of my "cache entries" to execute the sixth
SQL statement.
 
An example of a context area being freed is:  
                 
oclose(cursor[0]);      
 
Reusing cursor[0] would require another oopen() and another osql3()--
another dynamic allocation of memory and another reparse.
 
 
Conclusion
----------
 
As a programmer, you will get the most from these options by using
them selectively inline rather than specifying them as options at pre-
compile time.
.

 

--------------------------------------------------------------------------------
 
 Copyright (c) 1995,2000 Oracle Corporation. All Rights Reserved. Legal Notices and Terms of Use.  

Hope this helps
Sameer

Thanks Sameer for pasting the contens here!