Note 4031.1 OERR: ORA 4031 (Known Issues)
Note 1087773.1 ORA-4031 Diagnostics Tools [Video]
Have you observed an ORA-04031 error reported in your alert log?
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.
Looking for the best way to diagnose?
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.
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]
Note 250434.1: BULLETIN: Learn More About My Oracle Support Configuration Manager The ORA-4031 can occur for many different reasons. Some possible causes are:
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.
Note 250434.1: BULLETIN: Learn More About My Oracle Support Configuration Manager
The ORA-4031 can occur for many different reasons. Some possible causes are:
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"