CREATE TABLE GC_PINNING
(OBJ_OWNER VARCHAR2(50), OBJ_NAME VARCHAR2(50),OBJ_TYPE VARCHAR2(50),
OBJ_SIZE NUMBER,PINNING_DATE DATE DEFAULT SYSDATE,obj_LOADS NUMBER);
insert into GC_PINNING
(OBJ_OWNER , OBJ_NAME,OBJ_TYPE,
OBJ_SIZE,PINNING_DATE ,obj_LOADS)
(select
OWNER , NAME,TYPE,
sharable_mem,
sysdate
,LOADS
from v$db_object_cache where
type in (‘PACKAGE’,’PACKAGE BODY’,’TRIGGER’,’SEQUENCE’,’PROCEDURE’)
and nvl(sharable_mem,0)>0 and kept=’NO’);
CREATE OR REPLACE PROCEDURE GC_PINNER AS
/*
gc_Pinner by Helio Dias.com
Subject : pinning object that had been saved on GC_PINNING table
June 24, 2008,
*/
cursor c_pinning is
select OBJ_OWNER||’.’||OBJ_NAME OBJ_NAME,OBJ_TYPE from GC_PINNING;
V_NAME VARCHAR2(50);
V_TYPE VARCHAR(50);
begin
open c_pinning;
loop
fetch c_pinning into v_name,v_type;
exit when c_pinning%notfound;
IF V_TYPE=’FUNCTION’
THEN V_TYPE:=’P’;
END IF;
IF V_TYPE=’SEQUENCE’
THEN V_TYPE:=’Q’;
END IF;
IF V_TYPE=’TRIGGER’
THEN V_TYPE:=’R’;
END IF;
V_TYPE:=SUBSTR(V_TYPE,1,1);
dbms_shared_pool.keep(V_NAME,V_TYPE);
end loop;
END;
/
Oracle Logbook 
Pin anonymous block:
View v$sqlarea – anomymous block is type 47
DECLARE
/* test */
begin
;end;
/
Miladin
http://oraclue.com/
set serveroutput on;
declare
/* DO_NOT_KEEP_ME */
addr varchar2(20);
hash number;
cursor getahv is
select address,hash_value from v$sqlarea where command_type=47
and sql_text not like '%DO_NOT_KEEP_ME%'
and sql_text like '%test%';
begin
open getahv;
loop
fetch getahv into addr,hash;
exit when getahv%notfound;
dbms_output.put_line(addr||to_char(hash));
dbms_shared_pool.keep(addr||','||to_char(hash),'C');
end loop;
end;
/
Comment by oraclue — October 6, 2008 @ 6:37 pm |