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