Script – One approach
set serverout on
set ver off
set timing off
prompt Calcolo Della Shared Pool
prompt
DECLARE
l_uplift CONSTANT NUMBER := 0.3; /* i.e. 30% above calculated */
l_numusers NUMBER DEFAULT 0; /* Change this 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(14);
l_total_max NUMBER(14);
pool_size varchar2(512);

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;


select DECODE(SUBSTR(value,LENGTH(VALUE)),’M’,SUBSTR(value,1,LENGTH(VALUE)-1)*1048576,value)
into pool_size
from v$parameter
where name=’shared_pool_size’;

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(‘Shared_pool attuale :’ || pool_size);

end;
/