Re: open_cursor problems

In article <92094h$ml6$1_at_nnrp1.deja.com>,   David Fitzjarrell <oratune_at_aol.com> wrote:

> PL/SQL implicitly declares a cursor for all SQL data manipulation
> statements, including queries that return only one row. For queries
> that return more than one row, you can explicitly declare a cursor to
> process the rows individually.
>
> A cursor is a handle to a specific private SQL area. Although most
> Oracle users rely on the automatic cursor handling of the Oracle
> utilities, the programmatic interfaces offer application designers
 more
> control over cursors. In application development, a cursor is a named
> resource available to a program, which can be specifically used for
> parsing SQL statements embedded within the application.
>
> There is no absolute limit to the total number of cursors one session
> can have open at one time, subject to two constraints:
>
> Each cursor requires virtual memory, so a session's total number of
> cursors is limited by the memory available to that process.
>
> A system-wide limit of cursors per session is set by the
 initialization
> parameter named OPEN_CURSORS found in the parameter file (such as
> INIT.ORA).
>
> Explicitly creating cursors for precompiler programs can offer some
> advantages in tuning those applications. For example, increasing the
> number of cursors can often reduce the frequency of parsing and
 improve
> performance. If you know how many cursors may be required at a given
> time, then you can make sure you can open that many simultaneously.
>
> After each stage of execution, the cursor retains enough information
> about the SQL statement to re-execute the statement without starting
> over, as long as no other SQL statement has been associated with that
> cursor. Note that the statement can be re-executed without including
> the parse stage.
>
> By opening several cursors, the parsed representation of several SQL
> statements can be saved. Repeated execution of the same SQL statements
> can thus begin at the describe, define, bind, or execute step, saving
> the repeated cost of opening cursors and parsing.
>
> Closing a cursor means that the information currently in the
 associated
> private area is lost and its memory is deallocated. Once a cursor is
> opened, it is not closed until one of the following events occurs:
>
> The user program terminates its connection to the server.
>
> If the user program is an OCI program or precompiler application, then
> it explicitly closes any open cursor during the execution of that
> program. (However, when this program terminates, any cursors remaining
> open are implicitly closed.)
>
> My advice? Increase the open_cursors parameter.
>
> --
> David Fitzjarrell
> Oracle Certified DBA
>

To David:

Great post.

In addition, consider stored procedures. What is the lifetime of a stored procedure? It is not associated with any given session, but is associated with the lifetime of the database.

If you created a simple stored procedure that declared and openned a cursor and exited, what effect on number of cursors does it have? Each call would creates a new cursor. Ending the program that made the call will not destroy the cursor. These cursors will accumulate.

I know as I had a coding error, in that it was possible to exit a procedure without closing the cursor. The 26th time someone called the procedure, a TOO MANY OPEN CURSOR error was encountered. ( the limit was 25 )

Similar problems can arise from using ref_cursors as one procedure creates the cursor which is passed on to another procedure/program. The consequence is that the cursors will accumulate unless one actively destroys the cursors.

--
Michael Krolewski
Rosetta Inpharmatics
mkrolewski_at_rii.com
              Usual disclaimers


Sent via Deja.com
http://www.deja.com/