Unanswered: "ORA-04031: unable to allocate 4200 bytes" while there's a lot of free memory
I'm occasionally getting this error when a Java application attempt to connect to my Oracle 8.1.7 database:
ORA-04031: unable to allocate 4200 bytes of shared memory ("shared pool","DATABASE","sga heap","state objects")
At the same time I don't see any problems with SGA: there's plenty free space and I think no fragmentation:
SQL> select * from v$sgastat
POOL NAME BYTES
----------- -------------------------- ----------
shared pool free memory 9791432
large pool free memory 614400
java pool free memory 17747968
java pool memory in use 3223552
34 rows selected.
Well, there are a couple of Java functions that get called once in a while but I don't believer that is where the problem is.
As you can probably guess from my questions, I haven't got much experience with Oracle. I googled around and from what I've read I suspect that the problem arises not from insufficient free space but from SGA fragmentation. Moreover, it doesn't seem to be happening in the SQL area because 1) judging by the parse/execute ratio of 0.09% it's being used quite efficiently and 2) flushing shared pool didn't help a bit. Does my reasoning make sense?
The error happens during either connect or call to a stored procedure. I understand that by pinning most frequently used packages I should be able to solve the issue. Does that sound right? Are there any other ways to reduce fragmentation (or may be coalesce free memory fragments)?
Increasing your shared_pool size would probably be worthwhile as you do seem to be a bit close to the limit. As a minor point the log_buffer parameter may be a bit low unless your application is mainly doing reads rather than writes.
you might also try to pin some of the sql in the SGA... Those errors are because there is not sufficient contiguous memory in the pool. As sql is aged
from the sga, it will leave a "hole" ... these "holes" get refilled as long as the
incoming sql can "fit"... 8.1.7 also had a bug... This is an undocumented parameter that you can put in your INIT.ORA file to try and work around some of those errors... Check it out on MetaLink