doc 

Name:   sharpool.sql 

Author: Mark Gurry


The following three queries obtain information on the SHARED_POOL_SIZE.

The first query lists the packages, procedures and functions in the 
order of largest first.

The second query lists the number of reloads. Reloads can be very 
damaging because memory has to be shuffled within the shared pool area
to make way for a reload of the object.

The third query lists how many times each object has been executed.

Oracle has provided a procedure which is stored in $ORACLE_HOME/rdbms/admin
called dbmspool.sql The SQL program produces 3 procedures. A procedure
called keep (i.e. dbms_shared_pool.keep) can be run to pin a procedure in
memory to ensure that it will not have to be re-loaded.    

Oracle 7.1.6 offers 2 new parameters that allow space to be reserved for
procedures/packages above a selected size. This gives greater control 
over the avoidance of fragmentation in the SHARED POOL.

See the parameters SHARED_POOL_RESERVED_SIZE and
                            SHARED_POOL_RESERVED_MIN_ALLOC.

They are listed later in this report. 

#
spool sharpool.lis

set linesize 100
set pagesize 999
set heading off
column owner format a10
column name  format a42
column sharable_mem format 99,999,999
column executions   format 999,999,999
column "Pinned" format a14;

set echo off
set feedback off
set verify off

ttitle ' Executions of Objects in the  Shared Pool  - Most Executions First'

select  owner, name||' - '||type name, executions , decode(substr(kept,1,1),'Y', '    ', '<<< Not Pinned') "Pinned"
  from v$db_object_cache
where executions  > 100
  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by executions  desc
/

ttitle ' Loads into Shared Pool  - Most Loads First'

select  owner, name||' - '||type name, loads , sharable_mem from v$db_object_cache
where loads > 3 
  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by loads desc
/

ttitle ' Objects that are KEPT '
select owner, name, kept
from v$db_object_cache
where kept like 'Y%'
 order by owner, name
/

ttitle ' Memory Usage of Shared Pool Order - Biggest First'

select  owner, name||' - '||type name, sharable_mem from v$db_object_cache
where sharable_mem > 10000
  and type in ('PACKAGE', 'PACKAGE BODY', 'FUNCTION', 'PROCEDURE')
order by sharable_mem desc
/

spool off