Re: Sizing SHARED_POOL_SIZE

Nice bit of SQL.

But don't take the answer as gospel (nothing against Rauf here, it's just that all sorts of things conspire to affect the appropriate size for the Shared Pool, not just the average number of concurrent users). But as a starting point, this is OK.

Regards
HJR "Rauf Sarwar" <rs_arwar_at_hotmail.com> wrote in message news:92eeeff0.0301151520.7f8d00a5_at_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 - 18:31:57 CST