Rem
Rem $Header: admin_pin_plsql.sql 02-feb-2005.01:00:42 jsadras Exp $
Rem
Rem admin_pin_plsql.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      admin_pin_plsql.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    jsadras     02/01/05 - report on shared pool usage 
Rem    rpinnama    09/26/02 - rpinnama_fix_repos_sdk_violations
Rem    rpinnama    09/26/02 - Created
Rem

DECLARE
BEGIN
  EMD_MAINTENANCE.PIN_PLSQL;
END;
/

prompt shared pool statistics

column mb format 999.99
column kept_mb format 999.99 heading "Kept|(MB)"
column not_kept_mb format 999.99 heading "Not kept|(MB)"
compute sum of mb on report
break on report
select name , round(bytes/(1024*1024),2) MB 
from v$sgastat  a
where pool = 'shared pool'
order by MB ;

prompt  db object cache:sharable memory

compute sum of kept_mb , not_kept_mb on report

SELECT namespace,
       sum(decode(kept,'YES',sharable_mem,0))/1048576 KEPT_MB ,
       sum(decode(kept,'NO',sharable_mem,0))/1048576 NOT_KEPT_MB ,
       count(*) count
  FROM v$db_object_cache
GROUP BY namespace   ;