Re: Sizing SHARED_POOL_SIZE

Steve Timko wrote:

> I'm a moron how would u run this?
>
> -s
>
> DA Morgan wrote:
> > 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
> >

In SQL*Plus on Windows ... Ctrl-C -> Ctrl-V press the <Enter> key.

In any environment by saving as a text file and executing with @.

Daniel Morgan Received on Fri Jan 17 2003 - 00:42:12 CST