SQL Script List Unpinned Objects in DB Cache

Updated : 20-Mar-2002
Version : 1.0

Description

List details of all packages, procedures, functions that are in the db_object_cache and therefore have been used recently, but are not pinned. This will provide a list of potential candidates for pinning in the shared pool.

Parameters

None.

SQL Source

col owner form a8
col name form a26
col type form a12
col sharable_mem  form 9999999 heading "Size"

spool cache.lst

SELECT owner
,name
,type 
,sharable_mem      
,executions  
,loads    
FROM v$db_object_cache
  WHERE type in ('TRIGGER','PROCEDURE','PACKAGE BODY','PACKAGE')
   AND  executions > 0
   AND kept='NO'
  ORDER BY executions desc,
           loads desc,
           sharable_mem desc
/

spool off

Return to Index of SQL Scripts

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