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');

How to Automate Pinning Objects in Shared Pool at Database Startup

1-I create the tables and jobs under user BAKIM you can create it where ever you want
2- If DBMS_SHARED_POOL is not exist then create it
select * from dba_objects where object_name='DBMS_SHARED_POOL';
sqlplus '/ as sysdba'
@?/rdbms/admin/dbmspool.sql

grant execute on dbms_shared_pool to bakim;


3- Create the tables that will keep the “WILL BE KEPT” objects

create table bakim.keep_in_shared_pool (OWNER VARCHAR2(30),OBJECT_NAME VARCHAR2(128),OBJECT_TYPE VARCHAR2(18))
tablespace tools storage (initial 1m next 1m)
/

alter table bakim. keep_in_shared_pool add constraints keep_in_shared_pool_pk primary key (owner,object_name,object_type)using index storage (initial 128k next 128k)
/

4-Create SPK_KEEP_ISLEM package under SYS which will used for finding and keeping the objects


create or replace package sys.spk_keep_islem is procedure keep_all_schema_procedures(ps_owner varchar2);
procedure keep_all_predefined_objects ;
procedure find_keep_in_shared_pool ;
end;
/

create or replace package body sys.spk_keep_islem is
-----------------------------------------------------------------------
procedure keep_all_schema_procedures(ps_owner varchar2) is
cursor cr_procedures is select 'SYS.dbms_shared_pool.keep('''owner'.'object_name''',''P'')' komut ,
owner'.'object_name oname , object_type
from dba_objects
where owner=ps_owner and object_type in ('PROCEDURE','PACKAGE','FUNCTION');
begin
for l in cr_procedures
loop SYS.dbms_shared_pool.keep(l.oname);
end loop ;
end;

procedure keep_all_predefined_objects is
cursor cr_keep_objects is
select owner'.'object_name oname,
decode(object_type,'PACKAGE','P','PACKAGE BODY','P','PROCEDURE','P','FUNCTION','P','TRIGGER','R','SEQUENCE','Q') otype
from bakim.keep_in_shared_pool k
where exists (select 1 from dba_objects o where o.owner=k.owner and o.object_name=k.object_name and o.object_type=k.object_type );
begin
for l in cr_keep_objects loop
if l.otype='P' then SYS.dbms_shared_pool.keep(l.oname);
else SYS.dbms_shared_pool.keep(l.oname,l.otype);
end if;
end loop ;
end;

procedure find_keep_in_shared_pool is
begin
delete bakim.keep_in_shared_pool k where not exists (select 1 from dba_objects o where k.owner=o.owner and
o.object_name=k.object_name and o.object_type=k.object_type);
insert into bakim.keep_in_shared_pool ( SELECT owner,name,type FROM v$db_object_cache c WHERE 1=1 and type in ('PROCEDURE','PACKAGE BODY','FUNCTION') and not exists (select 1 from bakim.keep_in_shared_pool k where k.owner=c.owner and c.name=k.object_name and c.type=k.object_type) and owner not in ('PKGBACKUP','SERDAR','ABDULLAH','ZIYA','ERKAN','DR0000'));
commit;
end;

end;
/

----------------------------------------------------------------
grant execute on sys.spk_keep_islem to bakim,dba
/

5- If it is the first time then keep the KEEPED objects in the table
insert into bakim.keep_in_shared_pool SELECT OWNER,NAME,TYPE FROM v$db_object_cache WHERE type in ('PROCEDURE','PACKAGE BODY') and kept='YES'
/

exec sys.spk_keep_islem.find_keep_in_shared_pool;
exec sys.spk_keep_islem.keep_all_predefined_objects;

6-Create trigger that will pin the objects in bakim.keep_in_shared_pool for every startup


select 'DROP TRIGGER ' owner'.'trigger_name';'from dba_triggers where trigger_name like '%KEEP%';

create or replace TRIGGER SYS.db_startup_keep
AFTER STARTUPON DATABASE
BEGIN
sys.spk_keep_islem.keep_all_predefined_objects;
end;
/
7-you can submit a job finding the shared_pool objects for every hour or we can do it while closing the database by the help of a trigger or we can do it both

CREATE OR REPLACE TRIGGER SYS.db_shutdown_keep
BEFORE SHUTDOWN ON DATABASE
BEGIN
sys.spk_keep_islem.find_keep_in_shared_pool;
END;
/

OR /AND

Conn BAKIM

declare
ln_jobno NUMBER;
ls_job VARCHAR2(2000);
begin
ls_job := 'sys.spk_keep_islem.find_keep_in_shared_pool;';
dbms_job.isubmit(1300, ls_job, sysdate, 'TRUNC(sysdate+1)+1/24',false);
dbms_output.put_line('job 'ln_jobno' started..');
commit;
end;
/
8- We can check the pinned objects
select count(*) from bakim.keep_in_shared_pool;
select type,KEPT,COUNT(*),sum(SHARABLE_MEM) FROM v$db_object_cache GROUP BY TYPE,KEPT;
select owner,count(*) from bakim.keep_in_shared_pool group by owner;

SELECT SUBSTR(owner,1,10) Owner,SUBSTR(type,1,12) Type,SUBSTR(name,1,30) Name,executions,sharable_mem Mem_used,SUBSTR(kept' ',1,4) "Kept?"
FROM v$db_object_cache
WHERE 1=1and type in ('PROCEDURE','PACKAGE BODY','PACKAGE')
ORDER BY sharable_mem
/
Note: 101627 is a usefull document