Re: Shared memory problem on Oracle 7.2.2
Date: 1996/01/11
Message-ID: <graeme-1101961932050001_at_grsmac.uk.pyramid.com>#1/1
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.
Does your application use "sharable" SQL? eg bind variables, consistent naming conventions etc, or does it generate a "large number" of different statements?
>
> Our database server is a SparcStation 20 with 32Mb shared memory.
> The Oracle database has 10 Mb shared memory (init.ora parameter
shared_pool_size)
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.
graeme
-- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd or Pyramid Technology Inc --------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44(0)1252 373035 ---mmm----- Senior Database Consultant Fax : +44(0)1252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme_at_pyra.co.uk --------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.Received on Thu Jan 11 1996 - 00:00:00 CET