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 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:
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 . . . . . .
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.|