SQL Script Size System Global Area

Updated : 3-Apr-2002
Version : 2.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

rem This is a sga utilisation script 

set serverout on
 
spool sga.lst  

DECLARE 

l_uplift CONSTANT NUMBER := 0.3;  /* i.e. 30% above calculated */

l_numusers NUMBER DEFAULT 50; /* Change this to a predicted number if not an 					 or set to 0 for actuals */
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('Approximating SGA for = '
		||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;

-- MTS user memory on a per user basis
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';

dbms_output.put_line('AVG MTS Mem on '||to_char(l_numusers)
                            ||' Users: '||to_char(l_avg_uga) );
dbms_output.put_line('MAX MTS Mem on '||to_char(l_numusers)
                            ||' Users: '||to_char(l_max_uga) );

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

dbms_output.put_line('Shared SQL Memory : '||to_char(l_sum_sql_shmem) );

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

dbms_output.put_line('Object Memory : '||to_char(l_sum_obj_shmem) );

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('Current 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('Current 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;
/

spool off

Previous Oracle Version Links

Size System Global Area

Return to Index of SQL Scripts

Home | Company Profile | Services | Contact Us | SQL scripts and tips | Quiz Legal