Recommendations for setting up SGA

[sybaseguru]

PLease see FAQ section "Recommendations for setting up SGA" for a multi purpose Oracle database"

 

[LKBrwnDBA]

If you already created a database then:
----------------------------------------------
/*
sga_calc.sql
*/

set serverout on

DECLARE

l_uplift CONSTANT NUMBER := 0.3; /* i.e. 30% above calculated */

l_numusers NUMBER DEFAULT 50; /* Change this to a predicted number if not an existing database */
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;

BEGIN

dbms_output.enable(20000);

IF ( l_numusers = 0) THEN
SELECT sessions_highwater
INTO l_numusers
FROM v$license;
dbms_output.put_line('Maximum concurrent users on this database = '
||TO_CHAR(l_numusers));
ELSE
dbms_output.put_line('Calculating SGA for = '
||TO_CHAR(l_numusers)||' concurrent users');
END IF;
dbms_output.new_line;

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;

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('Recommended Shared_pool size 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');

dbms_output.put_line('Recommended Shared_pool size between :'
|| TO_CHAR(ROUND(
(l_total_avg + (l_total_avg * l_uplift)) /(1024*1024), 0) )
||' and '
|| TO_CHAR(ROUND(
(l_total_max + (l_total_max * l_uplift )) /(1024*1024) ,0) )
||' M bytes');
end;
/
--------------------------------------------------------


The person who says it can't be done should not interrupt the person doing it.
-- Chinese proverb

 

[sybaseguru]

Hello,

Thanks for the template. FYI, the FAQ is for creating a new instance not for an already existing one.

 

[sem]

I suppose that you shared pool is a bit oversized. Is it for MTS system with large number of users? I mean that in some cases large shared pool is cramed with unusefull ad hoc sql statements (sqlarea), that even slows down the performance even though both library hit rate and dictionary hit rate are perfect.

Regards, Dima