Re: Sizing SHARED_POOL

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 15 Jan 2003 15:20:36 -0800
Message-ID: <92eeeff0.0301151520.7f8d00a5@posting.google.com>

"Stephan" <test_at_test.com> wrote in message news:<0OiV9.20$dd3.1716_at_nlnews00.chello.com>...
> Hi,
>
> W2000/ORA8.1.7
>
> Can someone tell me how to determine a 'correct' value for the init.ora
> value: SHARED_POOL_SIZE ?
>
> Thanks
> Stephan

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;
/

Regards
/Rauf Sarwar Received on Wed Jan 15 2003 - 17:20:36 CST

Re: Sizing SHARED_POOL_SIZE