Re: OPEN_CURSORS param in INIT.ORA

In article <16NOV199415521001_at_author.gsfc.nasa.gov>, JEFF CHAMBLEE (512) <chamblej_at_author.gsfc.nasa.gov> wrote:
>
>
>Hi folks,
>
>I'm getting an ORA-01000 error "maximum open cursors exceeded"
>from within SQL*Forms. I've been told to just go and increase
>OPEN_CURSORS in INIT.ORA. But first I'd like to make know if
>increasing OPEN_CURSORS will take memory (SGA space) away
>from other stuff.
>
>I have a recommendation from Marcel Krato [KRATOM_at_CBR.HHCS.GOV.AU]
>which says always set OPEN_CURSORS to the max (255 for Oracle V6).
>Note I'm on V7.
>
>What are the tradeoffs?
>
>I never saw fit to mess with any INIT.ORA parameters, so they are all
>Oracle defaults. Right now OPEN_CURSORS=50.

<details snipped>

I've never given it much thought, but there are tradeoffs for using a larger OPEN_CURSORS number. In version 7, all SQL statements are kept in the shared pool. If you enable more open cursors, then each user has the capability to hold open more SQL statements in the shared pool. If you have a well-written application, where all statements follow strict typecase and spacing rules, so that multiple users can share the same cached SQL statement, this the impact here will be minimal. BUT...if each user has the capability of running up to 255 different, unique then you _could_ run out of space in the shared pool, and subsequently have to enlarge your SGA.

However, since Oracle often opens cursors without you knowing about them, it is quite possible that 50 is not enough. I, too, do not have any reservations about increasing OPEN_CURSORS. So far, all of our apps use shareable SQL statements, and the impact has not been significant (mater of fact... it's been negligible).

So raise it to 100, and see how that goes. I never go to the maximum allowed value at first, for any of the initSID.ora parameters.

HTH

--
============================================================================
Mike Vergara           |   Be good...and you will be lonesome
vergara_at_nosc.mil       |                                       Jimmy Buffett
Opinions expressed are not necessarily those of anyone else but me.  So there.