SET SERVEROUTPUT ON
SET ECHO OFF
SET FEEDBACK ON
SET PAGES 80
SET LINES 132

REM
REM || Title : show_pinned_packages.sql
REM ||
REM || Purpose : This script will report packages that are currently in the shared Pool.
REM || If there are objects which are executed on a regular basis. Consider pinning them.
REM ||
REM || Release No : 1
REM ||
REM || Variables : None
REM ||
REM || Schema : system
REM ||
REM ||

CLEAR BREAKS

PROMPT
PROMPT If there are objects which are executed regularly consider pinning them into
PROMPT Shared Pool.
PROMPT

column executions format 999,999,999 HEADING 'Executions' JUSTIFY l;
column Mem_used format 999,999,999 HEADING 'Memory|Used' JUSTIFY l;
column Owner format A15 HEADING 'Owner' JUSTIFY l;
column Type format A15 HEADING 'Object|Type' JUSTIFY l;
column Name format A20 HEADING 'Object|Name' JUSTIFY l;

SELECT SUBSTR (owner, 1, 10) owner,
SUBSTR (TYPE, 1, 12) type,
SUBSTR (name, 1, 20) name,
executions,
sharable_mem mem_used,
SUBSTR (kept || ' ', 1, 4) "Kept"
FROM v$db_object_cache
WHERE TYPE IN ('TRIGGER', 'PROCEDURE', 'PACKAGE BODY', 'PACKAGE')
ORDER BY owner,
kept,
executions DESC

SPOOL show_pinned_objects.lst
/
SPOOL OFF

SET SERVEROUTPUT ON
SET ECHO ON