Re: Shared memory problem on Oracle 7.2.2

Graeme Sargent (graeme_at_uk.pyramid.com) wrote:
: In article <30EB9F89.5A83_at_stavanger.Geco-Prakla.slb.com>, Per Eivind Solum
: <peres_at_stavanger.Geco-Prakla.slb.com> wrote:
 

: > We are quite frequently getting error 4031 from
: > our Oracle 7.2.2 database:
: >
: >
: > GST0K1 > oerr ora 4031
: > 04031, 00000, "unable to allocate %s bytes of shared memory
: > (\"%s\",\"%s\",\"%s\")"
: > // *Cause: More shared memory is needed than was allocated in the shared
: > // pool.
: > // *Action: Either use the dbms_shared_pool package to pin large packages,
: > // reduce your use of shared memory, or increase the amount of
: > // available shared memory by increasing the value of the
: > // init.ora parameter "shared_pool_size".
 

: Have you followed this advice?
 

: >
: >
: > The error occurs in an application that performs a large number of
: > relatively small insert transactions. The number of bytes that Oracle
: > is unable to allocate is typically in the range of 100 - 500 bytes.
 

: You should check out the shared pool stats in bstat/estat, V$SQLAREA etc.
: Have you done any pinning, or reserving of the shared pool? It's
: difficult to know whether 10MB is appropriate without a lot more data.
 

: >
: > I have verified that the application commits each transaction properly.
: > Does anyone know if this a known problem on Oracle 7.2.2?
: > Is there any workaround?
 

: Almost certainly.
: Have you tried a larger shared pool?
: I have not seen this problem on 7.2.2.4 but I tend to run a much larger
: shared pool. I suspect you just need to tune it, although, on the
: information you have provided, I don't understand why you get allocation
: errors rather than thrashing.

I HAVE seen this error on 7.2.2.4! Even upping the shared pool to 8 times the size that we previously used on 7.0.16 doesn't get rid of the 4031 error. This is an ORACLE acknowledged bug ... one work-around is to 'alter system flush shared_pool' on a regular crontab entry. We find that the garbage collection in 7.2.2.x doesn't function correctly if the shared pool gets anywhere close to densely packed. Oracle did provide us with a bug fix that lets us go up to 20 minutes per 10 MB of shared_pool (i.e. approx. 80,000 small transactions).

Larger shared pools may or may not help, depending on the job mix. I've spoken to users with 150MB of shared pool still running into this ORACLE (not application) bug.

Bill

--
Names:	Bill Beaton			beatonb_at_cadvision.com
					Bill_Beaton_at_calgary.qc-data.com
Phone:	(403) 295-3254 (Home)		(403) 266-8622 (work)