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
>
Received on Thu Jan 16 2003 - 13:22:35 CST
