Oracle Logbook

June 25, 2008

Using DBMS_SHARED_POOL.KEEP TO PINNING

Filed under: Uncategorized — Helio Dias @ 6:03 pm

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;
/

1 Comment »

  1. 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 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: