My Oracle Support Banner

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

Last updated on AUGUST 02, 2019

Applies to:

Oracle Database - 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 complete error message reported in ASM alert.log file was:
  

ORA-04031: unable to allocate 4064 bytes of shared memory ("shared pool","select 'PROC '||spid PROC, a...","sga heap(1,0)","kglsim heap")



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

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


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.