SQL Script Pin All Packages

Updated : 20-Mar-2002
Version : 2.0

Description

Pin all packages that exist in the database above a minimum size. This script should be run on database startup so that all large or frequently accessed packages are pinned.

Parameters

obj_size - Minumum size of objects to pin in bytes.
exec - Minumum number of executions.

SQL Source

spool pinpkgs.lst

set serveroutput on size 1000000

PROMPT
PROMPT Ensure you have run the following as SYS
PROMPT $ORACLE_HOME/rdbms/admin/dbmspool.sql
PROMPT $ORACLE_HOME/rdbms/admin/prvtpool.plb
PROMPT
PROMPT 
PROMPT Listing all objects in shared_pool over 100k
PROMPT

REM execute dbms_shared_pool.sizes(150);

REM
REM list objects not pinned, consider pinning large packages or frequently
REM used packages

ACCEPT obj_size prompt 'Enter size of objects to pin (in bytes) : ' 
ACCEPT exec prompt 'Enter No Executions Limit to pin : ' 

DECLARE
   CURSOR get_pkgs IS 	SELECT owner, name, sharable_mem , executions
				FROM 	v$db_object_cache 
				WHERE	
                                  (sharable_mem > NVL('&obj_size',10000000)
                                  OR
                                  executions > NVL('&exec',10000000)
                                  )
				AND 	type IN 
				('PACKAGE', 'FUNCTION', 'PROCEDURE')   
				AND	kept = 'NO';

BEGIN
   FOR pkgs_rec IN get_pkgs LOOP
       dbms_shared_pool.keep(pkgs_rec.owner
                      || '.'
                      || pkgs_rec.name, 'P');
       dbms_output.put_line ('Pinned Package '
                            ||pkgs_rec.owner
                            || '.'
                            || pkgs_rec.name
			    || ' of size '
			    || pkgs_rec.sharable_mem ||' bytes.'
			    || ' executions '
			    || pkgs_rec.executions 
              );
               
   END LOOP;
END;
/ 

spool off

Previous Oracle Version Links

Pin All Packages

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal