Re: Sizing SHARED_POOL_SIZE

Rauf Sarwar wrote:

> "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

Excellent.

Dan Morgan Received on Thu Jan 16 2003 - 11:18:27 CST