Calculating SGA


--Calculating SGA for = 50 concurrent users
/*
Recommended Shared_pool size between :1282070563 and 3624922596 bytes
Recommended Shared_pool size between :1223 and ##
*/
DECLARE 
l_uplift CONSTANT NUMBER := 0.3; 
/* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 50; 
/* Change ths to a predicted number 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) ) ;
end;
/
Advertisements

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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: