There are a number of possible reasons for getting the ORA-04031 error. It could happen with any SQL being executed, but that does not mean that SQL or code is the actual culprit of the error. It just means that that specific SQL was unable to get sufficient memory allocated in the specified pool.
From Oracle Support:
An ORA-4031 error is raised when memory is unavailable for use or reuse in the System Global Area (SGA). The error message will indicate the memory pool getting errors and high level information about what kind of allocation failed and how much memory was unavailable. The problem with ORA-4031 analysis is that the error and associated trace is for a "victim" of the problem. The failing code ran into the memory limitation, but in almost all cases it was not part of the root problem. The error might be caused by:
- SGA components too small for workload.
- Auto-tuning issues.
- Fragmentation due to application design.
- Bug/leaks in memory allocations.
We do recommend reviewing the alert log and trace files generated by Oracle when getting this error as this might provide more details as to what might be the cause.
We recommend the review of the following Oracle support notes as well as reviewing the latest patch set notes as there are a number of bugs filed against all versions with regards to the 4031 error:
- Master Note for Diagnosing ORA-4031 [ID 1088239.1]
- OERR: ORA-4031 "unable to allocate %s bytes of shared memory ("%s","%s","%s")" [ID 4031.1]
- Diagnosing and Resolving Error ORA-04031 on the Shared Pool or Other Memory Pools [Video] [ID 146599.1]
- 10.2.0.5 Patch Set - List of Bug Fixes by Problem Type [ID 1088172.1]
Anton Els July 19, 2013 17:11