Re: open cursor limit?

In our last gripping episode Mike Krolewski <mkrolewski_at_rosetta.org> wrote:
> In article <90ja1u$dvc$1_at_nnrp1.deja.com>,
> susana73_at_hotmail.com wrote:
> > Hi,
> >
> > I have open_cursor=250 and keeping getting the maximum cursor error
> > message. I am wondering can I increase it as large as I want? What
> > kind of resource will it take on the server if I increase it? Does
 it
> > take up any resource even my apps is not running?
> >
> > Thanks,
> > Susan
> >
> > Sent via Deja.com http://www.deja.com/
> > Before you buy.
> >
>
> I was looking up this error in the 'Oracle PL/SQL Programming' by
> S.Feuerstein (O'Reilly) and came across this statement:
>
> "an initialization parameter called OPEN_CURSORS specifies the
> maximum number of open cursors tha a SINGLE-USER PROCESS can have at
> once. This parameter does not control a system-wide feature, but rath
> the maximum address/memory user by each process." (Bold mine)
>
> I would suggest that 250 is really huge for a single process. In fact,
> most system I have worked on the number is 50 or less. I cannot recall
> ever intentionally having more than 5 cursors open at the same time.
>
> --
> Michael Krolewski
> Rosetta Inpharmatics
> mkrolewski_at_rosetta.org
> Ususual disclaimers
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

You have, apparently, been working on small systems, or, at the very least, resource-frugal applications. I have worked on a number of databases where the open cursor count exceeded 10000, sometimes during complex and convoluted batch processes. Having the open_cursors parameter set at the default of 50, and having the instance actually function, is an anomaly.

--
David Fitzjarrell
Oracle Certified DBA


Sent via Deja.com http://www.deja.com/
Before you buy.