-- Estimate Shared Pool Size

-- Posted to comp.databases.oracle.server by Rauf Sarwar, Jan 2003

SET SERVEROUT ON
SET VERIFY OFF

DECLARE
   l_uplift CONSTANT NUMBER := 0.3;
   l_numusers        NUMBER := &Number_Of_Concurrent_Users;
   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;
   l_actual_sp       NUMBER;
BEGIN
   DBMS_OUTPUT.ENABLE(20000);

   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 to_number(value)
   INTO l_actual_sp
   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(CHR(10));
   DBMS_OUTPUT.PUT_LINE('Current Shared Pool Size is:
{'||to_char(round(l_actual_sp, 0))||'  BYTES  '||
   'OR  '||to_char(round((l_actual_sp/1048576), 0))||'  MB}');

   DBMS_OUTPUT.PUT_LINE(CHR(10));

   DBMS_OUTPUT.PUT_LINE('For ' || TO_CHAR(l_numusers) || ' concurrent
users, it can be resized to 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}  OR  '||
   '{'|| TO_CHAR(ROUND((l_total_avg + (l_total_avg *
l_uplift))/1048576, 0) ) ||'  and  ' ||
   TO_CHAR(ROUND((l_total_max + (l_total_max * l_uplift ))/1048576 ,0)
) ||'  MB}');
END;
/