Calculate SGA size – current db usage

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.

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.

set serverout on
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 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;

IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
dbms_output.put_line(‘Maximum concurrent users on this database = ‘
dbms_output.put_line(‘Calculating SGA for = ‘
||TO_CHAR(l_numusers)||’ concurrent users’);

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 = ‘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 :’
(l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||’ and ‘
(l_total_max + (l_total_max * l_uplift )) /(1024*1024) ,0) )
||’ M bytes’);


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: