Re: ORA-04031 errors

Lisa Hudd wrote:

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

Unfortunately I cannot change the program. We receive it in binary form only and I'm not sure exactly what all is being done in the program.

I'll try increasing the shared_pool_size, but it still seems odd to me that I'm having this problem on a Solaris system with a single instance and a 30M shared_pool while I can get by with a 20M shared_pool on a Unixware system that has much more overall database activity across two instances.

Is there any way to make sql age more rapidly? With this particular application there is essentially no chance that sql will be used more than

once.


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................


.....................................................