ORA-4031 On ASM Instances With High SQLA and Procwatcher is Running
Last updated on NOVEMBER 28, 2016
Applies to:Oracle Server - Enterprise Edition - Version 184.108.40.206 and later
Information in this document applies to any platform.
This issue was observed on Oracle 220.127.116.11 64-bit on a Linux x86_64 server, on a ASM Instance, part of a RAC cluster.
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:
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.
"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>
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