Re: ORA-04031 errors

Have you tried pinning stored procedures into the db - or, if your code is all in Pro*C, rewriting the SQL intensive portions as stored procedures called from the Pro*C and pinning those stored procedures ?

doing that can help alleviate the shortage of available memory that  you seem to be encountering. If you have a G of physical memory, you may be able to raise the shared pool a bit more than the 30000000, as long as you leave enough available system memory so that your system doesn't begin doing a lot of swapping/paging.

• Lisa

On Thu, 21 Oct 1999 08:01:47 -0400, Kenneth C Stahl <BlueSax_at_Unforgettable.com> wrote:

>I have an Oracle 7.3.4 database running under SunOS 5.6. There is 1G
>physical memory in the machine and the shared memory has been set to 400M.
>The initxxx.ora file has a shared_pool_size of 30000000
>
>The nature of the application using the database is that files containing
>ascii data are parsed and the extracted data is inserted into two tables -
>one containing a parent record and the other containing numerous child
>records.
>
>For some reason that I do not understand, after a day or so of operations,
>we start receiving ORA-4031 errors in the PRO-C program which inserts the
>records.
>The only thing that I have been able to do to keep this from happening has
>been to create a cron job which runs periodically to perform a "ALTER
>SYSTEM FLUSH SHARED_POOL".
>
>Does anyone else have any experience with this?
Received on Thu Oct 21 1999 - 12:44:48 CDT