++++++++++++++++++++
Problem description
++++++++++++++++++++
Some users started getting ORA-4031. This is the eror that show following:
4031, 00000, “unable to allocate %s bytes of shared memory (“%s”,”%s”,”%s”,”%s”)”
++++++++++++++++++++++
Problem solution
++++++++++++++++++++++
We ran following query and found failues that may result into 4031.
set echo off verify off
set lines 120 pages 555
/*
V$SHARED_POOL_RESERVED_SIZE
if enough memory then
Goal : REQUEST_MISS = 0
if not enough memory to add then
REQUEST_FAILURES = 0 or not increasing
LAST_FAILURE_SIZE > shared_pool_reserved_min_alloc
AVG_FREE_SIZE > shared_pool_reserved_min_alloc
If any of above is not met the incr the shared_pool_reserved_size.
=====================================
If REQUEST_FAILURES > 0 (and increasing) and
( LAST_FAILURE_SIZE > shared_pool_reserved_min_alloc
or MAX_FREE_SIZE < shared_pool_reserved_min_alloc
or FREE_MEMORY < shared_pool_reserved_min_alloc ) then
option:1 -> Incr shared_pool and shared_pool_reserved size
This will increase the amount of memory available on the reserved
list without impacting users not allocating memory from the reserved list.
option:2 -> Incr The shared_pool_reserved_min_alloc size
This reduces the number of allocations allowed to use memory from the
reserved list; doing so, however, will increase normal shared pool
perhaps impacting other users on the system.
end if;
Request Misses = 0 can mean the Reserved Area is too big.
Request Misses always increasing but Request Failures not increasing can mean the Reserved Area is too small.
In this case flushes in the Shared Pool satisfied the memory needs.
Request Misses and Request Failures always increasing can mean the Reserved Area is too small and
flushes in the Shared Pool are not helping (likely got an ORA-04031).
*/
prompt “SHARED_POOL_RESERVED stats”
select REQUEST_FAILURES, request_misses, LAST_FAILURE_SIZE, avg_free_size, max_free_size, free_space
from v$shared_pool_reserved;
Prompt ‘Following query shows sub-pools of shared pool from SGA’
column kghlurcr heading “RECURRENT|CHUNKS”
column kghlutrn heading “TRANSIENT|CHUNKS”
column kghlufsh heading “FLUSHED|CHUNKS”
column kghluops heading “PINS AND|RELEASES”
column kghlunfu heading “ORA-4031|ERRORS”
column kghlunfs heading “LAST ERROR|SIZE”
compute sum of kghlunfu on report
select
kghlurcr,
kghlutrn,
kghlufsh,
kghluops,
kghlunfu,
kghlunfs
from
sys.x$kghlu
/
prompt ‘Following query will return subpool wise free memory’
select addr, indx, INST_ID, KSMSSNAM, KSMSSLEN/(1024) FREE_KB, KSMDSIDX from x$ksmss where ksmssnam=’free memory’;
prompt ‘Following query will return shared pool wise free memory’
select pool, name, round(bytes/(1024)) FREE_KB from V$sgastat where POOL = ‘shared pool’ and NAME=’free memory’;
/*
KSMLRSIZ – amount of contiguous memory being allocated. Values over around 5K start to be a problem,
values over 10K are a serious problem, and values over 20K are very serious problems.
Anything less then 5K should not be a problem.
KSMLRNUM – number of objects that were flushed from the shared pool in order allocate the memory.
KSMLRHON – the name of the object being loaded into the shared pool if the object is a PL/SQL object or a cursor.
KSMLROHV – hash value of object being loaded
KSMLRSES – SADDR of the session that loaded the object
Prompt ‘Following query shows what is the query that is running and causes other parse trees to get out from shared_pool’
*/
select a.sid, a.serial#, a.machine, a.osuser, b.sql_text, c.ksmlrsiz “Allocted size” , KSMLRNUM “obj got out”
from v$sqltext b, v$session a, x$ksmlru c
where b.hash_value = a.sql_hash_value
and b.address = a.sql_address
and a.saddr = c.ksmlrses
and c.ksmlrsiz > 5000;
To resolve this issue, We changed following parametre and bounced the instance.
shared_pool_size=2147483648 # increased from 1.2G
shared_pool_reserved_size=429496730 # increased from 120M
_kghdsidx_count=2 # this is # of subpools in shared_pool (for >9i). We decrease it from 7 (default)
_shared_pool_reserved_min_alloc=4000 # this shows any allocation more than this size will goto reserved shared pool.
After bouncing the instance everything is fine and we took connections for about 2800 user sessions.