Caching Objects in Shared Pool

Introduction:
This document is about KEEPing frequently used database objects like PROCEDURES, PACKAGES, SEQUENCES, TRIGGERS and CURSORS in the SHARED_POOL of SGA. Caching/KEEPing frequently
used objects in SHARED POOL will help improve the database performance.

Tested Environment: Oracle 9.2, Windows 2000 Server.

Connect to the database as sys:-
SQL> connect sys/**** as sysdba

Step#1: Create a table sp_obj_tab to hold the objects that need to be cached:-

SQL> CREATE TABLE sys.sp_obj_tab (
      objownr VARCHAR2(64),
      objname VARCHAR2(1000),
      objtype CHAR(1),
      listdte DATE,
      keepdte DATE,
      remarks CHAR(30));

Step#2: Take a look at script shared_pool_mgmt.SQL and run it, which will create a package called shared_pool_mgmt:-

SQL> @shared_pool_mgmt.SQL

Step#3: Create the below triggers which runs during database startup and shutdown:-

SQL> CREATE OR REPLACE TRIGGER db_shutdown_sp_obj_list
      BEFORE SHUTDOWN ON DATABASE
      BEGIN
      sys.SHARED_POOL_MGMT.list_obj_table;
      END;
      /

SQL> CREATE OR REPLACE TRIGGER db_startup_sp_obj_keep
      AFTER STARTUP ON DATABASE
      BEGIN
      sys.SHARED_POOL_MGMT.keep_obj_startup;
      END;
      /

Step#4: Schedule a dbms job that runs at regular intervals, the below once runs at 5 pm every day:-

SQL> VARIABLE JOBNO NUMBER;

SQL> BEGIN
      DBMS_JOB.SUBMIT(JOB=>:JOBNO,
      WHAT=>'shared_pool_mgmt.keep_obj_periodic;',
      NEXT_DATE=>TRUNC(SYSDATE)+1+17/24,
      INTERVAL=>'TRUNC(SYSDATE)+1+17/24');
      COMMIT;
     END;
     /

SQL> PRINT JOBNO;

---

Below is the script, mentioned in Step#2, copy and paste it in a file called shared_pool_mgmt.SQL :-

CREATE OR REPLACE PACKAGE shared_pool_mgmt AS
---------------------------------------------------
---- Name: SHARED_POOL_MGMT
---- Schema: SYS
---- Created by: BabuMani
---- Created on: Dec-05-2006
-- Description: This package helps database
-- administrators to KEEP frequently used database
-- objects like PROCEDURES, PACKAGES, SEQUENCES,
-- TRIGGERS and CURSORS in the SHARED_POOL of SGA.
---------------------------------------------------
PROCEDURE list_obj_table;
-- PROCEDURE LIST_OBJ_TABLE: Identifies the top 10
-- objects based on various system resource
-- consuming factors and writes them in a table
-- called SP_OBJ_TAB. This table is populated
-- during database shutdown. This procedure is run
-- using database shutdown trigger (BEFORE
-- SHUTDOWN ON DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_startup;
-- PROCEDURE KEEP_OBJ_STARTUP: Reads the objects
-- listed in table SP_OBJ_TAB during database
-- startup and KEEP them (except CURSORS)in
-- SHARED_POOL. This procedure is run using
-- database startup trigger (AFTER STARTUP ON
-- DATABASE).
---------------------------------------------------
PROCEDURE keep_obj_periodic;
-- PROCEDURE KEEP_OBJ_PERIODIC: It refreshes the
-- list of objects in table SP_OBJ_TAB and KEEP
-- them (including CURSORS) in SHARED_POOL.
-- Suggested to run this procedure at scheduled
-- intervals.
---------------------------------------------------
END shared_pool_mgmt;
/

CREATE OR REPLACE PACKAGE BODY shared_pool_mgmt AS

PROCEDURE list_obj_table IS
PRAGMA AUTONOMOUS_TRANSACTION;
 BEGIN
  DELETE FROM sys.sp_obj_tab;
  COMMIT;
  INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
  SELECT DISTINCT owner,name,DECODE(type,'TRIGGER','R','P'),SYSDATE,'Listed to Keep in Shared Pool'
    FROM v$db_object_cache
   WHERE type IN ('PACKAGE','PROCEDURE','PACKAGE BODY','TRIGGER')
     AND loads >= 1 AND executions > 0
  UNION
  SELECT DISTINCT owner,name,'Q',SYSDATE,'Listed to Keep in Shared Pool'
    FROM v$db_object_cache
   WHERE type = 'SEQUENCE'
     AND loads >= 1 ;
  INSERT /*DO NOT PIN*/ INTO sys.sp_obj_tab (objownr,objname,objtype,listdte,remarks)
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY executions DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY loads DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY buffer_gets DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY disk_reads DESC)
         WHERE ROWNUM<=10
  UNION
      SELECT * FROM
       (SELECT address||','||hash_value,SUBSTR(sql_text,1,40),'C',SYSDATE,'Listed to Keep in Shared Pool'
          FROM v$sqlarea
         WHERE sql_text NOT LIKE '%/*DO NOT PIN*/%'
           AND loads > 0
           AND executions > 0
      ORDER BY sharable_mem DESC)
         WHERE ROWNUM<=10;
  COMMIT;
 END list_obj_table;

 PROCEDURE keep_obj_startup IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  own VARCHAR2(64);
  nam VARCHAR2(1000);
  typ VARCHAR2(1);
  CURSOR cur1 IS
  SELECT objownr, objname, objtype FROM SYS.sp_obj_tab WHERE objtype<>'C';
 BEGIN
  OPEN cur1;
   LOOP
    FETCH cur1 into own, nam, typ;
    EXIT WHEN cur1%NOTFOUND;
    SYS.DBMS_SHARED_POOL.KEEP(''|| own || '.' || nam || '',''||typ||'');
    UPDATE sys.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
    COMMIT;
   END LOOP;
  CLOSE cur1;
 END keep_obj_startup;

 PROCEDURE keep_obj_periodic IS
  PRAGMA AUTONOMOUS_TRANSACTION;
  own VARCHAR2(64);
  nam VARCHAR2(1000);
  typ VARCHAR2(1);
  CURSOR cur2 IS
  SELECT objownr, objname, objtype FROM SYS.sp_obj_tab;
 BEGIN
  shared_pool_mgmt.list_obj_table;
  OPEN cur2;
   LOOP
    FETCH cur2 INTO own, nam, typ;
    EXIT WHEN cur2%NOTFOUND;
    IF typ='C' THEN
     SYS.dbms_shared_pool.keep(''|| own ||'',''||typ||'');
    ELSE
     SYS.dbms_shared_pool.keep(''|| own || '.' || nam || '',''||typ||'');
    END IF;
    UPDATE SYS.sp_obj_tab SET remarks='Listed n Kept in Shared Pool', keepdte=SYSDATE WHERE objownr=own AND objname=nam AND objtype=typ;
    COMMIT;
   END LOOP;
  CLOSE cur2;
 END keep_obj_periodic;

END shared_pool_mgmt;
/

---

Below are some Handy-Dandy queries:-

COLUMN pool FORMAT A25

SELECT pool||(' -- Total') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE pool IS NOT NULL GROUP BY pool
UNION
SELECT pool||(' --- Free') POOL,ROUND(SUM(bytes)/1024/1024) MB FROM v$sgastat WHERE name='free memory' AND pool IS NOT NULL GROUP BY pool;

SELECT type,COUNT(*) COUNT FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;

SELECT DECODE(objtype,'R','Trigger','P','Package','C','Cursor','Q','Sequence',objtype) OBJTYPE, COUNT(*) COUNT FROM sp_obj_tab GROUP BY objtype ORDER BY 2 ;

COMPUTE SUM LABEL 'TOTAL ' OF COUNT ON REPORT
COMPUTE SUM LABEL 'TOTAL ' OF SHARABLE_MEM_K ON REPORT
BREAK ON REPORT

SELECT type,COUNT(*) COUNT, round(sum(SHARABLE_MEM)/1024) SHARABLE_MEM_K FROM v$db_object_cache WHERE kept='YES' GROUP BY type ORDER BY 2 ;

Library Cache Hit Ratio:

COMPUTE SUM LABEL 'TOTAL' OF PINHITS ON REPORT
COMPUTE SUM LABEL 'TOTAL' OF PINS ON REPORT
BREAK ON REPORT
  SELECT namespace
       , pins
       , pinhits
       , reloads
       , invalidations
    FROM v$librarycache
ORDER BY namespace;

CLEAR COMPUTE
CLEAR BREAK

  SELECT sum(pinhits) pinhits,
         sum(pins) pins,
         round((sum(pinhits)/sum(pins))*100,2) LIBRARY_CACHE_HIT_RATIO
    FROM v$librarycache
ORDER BY namespace;

Dictionary Cache (Row Cache) Hit Ratio:

column parameter format a25
column RATIO format 999.9
column UPDATES format 999,999,999

  SELECT parameter
       , SUM(gets) gets
       , SUM(getmisses) getmisses
       , 100*SUM(gets - getmisses) / SUM(gets) RATIO
       , SUM(modifications) UPDATES
    FROM v$rowcache
   WHERE gets > 0
GROUP BY parameter;

SELECT ROUND((SUM(gets - getmisses - fixed)) / SUM(gets)*100,2) "ROW CACHE" FROM v$rowcache;

---

Reference:
Oracle9i Database Performance Tuning Guide and Reference - Chapter 14 - Memory Configuration and Use -
Configuring and Using the Shared Pool and Large Pool