Problem Description
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.
Solution
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
Hi guys.
I have sga_max_size=544M with autotuning but I have that problem anyway. Increase sga maybe? Do you have working configuration in ASM memory configuration?
Thanks
PD: Oracle 11.2.0.3 Standard, RAC enviroment.
Hi,
Unfortunately each environment is different than another, so there is not necessary a one size fit all.
In many cases when getting the ORA-4031 increasing the SGA allocation will resolve the issue, but if this happens constant even with increases in the SGA then there might be a more underlying issue to investigate. It might be SQL that needs to be improved or it can be something else such just not enough memory being allocated, or a possible bug.
Oracle does provide support notes such as the ones listed above to help you track down possible issues. Also keeping up to date with patches is recommended. If you have done multiple memory increases and still getting this error, and investigation following the support notes does not shed light on the issue, then logging a call with Oracle support might be a good option.
Regards Anton