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:

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

Sign In with your My Oracle Support account

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

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms