Re: Ora 4031

From: Mladen Gogala <gogala_at_sbcglobal.net>
Date: Tue, 03 May 2005 04:09:27 +0000
Message-Id: <1115093367l.5704l.2l@medo.noip.com>

On 05/02/2005 09:38:16 PM, Wolfgang Breitling wrote:
> Mladen,

>=20
> I have to second Yongping's sentiment. The required size of the shared po=
ol=20
> depends on a lot of factors. I am running several Peoplesoft databases wi=
th=20
> a shared pool of 80-100M each and none is getting 4031 errors. Granted,=20
> they are development instances.

Wolfgang, I am managing an OLTP database accessed by a homegrown applicatio= ns,
written in Java and relying heavily on PL/SQL. DBMS_LOB is used on a regula= r basis.
The company that worked for previously was using Oracle*Text and also requi= red large
shared pools. I monitored V$SGASTAT from OEM and it used to have approximat= ely 30%
of free space, after everything that was used regularly was pinned. My numb= er may have=20
been influenced by recent upgrade of 10.1.0.3 database to 10.1.0.4 which fa= ils if you=20
don't have at least 150MB of shared pool, but not by much.

> I agreee that Oracle 9i probably needs a bit more shared pool than 8i, al=
l=20
> else being equal.

Yes, but not that much more. The problem is with abundant new features, lik= e DBMS_METADATA
which is really expensive. When people start using those, it starts to get = expensive. After=20
switching from an austere version 8i, 9.2 is like a candy store.

>=20

> Laura,

>=20

> do you have any non-default cursor parameters? Such as "open_cursors",=20
> "session_cached_cursors", or "cursor_space_for_time"? hat is the exact=20
> error you are getting. Is it always the same routine that is getting the=20
> error. Have you tried "keep"ing the "STANDARD" (and others) PL/SQL proced=
ures?

Ufff, I forgot about "cursor_space_for_time". I haven't used in ages, ever = since=20
the early versions of 8i. I should be consulting more.

--=20
Mladen Gogala
Oracle DBA

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

Received on Tue May 03 2005 - 00:14:00 CDT