--SQL ScriptSQL Script
--Size System Global Area
--Posted : 3-November-1998
--Version : 1.0
--Description
--This script will calculate the size of the SGA of an existing database based 
--upon the current usage. This script is particularly useful for getting a SGA 
--size of an existing development database and scaling up the figures for a 
--production database that does not yet exist. Run this script on the development 
--database and alter the number of users constant (l_numusers) to be what is 
--expected on the production database for the number of concurrent users. It 
--assumes that there is 30% free memory on top of the calculated. This can be 
--altered by changing the l_uplift PL/SQL constant.
--Parameters
--The script default to using the maximum number of concurrent users which have 
--logged on, but you can amend this script to calculate for any number by changing 
--the PL/SQL constant l_numusers. 
--SQL Source
set serverout on
@DBA/FREESGA.SQL
DECLARE 
l_uplift CONSTANT NUMBER := 0.3;  /* i.e. 30% above calculated */

l_numusers NUMBER DEFAULT &USERS; /* Change this to a predicted number if not an 					  existing database */
l_avg_uga NUMBER;
l_max_uga NUMBER;
l_sum_sql_shmem NUMBER;
l_sum_obj_shmem NUMBER;
l_total_avg NUMBER;
l_total_max NUMBER;
BEGIN
dbms_output.enable(20000);

IF ( l_numusers = 0) THEN
   SELECT sessions_highwater
   INTO l_numusers
   FROM v$license;
   dbms_output.put_line('Maximum concurrent users on this database = '
		||TO_CHAR(l_numusers));
ELSE
   dbms_output.put_line('Calculating SGA for = '
		||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;

SELECT 
    avg(value)*l_numusers
   ,max(value)*l_numusers
INTO l_avg_uga, l_max_uga
FROM v$sesstat s, v$statname n
WHERE s.statistic# = n.statistic#
AND n.name = 'session uga memory max';

SELECT
    sum(sharable_mem) INTO l_sum_sql_shmem
FROM v$sqlarea;

SELECT 
    sum(sharable_mem) INTO l_sum_obj_shmem
FROM v$db_object_cache;

l_total_avg := l_avg_uga + l_sum_sql_shmem + l_sum_obj_shmem; 
l_total_max := l_max_uga + l_sum_sql_shmem + l_sum_obj_shmem; 

dbms_output.put_line('Recommended Shared_pool size between :' 
	|| TO_CHAR(ROUND(l_total_avg  + (l_total_avg * l_uplift), 0) ) 
	||' and '
	|| TO_CHAR(ROUND(l_total_max + (l_total_max * l_uplift), 0) ) 
	||' bytes');

dbms_output.put_line('Recommended Shared_pool size between :' 
	|| TO_CHAR(ROUND(
		(l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) ) 
	||' and '
	|| TO_CHAR(ROUND(
		(l_total_max + (l_total_max * l_uplift )) /(1024*1024) ,0) ) 
	||' M bytes');
end;
/