Re: ORA-04031 errors

Darren Smith wrote:
>
> It sounds like your shared_pool doesnt have enough contiguous space. Its
> probably fragmented.
>
> People usually get this error while attempting to load a big package
> or while executing a very large procedure and there is not sufficient
> contiguous free memory in the SHARED_POOL. This may be due to:
>
> 1. Fragmentation
> or
> 2. Insufficient memory in the shared pool.
>
> If it is due to the former, one needs to flush the shared pool and/or break up
>
> the package or procedure into smaller blocks. If the shared pool is badly
> fragmented, even using small packages or procedures can result in this error.
> Flushing the shared pool might not help all the time because it will not flush
>
> the PINNED buffers that are being changed at that time.
>
> Utilize dbms_shared_pool package available with 7.0.13 and higher.
> This package allows you to display the sizes of objects in the shared pool,
>
> and mark them for PINNING in the SGA in order to reduce memory
> fragmentation.
>
> The other option is to increase the size of your shared pool.
>
> Hope this helps..
>

Increasing the shared pool size seems to help, but I'm not sure why this problem is happening if it is being caused by package loading because in the particular case I'm working with there are no pl/sql blocks being run. The ONLY thing that is being done is that a single process (I believe it is PRO-C, but I don't have the source code) is reading text files, parsing them and then inserting extracted data into two tables. That's it. That is the only work that is being done by the database. Still, I'll take a look at the dbms_shared_pool package to see if I can learn anything.

At what point does the size of shared_pool become absurd? If I have a machine with 1G physical and my SHMMAX is 400M, then would it be reasonable to set shared_pool as big as 100M or larger? Is there a point of diminishing returns?

What really bugs me about this is that I'm having the problem on a Solaris box and currently my shared_pool_size is 30M. I'm running the same stuff on a Unixware box that has two database instances and a much heavier load overall and on that machine I'm getting by with a 20M shared_pool_size. I don't understand why there should be that much difference between the two platforms unless there is a patch that I am missing on the Solaris boxes.

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