Toolkit for dynamic marking of Library Cache objects as Kept

Instructions
Execution Environment:
SQL*Plus, iSQL*Plus

Access Privileges:
Requires SYS

Usage:
sqlplus “/as sysdba” @PIND_install.sql

Instructions:
– copy&paste bellow appended script into file and run
– make sure startup/shutdown triggers are (“_system_trig_enabled=TRUE” – enabled by default)
– make sure the database jobs are enabled (job_queue_processes=1+)

PROOFREAD THIS SCRIPT BEFORE USING IT! Due to differences in the way text
editors, e-mail packages, and operating systems handle text formatting (spaces,
tabs, and carriage returns), this script may not be in an executable state
when you first receive it. Check over the script to ensure that errors of
this type are corrected.The script will produce an output file named [outputfile].
This file can be viewed in a browser or uploaded for support analysis.

Description
· Description: Tool for automated keeping/taking snapshot of objects upon startup/shutdown.
· Successful installation checks:
SQL> select owner,object_name,object_type,status from dba_objects where owner=’SYS’ and (object_name like ‘PIND%’ or object_name like ‘GPIND%’) ;
OWNER OBJECT_NAME OBJECT_TYPE STATUS
——————– —————————— ——————- ——-
SYS GPIND$DB_OBJECT_CACHE VIEW VALID
SYS PIND$DB_OBJECT_CACHE VIEW VALID
SYS PIND_KEPT_OBJ TABLE VALID
SYS PIND_OBJ PACKAGE VALID
SYS PIND_OBJ PACKAGE BODY VALID
SYS PIND_ON_SHUT TRIGGER VALID
SYS PIND_ON_START TRIGGER VALID

· Output file naming: PIND_install.lst

References
Script description and some notes releated to ORA-4031 and DBMS_SHARED_POOL are avialable in
Note 311689.1 ORA-4031 and DBMS_SHARED_POOL.KEEP remarks and PIND toolkit overview .

Script

———————————————————————————————–
———————————— Script Starts here ————————————–
———————————————————————————————–
/*
PIND
PIn Dynamically – Tool for automated pinning of often used objects into library cache.
Snapshot of most used objects is taken using shutdown trigger and found objects pinned using
startup trigger. Cursors are pinned during instance lifetime via DBMS_JOB facility.
Mechanism protects against false snapshots and against marking significant part of Shared Pool as kept.
Standard DBMS_SHARED_POOL package is utilized for keeping.
Basic idea is based on Note:101627.1. Tested on 9.2, 10.1

Note: This requires startup/shutdown triggers to be enabled using parameter “_system_trig_enabled=TRUE”.
(Enabled by default.)

author: Daniel Simon (daniel.x.simon@oracle.com)
version: 3.1 – 12.1.2007 (PIND_KEPT_OBJ PADDRESS RAW(4)->(8) for 64bit platforms)
3.0 – 17.5.2005
2.0 – 1.3.2005
1.0 – 31.12.2004 (created)
*/

spool pind_install.lst

set serverout on size 1000000

/*
whenever sqlerror exit
*/

Prompt Creating required underlying views

create or replace view GPIND$DB_OBJECT_CACHE (
INST_ID,
OWNER,
NAME,
DB_LINK,
NAMESPACE,
TYPE,
SHARABLE_MEM,
LOADS,
EXECUTIONS,
LOCKS,
PINS,
KEPT,
CHILD_LATCH,
HASH_VALUE,
ADDRESS,
PADDRESS,
CRSR_PLAN_HASH_VALUE,
kglobt02
) as
select inst_id,kglnaown,kglnaobj,kglnadlk, decode(kglhdnsp,0,’CURSOR’,1,’TABLE/PROCEDURE’,2,’BODY’,3,’TRIGGER’, 4,’INDEX’,5,’CLUSTER’,
6,’OBJECT’,13,’JAVA SOURCE’,14,’JAVA RESOURCE’, 15,’REPLICATED TABLE OBJECT’,16,’REPLICATION INTERNAL PACKAGE’, 17,
‘CONTEXT POLICY’,18,’PUB_SUB’,19,’SUMMARY’,20,’DIMENSION’, 21,’APP CONTEXT’,22,’STORED OUTLINE’,23,’RULESET’,24,’RSRC PLAN’,
25,’RSRC CONSUMER GROUP’,26,’PENDING RSRC PLAN’,27,’PENDING RSRC CONSUMER GROUP’, 28,’SUBSCRIPTION’,29,’LOCATION’,30,
‘REMOTE OBJECT’, 31,’SNAPSHOT METADATA’,32,’JAVA SHARED DATA’,33,’SECURITY PROFILE’, ‘INVALID NAMESPACE’),
decode(bitand(kglobflg,3),0,’NOT LOADED’,2,’NON-EXISTENT’,3,’INVALID STATUS’, decode(kglobtyp, 0,’CURSOR’,1,’INDEX’,
2,’TABLE’,3,’CLUSTER’,4,’VIEW’, 5,’SYNONYM’,6,’SEQUENCE’,7,’PROCEDURE’,8,’FUNCTION’,9,’PACKAGE’,10, ‘NON-EXISTENT’,
11,’PACKAGE BODY’,12,’TRIGGER’,13,’TYPE’,14,’TYPE BODY’, 15,’OBJECT’,16,’USER’,17,’DBLINK’,18,’PIPE’,19,’TABLE PARTITION’,
20,’INDEX PARTITION’,21,’LOB’,22,’LIBRARY’,23,’DIRECTORY’,24,’QUEUE’, 25,’INDEX-ORGANIZED TABLE’,
26,’REPLICATION OBJECT GROUP’, 27,’REPLICATION PROPAGATOR’, 28,’JAVA SOURCE’,29,’JAVA CLASS’,30,’JAVA RESOURCE’,
31,’JAVA JAR’, 32,’INDEX TYPE’,33, ‘OPERATOR’,34,’TABLE SUBPARTITION’,35,’INDEX SUBPARTITION’,
36, ‘REPLICATED TABLE OBJECT’,37,’REPLICATION INTERNAL PACKAGE’, 38,’CONTEXT POLICY’,39,’PUB_SUB’,40,’LOB PARTITION’,
41,’LOB SUBPARTITION’, 42,’SUMMARY’,43,’DIMENSION’,44,’APP CONTEXT’,45,’STORED OUTLINE’,46,’RULESET’, 47,’RSRC PLAN’,
48,’RSRC CONSUMER GROUP’,49,’PENDING RSRC PLAN’, 50,’PENDING RSRC CONSUMER GROUP’,51,’SUBSCRIPTION’,52,’LOCATION’,
53,’REMOTE OBJECT’,54,’SNAPSHOT METADATA’,55,’IFS’, 56,’JAVA SHARED DATA’,57,’SECURITY PROFILE’,’INVALID TYPE’)),
kglobhs0+kglobhs1+kglobhs2+kglobhs3+kglobhs4+kglobhs5+kglobhs6, kglhdldc,kglhdexc,kglhdlkc,kglobpc0,
decode(kglhdkmk,0,’NO’,’YES’),kglhdclt, kglnahsh, KGLHDADR, KGLHDPAR, kglobt30, kglobt02
from x$kglob
/

create or replace view PIND$DB_OBJECT_CACHE (
OWNER,
NAME,
DB_LINK,
NAMESPACE,
TYPE,
SHARABLE_MEM,
LOADS,
EXECUTIONS,
LOCKS,
PINS,
KEPT,
CHILD_LATCH,
HASH_VALUE,
ADDRESS,
PADDRESS,
CRSR_PLAN_HASH_VALUE,
kglobt02
) as
select OWNER , NAME , DB_LINK , NAMESPACE , TYPE , SHARABLE_MEM , LOADS , EXECUTIONS , LOCKS , PINS , KEPT , CHILD_LATCH, HASH_VALUE, ADDRESS, PADDRESS, CRSR_PLAN_HASH_VALUE, kglobt02
from GPIND$DB_OBJECT_CACHE where inst_id = USERENV(‘Instance’)
/

prompt Creating Auxiliary Table (snapshot of most used objects)

create table PIND_KEPT_OBJ (INST_NUM number,PADDRESS RAW(8), HASH_VALUE number, CRSR_PLAN_HASH_VALUE number,
owner varchar2(64),TYPE VARCHAR2(28),NAME VARCHAR2(1000),cardinality number,
sharable_mem number,executions number,loads number ,kept VARCHAR2(3) ,enc_type VARCHAR2(2),collected date)
storage (initial 32k next 32k)
/

prompt Creating PIND_OBJ Pacakge (functional part)

create or replace PACKAGE PIND_OBJ is
————————————————
—— take snapshot of objects to be kept
————————————————
function SNAP return number ;
procedure SNAP ;

————————————————
—— pin objects captured by SNAP procedure
————————————————
function keep (p_type in varchar2 default null, p_max_perct_kept in number default 5) return number ;
procedure keep (p_type in varchar2 default null, p_max_perct_kept in number default 5);

procedure sched_keepc (p_iter_curr in number, p_iter_max in number, p_interval_hrs in number, p_start_date in date);

end;
/

show error

create or replace PACKAGE BODY PIND_OBJ is
————————————————
—— take snapshot of objects to be kept
—— return number of objects we have found to be pinned
————————————————
function SNAP return number as pragma AUTONOMOUS_TRANSACTION;

— Cardinality explains ‘weight’ of the object. We keep objects with highest weight first.
cursor objs is
select USERENV(‘Instance’) INST_NUM,PADDRESS, HASH_VALUE, CRSR_PLAN_HASH_VALUE, owner,type,decode(type,’CURSOR’,substr(name,1,30)||’…’,name) name, sharable_mem*LOADS cardinality, sharable_mem,
executions,loads,kept, decode(type,’CURSOR’,’C’,’TRIGGER’,’R’,’TYPE BODY’,’T’,’SEQUENCE’,’Q’,’JAVA CLASS’,’JC’,’P’) enc_type, sysdate collected
from pind$db_object_cache
— Keep Objects meeting one or more of following criteria.
where — a) cursors are very dynamic (20* more) than other => allow 10 reloads if not kept yet. If KEPT do keep again only if used at least 2 times
type=’CURSOR’ and PADDRESS!=ADDRESS and ( (kept=’NO’ and loads>10) or (kept=’YES’ and executions>1))
or — b) Other types of objects reLoaded at least 2 times (loads = 2+)
type in (‘PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’TRIGGER’,’TYPE BODY’,’FUNCTION’) and ((kept=’NO’ and loads>1) or (kept=’YES’ and executions>0))
or — c) or objects kept but executions not incremented when used (never unpinned – be carefull)
type in (‘SEQUENCE’,’JAVA CLASS’) and owner not in (‘SYS’,’SYSTEM’) and (kept=’YES’ or (kept=’NO’ and loads>1));
— Cardinality limits
avg_old_crdnl number;
avg_new_crdnl number;
— number of objects found to be pinned
ret_chosen number :=0;
BEGIN
select nvl(avg(cardinality),0) into avg_old_crdnl from PIND_KEPT_OBJ;
select avg(sharable_mem*LOADS) into avg_new_crdnl from pind$db_object_cache where type in (‘PACKAGE’,’PACKAGE BODY’,’PROCEDURE’,’TRIGGER’,’TYPE BODY’,’SEQUENCE’,’FUNCTION’,’CURSOR’,’JAVA CLASS’);
— has database been really used since last restart? (elimination of false data after more restarts)
if ((1000*avg_new_crdnl)>avg_old_crdnl)
then
delete from PIND_KEPT_OBJ where INST_NUM=USERENV(‘Instance’);
commit;
for o_rec in objs
loop
insert into PIND_KEPT_OBJ (INST_NUM,PADDRESS,HASH_VALUE,CRSR_PLAN_HASH_VALUE,OWNER,TYPE,NAME,CARDINALITY,SHARABLE_MEM,EXECUTIONS,LOADS,KEPT,ENC_TYPE,COLLECTED)
values (o_rec.INST_NUM,o_rec.PADDRESS, o_rec.HASH_VALUE, o_rec.CRSR_PLAN_HASH_VALUE, o_rec.owner , o_rec.TYPE, o_rec.name,
o_rec.cardinality, o_rec.sharable_mem,o_rec.executions, o_rec.loads, o_rec.kept, o_rec.enc_type, o_rec.collected);
ret_chosen := ret_chosen + 1; –object counter
end loop;
commit;
else
— probably we are called just after restart without any activity between start and stop
dbms_output.put_line(‘PIND: not representative sample: avg_old_crdnl=’||avg_old_crdnl||’ 1000*avg_new_crdnl=’||1000*avg_new_crdnl);
null;
end if;
return ret_chosen ; –number of objects to be kept
end;

————————————————
—— pin objects captured by SNAP procedure
—— return number of objects we have pinned
————————————————
function KEEP (p_type in varchar2 default null, p_max_perct_kept in number default 5) return number as pragma AUTONOMOUS_TRANSACTION;
type krec_type is record (what varchar2(256), enc_type varchar2(2), mem_size number);
krec krec_type ;
TYPE krecCurTyp IS REF CURSOR RETURN krec_type ;
krecCur krecCurTyp;
— (maximum) amount of memory (we allow) to be kept
kept_size_max number;
kept_size number :=0;
— number of objects found to be pinned
ret_chosen number :=0;
BEGIN
— find out kept limits and current amount
select sum(bytes)*(p_max_perct_kept/100) into kept_size_max from v$sgastat where pool=’shared pool’;
select nvl(sum(sharable_mem),0) into kept_size from pind$db_object_cache where kept=’YES’;
if (p_type = ‘C’) or (p_type = ‘c’) then
open krecCur for select s.PADDRESS||’.’||s.HASH_VALUE what,’C’ enc_type, s.sharable_mem mem_size from PIND_KEPT_OBJ k, pind$db_object_cache s /*v$sql*/ where k.INST_NUM=USERENV(‘Instance’) and k.type=’CURSOR’
and s.PADDRESS!=s.ADDRESS and s.HASH_VALUE=k.HASH_VALUE and s.CRSR_PLAN_HASH_VALUE = k.CRSR_PLAN_HASH_VALUE and s.kept=’NO’ order by k.sharable_mem*k.loads desc;
else
open krecCur for select decode(enc_type,’JC’,owner||’.”‘||name||'”‘,owner||’.’||name) what, enc_type, sharable_mem mem_size from PIND_KEPT_OBJ where INST_NUM=USERENV(‘Instance’) and TYPE!=’CURSOR’ order by sharable_mem*loads desc;
end if;
loop
begin
fetch krecCur into krec;
exit when krecCur%NOTFOUND;
— we can’t keep more memory than kept_size_max bytes (5% of whole shared_pool)
kept_size := kept_size + krec.mem_size;
exit when kept_size > kept_size_max;
–dbms_output.put_line(‘Keeping :’||krec.what||’, ‘|| krec.enc_type);
SYS.dbms_shared_pool.keep(”|| krec.what || ”, ” || krec.enc_type || ”);
ret_chosen := ret_chosen + 1; –object counter
exception when others then
dbms_output.put_line(‘PIND: unable to pin ‘||krec.what||’, ‘|| krec.enc_type); dbms_system.ksdwrt(1,’PIND: unable to pin ‘||krec.what||’, ‘|| krec.enc_type);
dbms_output.put_line(‘ Error: ‘||SQLERRM); dbms_system.ksdwrt(1, ‘ Error: ‘||SQLERRM);
end;
end loop;
close krecCur;
sys.dbms_shared_pool.keep(‘SYS.STANDARD’,’P’);
sys.dbms_shared_pool.keep(‘SYS.DIUTIL’,’P’);
return (ret_chosen+2) ; –number of objects to be kept
END;

procedure sched_keepc (p_iter_curr in number, p_iter_max in number, p_interval_hrs in number, p_start_date in date)
as pragma AUTONOMOUS_TRANSACTION;
job number;
d_dummy date;
begin
— continue only if we haven’t be bounced since last run (new job submitted already then)
select null into d_dummy from v$instance where to_char(startup_time,’dd.mm.yyyy hh24:mi:ss’)=to_char(p_start_date,’dd.mm.yyyy hh24:mi:ss’);
— desired action done here
dbms_output.put_line(‘PIND: Starting action. Got parameters: ‘||p_iter_curr||’ ‘||p_iter_max||’ ‘||p_interval_hrs);
keep(‘C’);
dbms_output.put_line(‘PIND: Finished action’);
— schedule next run
if (p_iter_max>p_iter_curr) –otherwise we are done and nothing is needed
then
dbms_job.submit(job=>job,what=>’PIND_OBJ.sched_keepc(‘||(p_iter_curr+1)||’,’||p_iter_max||’,’||p_interval_hrs||’,to_date(”’||to_char(p_start_date,’dd.mm.yyyy hh24:mi:ss’)||”’,”dd.mm.yyyy hh24:mi:ss”));’,next_date=>sysdate+(p_interval_hrs/24),instance=>userenv(‘Instance’));
commit;
–dbms_system.ksdwrt(1,’PIND: job=’||job||’: PIND_OBJ.sched_keepc(‘||(p_iter_curr+1)||’,’||p_iter_max||’,’||p_interval_hrs||’,to_date(”’||to_char(p_start_date,’dd.mm.yyyy hh24:mi:ss’)||”’,”dd.mm.yyyy hh24:mi:ss”)); next:’||to_char(sysdate+(p_interval_hrs/24),’dd.mm. hh24:mi:ss’)||’,instance=>’||userenv(‘Instance’));
dbms_output.put_line(‘PIND: job=’||job||’: PIND_OBJ.sched_keepc(‘||(p_iter_curr+1)||’,’||p_iter_max||’,’||p_interval_hrs||’,to_date(”’||to_char(p_start_date,’dd.mm.yyyy hh24:mi:ss’)||”’,”dd.mm.yyyy hh24:mi:ss”)); next:’||to_char(sysdate+(p_interval_hrs/24),’dd.mm. hh24:mi:ss’)||’,instance=>’||userenv(‘Instance’));
end if;
exception when NO_DATA_FOUND then
–dbms_system.ksdwrt(1,’PIND: db bounced before finished jobs. Ending.’);
null; — database has been bounced before we managed to do all attempts => nothing needed
when OTHERS then
raise_application_error(-20002,’PIND: got error’||SQLERRM);
end;

— command line interface
procedure snap is
dummy number;
begin
dummy := snap;
end;

procedure keep (p_type in varchar2 default null, p_max_perct_kept in number default 5) is
dummy number;
begin
dummy := keep(p_type, p_max_perct_kept );
end;

end; — PIND_PKG
/

show error

prompt Creating Triggers Calling Functional Part upon Startup/Shutdown

create or replace trigger PIND_ON_SHUT
BEFORE SHUTDOWN ON DATABASE
DECLARE
obj_pinned number :=0;
BEGIN
dbms_system.ksdwrt(2,’PIND: collect list of objects to be kept – start’);
obj_pinned := pind_obj.snap;
dbms_system.ksdwrt(2,’PIND: collect list of objects to be kept – finished (‘||obj_pinned||’ objects found)’);
END;
/

create or replace trigger PIND_ON_START
AFTER STARTUP ON DATABASE
DECLARE
obj_pinned number :=0;
st_time date;
BEGIN
dbms_system.ksdwrt(2,’PIND: keep objects in Shared pool – start’);
obj_pinned := pind_obj.keep;
select startup_time into st_time from v$instance;
pind_obj.sched_keepc(1,4,6,st_time);
dbms_system.ksdwrt(2,’PIND: keep objects in Shared pool – finished (‘||obj_pinned||’ objects pinned)’);
END;
/

—–
— checkup
—-

alter session set nls_date_format = ‘dd.mm.yyyy hh24:mi:ss’;

Prompt Doing After Installation Checkup
col OBJECT_NAME for a30
select owner,object_name,object_type,status from dba_objects where owner=’SYS’ and (object_name like ‘PIND%’ or object_name like ‘GPIND%’) ;

prompt # of Objects to be kept by PIND (should be 0 after installation)
select inst_num,count(*) from PIND_KEPT_OBJ group by inst_num;

prompt # of Objects Kept at the Moment
select type,count(*) from v$db_object_cache where kept=’YES’ group by type
union
select ‘Total’,count(*) from v$db_object_cache where kept=’YES’;

prompt Collecting list of objects to be kept
exec pind_obj.snap;
prompt Marking objects to be kept
exec pind_obj.keep;
exec pind_obj.keep(p_type=>’c’);

prompt # of Objects newly pinned (and object details)
select inst_num,count(*) from PIND_KEPT_OBJ group by inst_num;
select inst_num,type,count(*),sum(sharable_mem) from pind_kept_obj group by inst_num,type;

prompt # of Objects Kept at the Moment
select type,count(*) from v$db_object_cache where kept=’YES’ group by type
union
select ‘Total’,count(*) from v$db_object_cache where kept=’YES’;

prompt Cursor pinning facility
show parameters job
col what for a60
select job,what,last_date,next_date,INSTANCE from dba_jobs;

spool off
———————————————————————————————–
———————————— Script Ends here —————————————-
———————————————————————————————–

.

Bookmarks Admin Profile Feedback Sign Out Help

Copyright © 2006, Oracle. All rights reserved. Legal Notices and Terms of Use | Privacy Statement

Sobre Alexandre Pires

ORACLE OCS Goldengate Specialist, OCE RAC 10g R2, OCP 12C, 11g, 10g , 9i e 8i - Mais de 25 anos de experiência na área de TI. Participei de projetos na G&P alocado na TOK STOK, EDINFOR alocado na TV CIDADE "NET", 3CON Alocado no PÃO DE AÇUCAR, DISCOVER alocado na VIVO, BANCO IBI e TIVIT, SPC BRASIL, UOLDIVEO alocado no CARREFOUR e atualmente na ORACLE ACS atendendo os seguintes projetos: VIVO, CLARO, TIM, CIELO, CAIXA SEGUROS, MAPFRE, PORTO SEGURO, SULAMERICA, BRADESCO SEGUROS, BANCO BRADESCO, BASA, SANTANDER, CNJ, TSE, ELETROPAULO, EDP, SKY, NATURA, ODEBRESHT, NISSEI, SICREDI, CELEPAR, TAM, TIVIT, IBM, SMILES, CELEPAR, SERPRO,OKI,BANCO PAN, etc
Esse post foi publicado em BUFFERS, PLSQL TUNING e marcado . Guardar link permanente.

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s