Re: OPEN_CURSORS parameter

There are two types of cursors, implicit and explicit. Explicit cursors are explicitly stated with the PL/SQL "CURSOR IS select statement" command. Implicit cursors are (as indicated) never explicitly defined. Implicit cursors are created wheneven an SQL command returns multiple rows. So if I say "SELECT * FROM table2;" and table2 has more than one row, then an implicit cursor is created until the command is done processing. Then the cursor is dropped.

Obviously, having more cursors open will require more space (read memory) to hold the contents of the query. Anytime a cursor is opened (implicitly or explicitly), the query results are held in memory until the cursor is closed. Most cursors don't take up that much memory. But once in a while, a query can return tons of information. It all depends on your environment.

As an additional note, I've changed MAX cursors to 500 without seeing any ill effects on the system.

HTH,
Brian

Dipen Kotecha wrote:
>
> I am trying to tune our database (7.3.4) and have been told that
> (amongst other things) increasing the parameter 'open_cursors' should
> help. Before I do so I would like to be sure that I understand what I am
> doing and whether it is the right thing to do.
>
> I understand that this parameter represents the max. number of cursors
> that a session can have open, but is this the same type of cursor as a
> PL/SQL cursor? Also if a session has more cursors open will it use more
> CPU, memory, i/o as a result?
>
> Currently it is set to 50 and I am thinking of changing it to 100. Any
> advice/information is greatly appreciated.
>
> Thanks in advance,
>
> Dipen
Received on Fri Sep 10 1999 - 08:25:10 CDT