04031 Oracle Error
Contents |
» ORA-4031 Troubleshooting By user706992 on Jun 11, 2010 QUICKLINKS: Note 4031.1 OERR: ORA 4031 (Known Issues) Note 396940.1 Troubleshooting and Diagnosing ORA-4031 Error Diagnostic Tool for ORA-4031 Note 1087773.1 ORA-4031 Diagnostics Tools [Video] Have you observed anORA-04031 error reported
Ora-4031 Unable To Allocate
in your alert log? An ORA-4031 error is raised when memory is unavailable for ora-4031 shared pool use or reuse in the System Global Area (SGA). The error message will indicate the memory pool getting errors and high level
Ora 04031 Oracle 11g
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 http://www.dba-oracle.com/sf_ora_04031_unable_to_allocate_string_bytes_of_shared_memory.htm 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 https://blogs.oracle.com/db/entry/ora-4031_troubleshooting 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 Diagnosibility 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 Common Causes/Solutions The ORA-4031 can occur for many different reasons. Some possible causes are: SGA components too small for workload Auto-tuning issues Fragmentation
Fundamentals Oracle Errors Performance Tuning ASM Datafiles & Tablespaces Exadata 5 Easy Step to Solve ORA-04031 with Oracle Support Provided Tool Are you still seeing "ORA-04031: unable to allocate 512 bytes of http://www.dbas-oracle.com/2013/05/5-Easy-Step-to-Solve-ORA-04031-with-Oracle-Support-Provided-Tool.html shared memory". Now Remote DBA need not to worry about this error because Oracle has introduced a very good tool to Troubleshoot ORA-04031. You just need to upload alert log files and http://blog.tanelpoder.com/2009/06/04/ora-04031-errors-and-monitoring-shared-pool-subpool-memory-utilization-with-sgastatxsql/ trace file having error details and an online tool will give a solution immediately. Before going into detail, Let's first understand what is ORA-04031 ? when It arise ? and what is difference unable to between ORA-04031 and ORA-04030 ? What is ORA-04031 ? and When it arise ? ORA-04031 is error message related to lack of available SGA memory component. When a process needs some memory from SGA and memory is not available in this situation ORA-04031 occurs. What is difference between ORA-04031 and ORA-04030 ? ORA-04031 is error message related to lack of available SGA memory component While unable to allocate RA-04030 is related to lack of available memory in PGA area. ORA-4031 error message look like this in Database Alert log files: Tue Sep 27 22:20:33 2011 Errors in file /u01/app/oracle/diag/rdbms/dbh/Dbh3/trace/Dbh3_j004_16488.trc (incident=72497): ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA","tmp") Incident details in: /u01/app/oracle/diag/rdbms/dbh/Dbh3/incident/incdir_72497/DBH3_j004_16488_i72497.trc Tue Sep 27 22:20:43 2011 Dumping diagnostic data in directory=[cdmp_20110927222043], requested by (instance=3, osid=16488 (J004)), summary=[incident=72497]. Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. ORA-4030 error message look like this in Database Alert log files: Wed Mar 27 13:35:52 2013 Errors in file /u01/app/orappdw1/diag/rdbms/dbh/DBH4/trace/DBH4_pz99_15585_DBMS_SQLDIAG_10053_20130327_132724.trc (incident=146193): ORA-04030: out of process memory when trying to allocate 8224 bytes (qesca subheap,qerjo hash table) Incident details in: /u01/app/orappdw1/diag/rdbms/dbh/DBH4/incident/incdir_146193/DBH4_pz99_15585_i146193.trc Use ADRCI or Support Workbench to package the incident. See Note 411.1 at My Oracle Support for error and packaging details. Wed Mar 27 13:35:57 2013 Note a big difference, but ORA-04031 is SGA related error and ORA-4030 is PGA related error. Let's understand what does this error statement means. ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select t.ts#,t.file#,t.block...","SQLA","tmp"). The first argument in brackets
TPT scripts for solving real life performance problems → ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql by Tanel Poder Posted on June 4, 2009 Since Oracle 9.2 the shared pool can be "partitioned" into multiple parts. This was probably done for relieving shared pool latch contention for crappy applications (which use shared pool latches too much due bad cursor or connection management). The "partitions" are called shared pool subpools and there can be up to 7 subpools. Each subpool is protected by a separate shared pool latch and each subpool has its own freelists and LRU list. If you are interested in more details, a good starting point is this whitepaper by Oracle. There are few different ways for detecting how many subpools you have in use. The more convenient ones are here: You could query X$KGHLU which has a line for each shared pool subpool and (from 10g) also java pool if it's defined: SQL> select count(distinct kghluidx) num_subpools 2 from x$kghlu 3 where kghlushrpool = 1; NUM_SUBPOOLS ------------ 7 The "kghlushrpool" column, which is 1 for shared pool subheaps and 0 for java pool, isn't there in 9i (and in 9i the java pool apparently is not reported in x$kghlu anyway). The reason why I don't just count all matching lines from x$kghlu but use count distinct instead is that in Oracle 10.2.0.1 there are 4x more lines reported in this x$table. There's an additional concept called sub-sub-pool starting from 10.2 where each shared pool sub-pool is split futher into 4 areas (allocations with different expected lifetime/durations go into different sub-sub-pools, but the same sub-pool latch protects all activity in sub-sub pools too). But in 10.2.0.1 the x$kghlu reports all sub-sub-pools too for some reason. The whitepaper from Oracle mentioned above explains this in more detail. So