REM RMINC_pinning_status.sql
REM
REM Copyright 2006, All Rights Reserved, Reed-Matthews, Inc.
REM Concurrent Program Name:  Pinning Status
REM
REM 1.  Gives a rough idea of how well pinning is working for you.  If you get a lot of results,
REM then you may need to make adjustments to PIND   
REM 
REM 2.  Tells you which objects you are pinning.  You can look at the loads and executions 
REM and sharable memory to gauge if you are pinning effectively
REM
REM 3.  Checks to see if you are running the PIND toolset.  If you don't have PIND objects, then
REM you aren't running the toolset.  You should read up on this toolset and consider using it.
REM

set pagesize 62
set linesize 180
set feedback on
set heading on
column owner format a20
column name format a40
column type format a15

ttitle left "Unpinned Objects That Are Reloading More Than Once, Executing More Than Once, And Have High Sharable Memory"
select substr(owner, 1,20) owner,substr(name,1,40) name, substr(type,1,15) type, to_char(sharable_mem/1024,'9,999.9') "SPACE(K)",loads,executions execs from v$db_object_cache where kept='NO' and 
type in ('PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','TYPE BODY','FUNCTION','SEQUENCE','CURSOR','JAVA CLASS')
and loads > 1 and executions >1 and sharable_mem/1024 > 25
order by loads desc;

set newpage 0
ttitle "Pinned Objects Not Owned By SYS or SYSTEM"
select substr(owner, 1,20) owner,substr(name,1,40) name, substr(type,1,15) type, to_char(sharable_mem/1024,'9,999.9') "SPACE(K)",
loads,executions execs from v$db_object_cache where kept='YES' and
type in ('PACKAGE','PACKAGE BODY','PROCEDURE','TRIGGER','TYPE BODY','FUNCTION','SEQUENCE','CURSOR','JAVA CLASS')
and owner not in ('SYS','SYSTEM')
order by owner,name;

set heading off
set feedback off
ttitle left "Status of PIND"
select 'Are you running PIND?' from dual;
set verify on
set heading on 
set feedback on
set newpage 0
select owner,object_name,object_type,status from dba_objects where owner='SYS' and
 (object_name like 'PIND%' or object_name like 'GPIND%');