My Oracle Support Banner

ORA-4031 On ASM Instances With High SQLA and Procwatcher is Running (Doc ID 1454936.1)

Last updated on NOVEMBER 28, 2016

Applies to:

Oracle Server - Enterprise Edition - Version 11.2.0.2 and later
Information in this document applies to any platform.

Symptoms

This issue was observed on Oracle 11.2.0.2 64-bit on a Linux x86_64 server, on a ASM Instance, part of a RAC cluster.

The memory allocation on ths instance were:
  shared_pool_size = 200M
  large_pool_size = 80M
  instance_type = "asm"
  sga_target = 500M
  db_cache_size = 128M




1) An example of the errors were:
  

Sun Apr 15 05:13:01 2012
  Errors in file /u01/app/oracle/diag/asm/+asm/+ASM1/trace/+ASM1_ora_2061.trc (incident=72226):
  ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select 'PROC '||spid PROC, a...","sga heap(1,0)","kglsim heap")
  Incident details in: /u01/app/oracle/diag/asm/+asm/+ASM1/incident/incdir_72226/+ASM1_ora_2061_i72226.trc





2) The trace file shows a single subpool where the largest memory consumer is the SQLA area.

SUBPOOL Top memory consumers
--------------------------------
"SQLA " 53112128
"KGLH0 " 23993336
"ASH buffers " 14680064
. . . . .



3) The trace file shows the last wait event was: 'SGA: allocation forcing component growth'.

 

4) Another symptom was the continuous growth of the SQLA area in the shared pool.
This can be determined by taking regular samples as follows:

SQL> select * from v$sgastat where pool = 'SQLA';

 

5) A look at the objects in the SQL area can be one with a SQL statement like this:

select substr(a.sql_text,1,30), a.sql_id, a.sharable_mem SMEM,a.persistent_mem PMEM,a.runtime_mem RMEM,a.version_count VC,a.executions EX
from v$sqlarea a order by VC desc;

6) The SQL area may show SQL statements like the following:
select 'PROC '||p.spid||' - '| . . . . . .
select 'PROC '||spid PROC, ash . . . . . .

Changes

 Procwather was started.

Procwatcher is a tool to examine and monitor Oracle database and/or clusterware processes at an interval. It is found in <Note 459694.1>

Cause

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Symptoms
Changes
Cause
Solution
References

This document is being delivered to you via Oracle Support's Rapid Visibility (RaV) process and therefore has not been subject to an independent technical review.
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.