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 challenge 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. 

When an ORA-4031 error occurs, a trace file is raised and noted in the alert log if the process experiencing the error is a background process.   User processes may experience errors without reports in the alert log or traces generated.   The V$SHARED_POOL_RESERVED view will show reports of misses for memory over the life of the database.

Diagnostics scripts are available in Note 430473.1 to help in analysis of the problem.  There is also a training video on using and interpreting the script data Note 1087773.1.

11g Diagnosability

Starting with Oracle Database 11g Release 1, the Diagnosability infrastructure was introduced which places traces and core files into a location controlled by the DIAGNOSTIC_DEST initialization parameter when an incident, such as an ORA-4031 occurs. For earlier versions, the trace file will be written to either USER_DUMP_DEST (if the error was caught in a user process) or BACKGROUND_DUMP_DEST (if the error was caught in a background process like PMON or SMON). The trace file contains vital information about what led to the error condition.  

Note 443529.1 11g Quick Steps to Package and Send Critical Error Diagnostic 

Information to Support[Video]

Oracle Configuration Manager (OCM)

Oracle Configuration Manager (OCM) works with My Oracle Support to enable proactive 
support capability that helps you organize, collect and manage your Oracle configurations.

Oracle Configuration Manager Quick Start Guide

Note 548815.1: My Oracle Support Configuration Management FAQ 

Note 250434.1: BULLETIN: Learn More About My Oracle Support Configuration Manager 

For more on the 4031 and how this affects the SGA, see 

Note 396940.1 Troubleshooting and Diagnosing ORA-4031 Error

Because of the multiple potential causes, it is important to gather enough diagnostics so that 

an appropriate solution can be identified.  However, most commonly the cause is associated 

with configuration tuning.   Ensuring that MEMORY_TARGET or SGA_TARGET are large 

enough to accommodate workload can get around many scenarios. 

The default trace associated with the error provides very high level information about the 

memory problem and the "victim" that ran into the issue.   The data in the default trace is 

not going to point to the root cause of the problem.

When migrating from 9i to 10g and higher, it is necessary to increase the size of the Shared 

Pool due to changes in the basic design of the shared memory area.

Note 270935.1 Shared pool sizing in 10g

NOTE: Diagnostics on the errors should be investigated as close to the time of the error(s) 

as possible.  If you must restart a database, it is not feasible to diagnose the problem 

until the database has matured and/or started seeing the problems again.

Note 801787.1 Common Cause for ORA-4031 in 10gR2, Excess "KGH: NO ACCESS" 

Memory Allocation

***For reference to the content in this blog, refer to Note.1088239.1 Master 
Note for Diagnosing ORA-4031