Gathering Initial troubleshooting information for analysis of ORA-4031 Errors on the Shared Pool

In this Document

Purpose

Troubleshooting Steps

References

Applies To:Oracle database-enterprise edition-version 8.0.6.3 and later
information in this document applies to any platform.
PURPOSE

The purpose of this document are to provide some steps to take to gather some essential information necessary for initial a Nalysis of ORA-4031 errors on the shared pool. For more detailed information gathering, please refer to MOS Document 430473.1.

Note:this Document only applies to ORA-4031 errors generated on the shared pool. For ORA-4031 errors in the large pool, please review MOS Document 1674933.1. For ORA-4031 errors in the Java and Streams pools, please review MOS Document 1675450.1.TROUBLESHOOTING STEPS

Review MOS Document 1674117.1 on what information are to be gathered for analysis of a ORA-4031 issue, why this Informatio n is necessary, and what to obtain it.

At least the following pieces of information should is collected to being able to start analysis of the ORA-4031 issue:

2. For oracle10g and lower releases, obtain the trace file of the very first ORA-4031 occurrence reported after the last inst ance startup. For oracle11g and higher release, obtain the incident package (see MOS Document 443529.1) of the very first ORA-4031 incid ENT reported after the last instance startup.
   
   
4. Obtain the RDA report (see MOS Document 314422.1 for details of installing & running RDA. RDA can be included in incident packages by default in 11.2.0.2 onwards by following the instructions in MOS Document 1456 047.1).
   
   
5. Obtain the current memory allocations in the shared pool:
   Connect/as SYSDBA
   
   SET PAGESIZE 9999
   SET linesize 256
   SET Trimout on
   SET Trimspool on
   COL ' Total Shared Pool Usage ' FORMAT 99999999999999999999999
   COL bytes FORMAT 999999999999999
   COL current_size FORMAT 999999999999999
   COL name FORMAT A40
   COL value FORMAT A20
   ALTER SESSION SET nls_date_format= ' dd-mon-yyyy HH24:MI:SS ';
   
   SPOOL Sgaparams. Txt
   
   /* Database Identification */
   SELECT name, platform_id, database_role from V$database;
   SELECT * from v$version WHERE banner like ' Oracle database% ';
   
   /* Current instance parameter values */
   SELECT n.ksppinm name, V.KSPPSTVL value
   From X$ksppi N, X$KSPPSV v
   WHERE n.indx = V.indx
   and (n.ksppinm like '%shared_pool% ' OR n.ksppinm in (' _kghdsidx_count ', ' _ksmg_granule_size ', ' _memory_imm_mode_without _autosga '))
   ORDER by 1;
   
   /* Current Memory settings */
   SELECT component, current_size from v$sga_dynamic_components;
   
   /* Memory Resizing operations */
   SELECT start_time, End_time, Component, Oper_type, Oper_mode, Initial_size, target_size, final_size, status
   From V$sga_resize_ops
   ORDER by 1, 2;
   
   /* Historical Memory resizing operations */
   SELECT start_time, End_time, Component, Oper_type, Oper_mode, Initial_size, target_size, final_size, status
   From Dba_hist_memory_resize_ops
   ORDER by 1, 2;
   
   /* Shared Pool 4031 Information */
   SELECT request_failures, last_failure_size from v$shared_pool_reserved;
   
   /* Shared pool reserved 4031 information */
   SELECT requests, request_misses, Free_space, Avg_free_size, Free_count, max_free_size from v$shared_pool_reserved;
   
   /* Shared pool memory allocations by size */
   SELECT name, bytes from v$sgastat WHERE pool = ' shared pool ' and (bytes > 999999 OR name = ' free Memory ') ORDER by byte s DESC;
   
   /* Total Shared pool usage */
   Select SUM (bytes) "Total Shared pool Usage" from v$sgastat WHERE Pool = ' Shared pool ' and name! = ' free memory ';
   
   /* Cursor sharability problems */
   /* This version was for >= 10g; For <= 9i substitute ss.kglhdpar for ss.address!!!! */
   SELECT sa.sql_text,sa.version_count,ss.*
   From V$sqlarea Sa,v$sql_shared_cursor SS
   WHERE sa.address=ss.address and Sa.version_count > 50
   ORDER by Sa.version_count;
   
   SPOOL OFF
   
   
7. For oracle10g Ang later, obtain a AWR report (see MOS Document 748642.1) from the period where the first ORA-4031 was enc Ountered:
   Connect/as SYSDBA
   @?/rdbms/admin/awrrpt.sql
   
   
9. For oracle10g and later, obtain SGA statistics:
   Connect/as SYSDBA
   Oradebug Setmypid
   Oradebug Unlimit
   ALTER SESSION SET Events ' immediate trace name Dump_all_comp_granule_addrs Level 1 ';
   Oradebug Tracefile_name
   Oradebug Close_trace

After have gathered all of the above information, upload the information to the Service Request.

referencesnote:443529.1-Database 11g:quick Steps to package and Send Critical Error Diagnostic information to support [Video]
note:314422.1-Remote Diagnostic Agent (RDA)-Getting Started


note:430473.1-ORA-4031 Common analysis/diagnostic Scripts [Video]
note:1456047.1-How to Setup Automatic Ida Data Collection for Incident Packages?
note:1675450.1-gathering Initial troubleshooting information for analysis of ORA-4031 Errors on Java and Streams pools
note:1674117.1-srdc-ora-4031 on Shared pool:checklist of Evidence to supply

note:1674933.1 -gathering Initial troubleshooting information for analysis of ORA-4031 Errors on the Large Pool

Others

Ora-04031:unable to Allocate%n Bytes of Shared Memory ("Shared Pool",%s,%s,%s)

Applies To:Oracle database-enterprise edition-version 8.1.7.4 and later
information in this document applies to any platform.
***checked for relevance on 31-jul-2013***
archivedPURPOSE

Ora-04031:unable to allocate%s bytes of shared memory (\ "%s\", \ "%s\", \ "%s\", \ "%s\")

Troubleshooting STEPS

Fix:try to increase shared_pool_size in Init.ora file. If It seems large enough then  improve segmentation in shared pool by reserving part of the shared pool for large objects U Sing parameter shared_pool_reserved_size (good value to start tuning is 1/3 of Shared-pool SIZE). It's possible to decide what's "large object" by parameter Shared_pool_reserved_min_alloc.

Gathering Initial troubleshooting information for analysis of ORA-4031 Errors on the Shared Pool