set showmode off echo off
set heading on pagesize 24 timing off feedback off termout on linesize 80
set verify off space 3
rem
rem Script: shared_pool_size.sql
rem Purpose: Estimate the size for the shared pool for a representative
rem    sample time.  Before running this, edit your initSID.ora file to
rem    change the value of shared_pool_size to a very large number (maybe
rem    a fourth or more of your system's available memory, depending on
rem    how many other instances and processes that you have running that
rem    are also using memory), then shutdown and startup your database
rem    and let it run for a representative time (like all day or when a
rem    large batch job is running that you want to accomodate).  Use the
rem    estimated size as a guideline for the value for shared_pool_size,
rem    changing that parameter to the estimated size or the original size
rem    and doing another shutdown/startup to put the value into effect. 
rem
rem Author: Stephen Rea
rem Released: 10/26/98
rem
select sum(a.spspv) "Packages/Views", sum(a.spssql) "SQL Statements",
   sum(a.spsusr) "SQL Users", round((sum(a.spspv) + sum(a.spssql) +
      sum(a.spsusr)) * 2.5,-6) "Estimated shared_pool_size"
from (select sum(sharable_mem) spspv, 0 spssql, 0 spsusr from v$db_object_cache
   union all select 0, sum(sharable_mem), 0 from v$sqlarea where executions > 5
   union all select 0, 0, sum(250 * users_opening) from v$sqlarea) a;
!echo
set space 1 timing on feedback 6 verify on echo on showmode both