Re: Shared memory problem on Oracle 7.2.2

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.