Re: Shared memory problem on Oracle 7.2.2

>Date: Thu, 04 Jan 1996 10:36:09 +0100
>Message-ID: <30EB9F89.5A83_at_stavanger.Geco-Prakla.slb.com>
 

>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\")"
 

>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.

Free advice and all...

Dynamic sql is a known chewer of shared pool - if you are spitting out sql statements filled with literals generated at run-time, then you are asking for trouble. In precompilers, the answer is to use bind variables in your sql.
PLSQL has its own methods for addressing this problem.  

If you don't use bind-variables in your insert statements, then it's likely that none of your sql is reused. This will chew up your shared pool and can eventually result in 4031, I believe.

For example (syntax off top of the head):

insert into mytable values (1,2,3);
insert into mytable values (2,1,3);
insert into mytable values (9,888,22);

As I understand it, if you use this technique, each statement takes up space in shared pool. For a large number of statements, you can get into trouble fairly quickly.

On the other hand, the following approach allegedly needs space for a only single statement:

loop until done
  next var1, var2, var3
  exec sql immediate insert into mytable values ( :var1, :var2, :var3); end loop

The shared-pool police where I work definitely notice if you get lazy with

your bind variables.

Paul Kelley Received on Fri Jan 05 1996 - 00:00:00 CET