Re: I need some helps ORA:4031.

Miro Kundacina wrote:
>
> Hello,
>
> I need some helps.
> Error description:
> Error ODBC/18 Error during execute SQL (SQLCODE=-1) (S1000 - 4031 -
> [Oracle][ODBC Oracle Driver][Oracle OCI]ORA-04031: unable to allocate 740
> bytes of shared memory ("unknown object","sga heap","library cache").)
>
> - Oracle7 Server Release 7.3.2.3.0
> - RS/6000 J40 (Aix 4.1 ) RAM 512 Mb
>
> The error occurs only in working with two tables (PAYMENTS, CARD), yet more
> complexed jobs are performed in other tables where the error doesn't occur.
>
> Thanks
>
> Miro Kundacina

Create the following shell script and then run it from your cron. Start off with doing it once per day and if the 4031 errors continue keep decreasing the time between runs until the errors stop. You may have to experiment with this.

#!/bin/ksh
ORACLE_SID="ORCL";export ORACLE_SID
ORACLE_HOME="/oracle/product/7.3.2"
${ORACLE_HOME}/bin/sqlplus -s system/manager <<-sqlEOF set feedback off;
set pages 0;
set echo off;
whenever sqlerror exit failure;
alter system flush shared_pool;
exit success;
sqlEOF
if [[ $? -ne 0 ]];then

    print -u2 "sga flush failed";
    exit 1;
fi
exit 0;

Of course, use the appropriate ORACLE_SID and ORACLE_HOME for your setup. If you don't want to use a literal login/password then simply create an ops$oracle account, give it dba privileges and then use the default logon.

On OLTP systems with low to moderate activity you may be able to get by with running it once per day. If you get into heavy activity then you may have to run it as much as once per hour. For warehousing/reporting databases it will really depend on how many transactions are occurring.

Basically this problem occurs because there is a frequently-used cursor in one of the client programs and the cursor is rebuilt each time it is used and literal values are embedded in the cursor. If it is at all possible to re-write the program, write the cursor to use bind variables instead. There are lots of good reasons for this. First of all, it eliminates the 4031 error, but secondly, it drastically decreases the load on the parser because the parser will recognize that the cursor is already in memory and will reuse that copy rather than creating a new one. As best as I can figure, when the literal values are being used, the parser creates a new copy each time and when 4031 errors occur it means that the cursors are being created faster than they can be aged off of the LRU list. Received on Wed Jul 05 2000 - 00:00:00 CDT