doc

    Name:   spoolsiz.sql

    Author: Mark Gurry


The following script assumes that you have set your
 SHARED_POOL_RESERVED_MIN_ALLOC to 2500. 

#


select sum(sharable_mem)
  from v$db_object_cache
 where sharable_mem > 2500;

doc

This is the total memory of packages, procedures, triggers, views, 
functions and other objects stored in the database.

#

select sum(sharable_mem)
  from v$sqlarea
where sharable_mem > 2500;

doc

This output is the amount of storage required for SQL. If you add the
 two values together, you have an approximate sizing for the 
SHARED_POOL_RESERVED size. It is best to add on some contingency, 
say 40%  for factors such as dynamic SQL which is not counted in the 
second query, and all statements not current running. 
The same methods can be used to calculate the total shared pool size.
You simply take away the where sharable_mem > 2500. 
When you are estimating the total shared pool size, you have to also 
take into account user cursors, which also use memory. You need about 
250 bytes of shared pool memory per user for each cursor that the user
 has open. To obtain the total cursor usage, run the following query or 
you can get the figure from the  library cache  from the V$SGASTAT table 
which amounts to the same thing. 
#

select sum(250 * user_opening)
from  v$sqlarea;

doc

The SHARED_POOL_SIZE must also include memory for the dictionary cache 
(usually around 4 Meg) and a collection of areas required to compile 
database objects and other miscellaneous areas shown in the V$SGASTAT.

#