Check ORA-04031

Note:146599.1
1-Check for shared_pool params
SELECT SUBSTR(NAME,1,30) ,SUBSTR(VALUE,1,50),ISSES_MODIFIABLE ISSYS_MODIFIABLE FROM V$PARAMETER where name like 'shared_pool%';
2-Check free space in the shared_pool

select pool,to_char(to_number(v$parameter.value),'999,999,999,999,999,999') "shared pool size", to_char(v$sgastat.bytes,'999,999,999,999,999,999') "free bytes ", (v$sgastat.bytes/v$parameter.value)*100 "Percent Free"from v$sgastat,v$parameter where v$sgastat.name ='free memory' and v$parameter.name =decode(pool,'shared pool','shared_pool_size', 'large pool','large_pool_size','java pool','java_pool_size');

3-Check SHARED_POOL_RESERVED_MIN_ALLOC

select nam.ksppinm NAME,val.KSPPSTVL VALUE from x$ksppi nam,x$ksppsv val where nam.indx = val.indx and nam.ksppinm like '%shared%'order by 1;

4-Check V$SHARED_POOL_RESERVED
Inadequate Sizing of (shared_pool_size ,shared_pool_reserved_size )
If REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC or REQUEST_FAILURES is 0 and LAST_FAILURE_SIZE is < SHARED_POOL_RESERVED_MIN_ALLOC Fragmentation if REQUEST_FAILURES is > 0 and LAST_FAILURE_SIZE is > SHARED_POOL_RESERVED_MIN_ALLOC

5-Check X$KSMSP for freable memory

SELECT KSMCHCLS CLASS, COUNT(KSMCHCLS) NUM, SUM(KSMCHSIZ) SIZ,To_char( ((SUM(KSMCHSIZ)/COUNT(KSMCHCLS)/1024)),'999,999.00')'k' "AVG SIZE" FROM X$KSMSP GROUP BY KSMCHCLS;

a) if free memory (SIZ) is low (less than 5mb or so) you may need to increase the shared_pool_size and shared_pool_reserved_size
b) if perm continually grows then it is possible you are seeing system memory leak.
c) if freeabl and recr are always huge, this indicates that you have lots of cursor info stored that is not releasing

d) if free is huge but you are still getting 4031 errors, (you can correlate that with the reloads and invalids causing fragmentation)

6-Check for literal sql

select substr(sql_text,1,40),count(*) from v$sqlareagroup by substr(sql_text,1,40) having count(*)>5order by count(*)
/

7-Check V$SHARED_POOL_ADVICE for shared_pool size

select * from V$SHARED_POOL_ADVICE ;


8-check V$SHARED_POOL_RESERVED (Is my Reserved Area sized properly)

select free_space, avg_free_size, free_count, max_free_size, request_misses, request_failures,LAST_FAILURE_SIZEfrom v$shared_pool_reserved
;

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


9-check which pool had error

select INDX,kghlurcr,kghlutrn,kghlufsh,kghluops,kghlunfu,kghlunfs
from sys.x$kghlu
whereinst_id = userenv('Instance');

10-Check Memory chunks

col sga_heap format a15
col size format a10

select KSMCHIDX "SubPool", 'sga heap('KSMCHIDX',0)'sga_heap,ksmchcom ChunkComment,
decode(round(ksmchsiz/1000),0,'0-1K', 1,'1-2K', 2,'2-3K',3,'3-4K',
4,'4-5K',5,'5-6k',6,'6-7k',7,'7-8k',8,
'8-9k', 9,'9-10k','> 10K') "size",
count(*),ksmchcls Status, sum(ksmchsiz) Bytes
from x$ksmsp
where KSMCHCOM = 'free memory'
group by ksmchidx, ksmchcls,
'sga heap('KSMCHIDX',0)',ksmchcom, ksmchcls,decode(round(ksmchsiz/1000),0,'0-1K',
1,'1-2K', 2,'2-3K', 3,'3-4K',4,'4-5K',5,'5-6k',6,
'6-7k',7,'7-8k',8,'8-9k', 9,'9-10k','> 10K');