RE: session_cached_cursors and ora-07745

From: Mohan, Ross <RMohan_at_arbinet.com>
Date: Tue, 15 Mar 2005 22:36:08 -0000
Message-ID: <CC74E7E10A8A054798B6611BD1FEF4D30625D9BD@vamail01.thexchange.com>

test/dev instance...7445...."other 920 databases" (which full = versions?)...due to....what?

Usually there's *something* in the u/bdump traces that indicates root = cause. Is there some=20
reason you've assumed the cursor caching is causal (as opposed to = coincident)?

Some questions that come to mind.

what are full versions of other DBs?
any other trace files concurrent?
when was session_cached_cursors changed on the test/qa box?
how long was parameter in play before the 7445?
was the instance under steady, sporadic, episodic (i.e. "rollout") or = unusual load?
have you set the parameter down or off (assuming, since this is = test/qa, you have that option)
can you 'instrument' your apps to track cursor usage?=20
is statspack running in there, and can you tracked current/cumulative = open cursors over time?

I'd say "try truncating the URL_PAGES table" but I don't know if you = have the Oracle
High Performance Option (OHPO) installed. :-)

-----Original Message-----
From: oracle-l-bounce_at_freelists.org =
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Paul Drake Sent: Tuesday, March 15, 2005 5:10 PM
To: robyn.sands_at_gmail.com
Cc: oracle-l_at_freelists.org
Subject: Re: session_cached_cursors and ora-07745

>From the readme from 9.2.0.5 patchset for win32:

=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

Oracle9i Database Server
Patch Set Notes
Release 2 Patch Set 4 Version 9.2.0.5.0 for 32-Bit Windows March 2004=20

PL/SQL Cursor Caching

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

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 =

> db that I've seen these errors on. Has anyone else seen this happen=20
> and/or heard that this param should not be used?
>=20
> tia ... Robyn
> --
> http://www.freelists.org/webpage/oracle-l
>=20

--=20
#/etc/init.d/init.cssd stop
# f=3Dma, divide by 1, convert to moles.

--
http://www.freelists.org/webpage/oracle-l
--
http://www.freelists.org/webpage/oracle-l

Received on Tue Mar 15 2005 - 17:40:25 CST