Code:
--  FILE:   cre_pin.sql
--
--  AUTHOR: Andy Rivenes, [email protected], http://www.appsdba.com
--          Copyright (C) 1998 AppsDBA Consulting
--
--  DATE:   05/26/1998
--
--  DESCRIPTION:
--          Query to create a shared pool pin script.  This script
--          looks for "large" packages in the shared pool and
--          creates a "pin" SQL statement.
--
--          This script is based on the following information:
--
-- Article-ID:         
-- Alias:              OLS:11578136.61
-- Circulation:        PUBLISHED (EXTERNAL)
-- Platform:           GENERIC  Generic issue
-- Subject:            LIBRARY CACHE AND SHARED POOL TUNING
--
-- In Oracle7.3 onwards the best candidates for keeping can be seen by
-- querying the table X$KSMSP to see if there are any chunks in the shared-pool
-- that have the KSMCHSIZ larger than 5K and KSMCHCOM like '%PL/SQL%'.
-- If so then one can identify the object name and owner of this chunk
-- using the following SQL:
--
--   select distinct
--   decode(kglobtyp,0,'CURSOR',7,'PROCEDURE',8,'FUNCTION',9,'PACKAGE',
--               11,'PACKAGE BODY',12,'TRIGGER',13,'TYPE',14,'TYPE BODY','OTHER')
--    ||' - '||kglnaown||'.'||kglnaobj "Eligible PL/SQL objects"
--   from x$kglob
--   where kglobhd4 in
--    (select  ksmchpar from x$ksmsp
--       where ksmchcom='PL/SQL MPCODE' and ksmchsiz>5120)
-- 
-- If you are 'keeping' PL/SQL objects today and migrate to 7.3 or higher
-- there is no need to re-assess the list of objects that you are keeping.
--
--          
--  REQUIREMENTS:
--         Must be run as SYS                 
--		
--  MODIFICATIONS:
--          A. Rivenes, 10/24/1999, Added the code from libcache_pin.sql
--                                  to create a more accurate list.
--
--
SET PAGESIZE 0;
SET FEEDBACK off;
SET VERIFY off;
SPOOL pin.sql;
--
SELECT DISTINCT
       'execute dbms_shared_pool.keep('''||dbc.owner||'.'||dbc.name||''');'
  FROM v$db_object_cache dbc,
       ( SELECT DISTINCT DECODE(kglobtyp,0,'CURSOR',
                                7,'PROCEDURE',
                                8,'FUNCTION',
                                9,'PACKAGE',
                               11,'PACKAGE BODY',
                               12,'TRIGGER',
                               13,'TYPE',
                               14,'TYPE BODY',
                               'OTHER') type,
                kglnaown,
                kglnaobj
           FROM x$kglob
          WHERE kglobhd4 IN ( SELECT ksmchpar
                                FROM x$ksmsp
                               WHERE ksmchcom = 'PL/SQL MPCODE'
                                 AND ksmchsiz > 5120 ) ) chnk
 WHERE dbc.owner = chnk.kglnaown
   AND dbc.name = chnk.kglnaobj
   AND dbc.type = chnk.type
   AND dbc.type = 'PACKAGE'
/
--
SPOOL off;
-- EXIT;
HTH

Sameer