Re: open_cursor problems

In our last gripping episode "CandyMan" <eric_kimble_at_hotmail.com> wrote:
> Hello,
> I have an issue with open_cursors and have read as much Metalink
> documentation that I can handle and am looking for a way to tell which
> cursors are still open, and if that is even possible. I have created
 two
> scripts from the Metalink information that tell me conflicting
 information:
>
> select user_name, count(*) num
> from v$open_cursor
> group by user_name;
>
> and the other is
>
> select sum(a.value) as Count, b.name, c.user_name
> from v$sesstat a, v$statname b, v$open_cursor c
> where a.statistic#=b.statistic# and
> a.sid = c.sid and
> name = 'opened cursors current'
> group by c.user_name, b.name;
>
> but neither of these shows me that we are over the cursor limit. I
 have the
> value set to 200 and the most I ever get is about 160. My developers
 are
> starting to hate the fact that I cannot answer their question about
 when
> Oracle releases the cursor. Any specific scripts that you have that
 might
> give me the information would be appreciated and any advice other than
> increase OPEN_CURSOR would be great.
>
> Eric Kimble
>
>

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


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