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 and later
Information in this document applies to any platform.


This issue was observed on Oracle 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 . . . . . .


 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>


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

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.