TroubleShoot‎ > ‎

ORA-04031

Let's see how to troubleshoot and understand the issue ORA-04031 - unable to allocate %s bytes of shared memory ("%s","%s","%s").
 
 

Introduction

ORA-04031 - deals with shared memory issues. It's lack of allocation of contiguous memory as and when requested by the current executing sql i.e., when process attempts to allocate a large piece of contiguous memory in the shared pool fails.
 
Let's see first how the chunks of memory are allocated.
 
Intially the memory in SGA are of various sizes (memory chunks). As and when instance is up, the memory chunks are allocated per the size of SHARED_POOL_SIZE and divided into various sub pools. The memory chunks are maintained with free list hash buckets. Free list hash buckets enables the process to move the memory chunks with in the pool according to the size and requests came in. When process unable to find the large memory chunk to satisfy the allocation request with the SGA, the issue raised.
 
Let's see the algorithm (or) steps how the memory chunk is allocated when request came in from the process. As stated above the memory areas are maintained with free list and LRU ( Least Recently Used).
 
 
The Shared Pool stores information related to the dictionary and library cache. let's see in simple steps how the allocation of memory chunks are satisfied.
 
Initially, based on requested size of memory chunk it checks the "free list"  - if found then allocates
else  it treats as large chunk of memory required by process
      check for reserved list (_shared_pool_reserved_pct)
      if found then allocates
      else
         carry out the regular LRU Operations and repeat as per threshold value.
 
If still the process could not able to find the large memory chunk, then would lead us to ORA-04031. The above code (or) algorithm of allocation of large memory chunks is repeatedly many times as per threshold limit and over a period time if the process is unable to find the large memory chunk it would raise issue. Try to understand how much internal code is executed and checks are performed and then the issue is raised and try to treat it as serious concern.
 

Systematic Approach 

 Understand the error message

 
Try to analyze the error information, check the error information, at which sub pool we are finding the issue.
Example:-
 
  Error text: unable to allocate %s bytes of shared memory (%s,%s,%s) 
 ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","java/lang/StringSYS","joxlod: in ehe","ioc_allocate_pal")
 
Compare the information across-
 %s bytes  -  4032 bytes  - memory chunk requested by process
 (%s,%s,%s)  -  ("shared pool","java/lang/StringSYS","joxlod: in ehe","ioc_allocate_pal")
                      With in Shared Memory - shared pool
                       Second argument - At that instance what code is executing which requested/ required the memory chunk
                        Third and other rest of the arguments - Memory allocations being requested by process
 
Let's consider one more example ,
 
ORA-04031: unable to allocate 4032 bytes of shared memory ("shared pool","unknown object","sga heap(1,0)","kglsim heap")
 
 %s bytes  -  4032 bytes  - memory chunk requested by process
 "shared pool" -  pool which is lacking of required memory
"unknown object" - object at that instance
"sga heap(1,0)","kglsim heap" -  memory allocations
 
ORA-04031: unable to allocate 32 bytes of shared memory ("shared pool","select u.name, o.name, trigg...","sql area","kglhin: temp")

%s bytes  -  32 bytes - memory chunk requested by process
same as above.
 
If you check correctly, the above are sub pools of Shared pool
 
we can get the details from the view v$sgastat.
 

Collect the Information about your Database which influence the Issue.

From Oracle Architecture point of view we can estimate that, the below details provides the valuable information in order to resolve the issue.
 
SGA Parameters/Current Values/ Advices
 
Fetch out the required parameters values.
 
select name||':  '||decode(value,null,-1,value) "Initial Setting" ,(value/1024/1024) "MBytes"
from v$parameter
where name in
('shared_pool_size','shared_pool_reserved_size','result_cache_size','pga_aggregate_target','streams_pool_size',
 'db_cache_size','db_recycle_cache_size','db_keep_cache_size','db_2k_cache_size','db_4k_cache_size',
 'db_8k_cache_size','db_16k_cache_size','db_32k_cache_size','memory_target','memory_max_target','large_pool_size',
 'java_pool_size','sga_max_size','memory_max_target','memory_target','sga_target')
 
Cursor Parameters
 
select name ||':  '|| decode(value, null,-1,value) "Initial Setting"
from v$parameter
where name in ('session_cached_cursors','open_cursors','processes','sessions','db_files','shared_server')
 
select name ||':  '|| value "Setting"
from v$parameter
where name in ('cursor_sharing','query_rewrite_enabled','result_cache_mode','statistics_level','db_cache_advice','compatible')
 
Reserved Pool Statistics
 
Get the details about reserved values and check for efficiency
 
select request_failures, last_failure_size, free_space, max_free_size, avg_free_size,used_space, requests, request_misses, last_miss_size
from v$shared_pool_reserved
 
 
select requests,
decode(requests,0,0,trunc(100-(100*(request_misses/requests)),0)) PCT, request_failures, last_miss_size, last_failure_size
from v$shared_pool_reserved

  
Note :-
 
SHARED_POOL_SIZE - This parameter specifies the size of the shared pool in bytes
SHARED_POOL_RESERVED_SIZE - It specifies the shared pool space which is reserved for large contiguous requests for shared pool memory.
SHARED_POOL_RESERVED_MIN_ALLOC - The value of this parameter controls allocation of reserved memory. Memory allocation larger than this value can allocate space from the reserved list if a chunk of memory of sufficient size is not found on the shared pool free lists. The default value is adequate for most systems. If you increase the value, then the Oracle server will allow fewer allocations from the reserved list and will request more memory from the shared pool list.
 

Check for Inadequate Sizing of Shared Pool

ORA-04031 error can be due to either an inadequeate sizing of the SHARED POOL size. From the above parameters we can understand that large memory chunks are allocated from "Shared Pool Reserved Size". let's check the view  V$SHARED_POOL_RESERVED and determined whether the issue is with respect to inadequate size or not.
 
select *
from v$shared_pool_reserved
where REQUEST_FAILURES  > 0
and LAST_FAILURE_SIZE  > SHARED_POOL_RESERVED_MIN_ALLOC
 
 SHARED_POOL_RESERVED_MIN_ALLOC - fetched from below query which provides undocumented parameters, its documented in oracle 8.0.6 version
 
select   a.ksppinm  "Parameter",
         b.ksppstvl "Session Value",
         c.ksppstvl "Instance Value"
from    x$ksppi a,
        x$ksppcv b,
        x$ksppsv c
where  a.indx = b.indx
  and  a.indx = c.indx
  and  a.ksppinm ='_shared_pool_reserved_min_alloc'
 
 If the above condiitons are met the you can consider lowering SHARED_POOL_RESERVED_MIN_ALLOC to allow the database putting more objects into the shared pool reserved space and then increase the SHARED_POOL_SIZE if the problem is not resolved, check for bugs reported by oracle with respect to above parameters / issues.
 
 

Check for Fragmentation

 
Second option for the issue would be the fragmentation with in loop. Try to check fragmentation with below query.
 
select *
from v$shared_pool_reserved

where REQUEST_FAILURES  > 0
and LAST_FAILURE_SIZE  < SHARED_POOL_RESERVED_MIN_ALLOC
 
If the above conditions met then you can  consider increasing SHARED_POOL_RESERVED_MIN_ALLOC to lower the number of objects being cached into the shared pool reserved space and increase SHARED_POOL_RESERVED_SIZE and SHARED_POOL_SIZE to increase the available memory in the shared pool reserved space.
 
Further if the issue is not resolved, then their would be cases that the issue might be misleaded. Try to understand the error message and still if the issue is not resolved then get a snapshot of the shared pool when the problem is intact.
 
event = "4031 trace name errorstack level 3"
event = "4031 trace name HEAPDUMP level 2"
 
It would be suggested to carry out the above events in order to get the heapdumps of the shared pool. Try to examine the "free memory "   - with in the head dumps.
Instead of heapdumps we can get the same information from one of the kernel tables i.e., X$KSMSP and X$KSMLRU.
 
X$KSMSP - SGA HeaP - Stores details about SGA pool. Let's see the distinct type of  "Chunks Classes" are available in SGA.
Column "ksmchcls" - provides the chunk class type
Column "ksmchcom" - provides the chunk comments
Column "KSMCHSIZ" - its a chunk size
SQL>  select distinct ksmchcls from x$ksmsp;
KSMCHCLS
--------
freeabl
recr
R-freea
perm
R-free
R-perm
free
7 rows selected.
SQL> SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,
  2  To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')||'k' "AVG SIZE"
  3  FROM X$KSMSP GROUP BY KSMCHCLS;
CLASS           NUM        SIZ AVG SIZE
-------- ---------- ---------- ------------
freeabl       44664  126412148        2.76k
recr          64131  112233960        1.71k
R-freea          82       1968         .02k
perm             13   54301252    4,079.12k
R-free           41   20063436      477.88k
R-perm            2   13162972    6,427.23k
free            912   17754896       19.01k
7 rows selected.
 
freeabl - Memory for user / system processing
recr    - Memory for user / system processing
R-freea - Reserved List
perm  - Memory allocated to the system
R-free - Reserved List
R-perm - Memory allocated to the system
free -  Free Memory
 
Least recently used shared pool chunks (X$KSMLRU)
We can find the things with respect to LRU algorithm. This fixed table can be used to identify what is causing the large allocation and it tracks allocations in the shared pool that cause other objects in the shared pool to be aged out. Before quering this fixed table we must remember the below note.
 
Note - Contents of the fixed table are erased whenever someone selects from the fixed table
 
So, we can create an temp table and let us copy the contents of fixed table for future purpose (or) whenever we need to analyze the information.
 

Tips :-

Library Cache Hit Ratio - ORA-04031 can cause due to Small shared pool size

From oracle 11g versions we can directly get the required values, instead of manual calcualtions.
 

select distinct namespace,gethitratio  , pinhitratio     
from V$LIBRARYCACHE

 

 

 

SQL> select     sum(PINS) Executions,
  2     sum(RELOADS) cache_misses,
  3     sum(RELOADS) / sum(PINS) miss_ratio
  4  from       v$librarycache;

 

EXECUTIONS CACHE_MISSES MISS_RATIO
---------- ------------ ----------
    865168         1821 .002104794;

 

 

Even we can get the details from kernel level table.
 
X$KGLST - Library cache [ST]atistics

KGLSTTYP - kernel General Library Statistic Types (NAMESPACE and TYPE)
KGLSTDSC - kernel General Library Statistic Description
KGLSTGET - kernel General Library Statistic Get
KGLSTGHT - kernel General Library Statistic Gethit
KGLSTPIN - kernel General Library Statistic Pin
KGLSTPHT - kernel General Library Statistic Pinhit
 
 
select  inst_id,
 kglstdsc,
 kglstget,
 kglstght,
 decode(kglstget,0,1,kglstght/kglstget),
 kglstpin,kglstpht,
 decode(kglstpin,0,1,kglstpht/kglstpin),
 kglstrld,kglstinv,
 kglstlrq,
 kglstprq,
 kglstprq,
 kglstmiv,
 kglstmiv
from x$kglst
where kglsttyp = 'NAMESPACE'
and kglstget != 0                     
and LENGTH(kglstdsc) <= 15
 
 
If the ratio of misses to executions is more than 1%, then try to reduce the library cache misses by increasing the shared pool size.

Statements with literal values or candidates to include bind variables

 
SELECT substr(sql_text,1,40) "SQL",count(*) ,sum(executions) "TotExecs"
FROM v$sql
WHERE executions < 5
GROUP BY substr(sql_text,1,40)
HAVING count(*) > 30
ORDER BY 2;
 
Note:- Try to query v$sql instead of v$sqlarea - which increases the latch activity inturn on Library Cache.

Pinned Objects/ Pinned and unpinned objects

select owner, name, type, sharable_mem
from v$db_object_cache
where  kept = 'YES'
order by sharable_mem desc;
 
 
select kept, type, sum(sharable_mem) memory
from v$db_object_cache
group by kept, type
order by 1, 3 desc;
 

Objects Candidate for Pinning

 
select owner, name, type, sharable_mem
from v$db_object_cache
where sharable_mem > 1000
and executions > 10
and (type='PACKAGE' or type = 'PACKAGE BODY' or type='FUNCTION'
   or type = 'PROCEDURE')
and kept= 'NO'
order by sharable_mem desc