Prior to release of the 9.2.0.5.0 patch set, the maximum number of =
cursors that could be cached for fast lookup by PL/SQL was bounded by =
the value of the init.ora parameter open_cursors. If you currently have =
open_cursors set to a high value (for example, greater than 1000), it is =
likely that this is causing large numbers of PL/SQL cursors to be cached =
in the shared pool. This could lead to issues with memory management, =
frequent reloading of library cache objects and ORA-04031 errors.

Patch set 9.2.0.5.0 alleviates the issue by changing the init.ora =
parameter which determines the upper bound for PL/SQL cursor caching =
from open_cursors to session_cached_cursors.

Most users will not need to modify the value of either of these =
parameters. If you already have session_cached_cursors set to a value =
greater than the open_cursors parameter, then this change will have no =
performance impact upon your system.

However, if you have session_cached_cursors set to zero, or set at a =
value significantly lower than the open_cursors parameter, and you are =
concerned that PL/SQL cursors need to be cached for optimal performance, =
then you should ensure that the session_cached_cursors parameter is =
increased appropriately.

This issue is bug number 3150705.=20
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=
=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D=3D

>From that, I thought that it was proper to set
|open_cursors|=3D|session_cached_cursors|
where |parameter| =3D value (aka magnitude or scalar).

The metalink note makes it clear to me that this is overkill. From this, =
it looks like I'd revert session_cached_cursors back to 100 and leave =
open_cursors at 255 like I did back in 9.2.0.4.

I do recall having a series of ora-4031 issues for a db after it went =
from 8.1.7 to 10.1.0.3. This could help to explain that. (the system =
state dump run while throwing the 4031s never completed as after 40 min =
we just bounced the db inst).

Paul

On Tue, 15 Mar 2005 16:50:58 -0500, Robyn <robyn.sands_at_gmail.com> wrote:
> Hello all,>=20> One of my test/dev instances went belly up yesterday morning with=20> ORA-07445 errors in the alert log. I've the the database back but I=20> ran across a doc on metalink (Note:274496.1) that indicates using=20> parameter session_cached_cursors should not be used with version=20> 9.2.0.5 and up.>=20> This database is 9.2.0.6 / HP-UX 11.23. I've been selectively using=20> session_cached_cursor with other 9.2.0 databases but this is the first =