How to determine SGA size

[gnagesh]

Hi Oracle Guru's

my system is having 2GB memory.

my sga is

Total System Global Area 1038995364 Bytes
Fixed size 94116 Bytes
Variable size 317857792 Bytes
Database buffers 719978496 Bytes
Redo Buffers 1064960 Bytes

init.ora parameters are :

DB_BLOCK_BUFFERS = 175776
SHARED_POOL_SIZE = 251135488
LARGE_POOL_SIZE = 614400
JAVA_POOL_SIZE = 20971520
LOG_BUFFER = 1048576

BLOCK SIZE IS 4K.

Can any one suggest me, how to determine the initial SGA size. Some documents are suggesting that SGA should be 1/3-1/2 of the physical memory. My system is running OLTP applications.

Please treat this as an urgent issue. Because I am making changes remotely to my Beijing(CHINA) Database.

Thanks in Advance.

[aarroyob]

Ohhhhh, little SGA ;-) is a joke

Read note 1008866.6 in metalink.

This will help you

Regards

Angel

[julian]

Can any one suggest me, how to determine the initial SGA size. Some documents are suggesting that SGA should be 1/3-1/2 of the physical memory

1/3? Which document. Some people will tell you that the SGA should be 50-60% of the RAM. I would say depends on the system. You will be on the safe side with these numbers but you are also wasiting memory.

As I see your SGA is a bit less than 1G. You say you have 2G RAM. Why waist it?

[gnagesh]

Hi All,

Thanks allot. Now I had increased my SGA to 1.2G, which is 60% of my 2G memory. Is it Okay?

Any recommendations?

Thanks in Advance.

[pipo]

SGA should be sized according to your needs, one cannot give a general rule on SGA sizing :/

it really depends on the activity you have, on how many people work together, and on how much data the manipulate ...

[pando]

Originally posted by pipo
SGA should be sized according to your needs, one cannot give a general rule on SGA sizing :/

it really depends on the activity you have, on how many people work together, and on how much data the manipulate ...

agreed!

if you have 10 instances how are you gonna resize your SGA?

[Sureshy]

Have you done any analysis which warrants an increase in sga size ?

Firstly check the main memmory areas to see whether they are hitting the recommended statistics.

No need to increase if your system is making optimal use of its memmory structures. Below are a few scripts to get you started.


prompt Listing Buffer Cache Hit Ratio
prompt Ideally this should be above 90%
prompt ---------------------------------


column "logical_reads" format 99,999,999,999
column "phys_reads" format 999,999,999
column "phy_writes" format 999,999,999
select a.value + b.value "logical_reads",
c.value "phys_reads",
round(100 * ((a.value+b.value)-c.value) /
(a.value+b.value))
"BUFFER HIT RATIO"
from v$sysstat a, v$sysstat b, v$sysstat c
where
a.statistic# = 38
and
b.statistic# = 39
and
c.statistic# = 40;

prompt
Prompt Listing Get Hit Ratio of Library Cache
Prompt Ideally this should be above 90%
Prompt -------------------------------------

select gethits,gets,(gethitratio*100)
from v$librarycache
where namespace = 'SQL AREA';

prompt
prompt Listing Reloads/Pins Ratio of Library Cache
Prompt Ideally this should be below 1%
prompt ---------------------------------------------

select reloads,pins,(reloads/pins * 100) AS RATIO
from v$librarycache
where namespace = 'SQL AREA';

prompt
prompt Listing Getmisses/Gets ratio of Dictionary Cache
Prompt Ideally this should be below 15%
Prompt ------------------------------------------------

select sum(getmisses),sum(gets),sum(getmisses)/sum(gets) * 100
from v$rowcache;

prompt
Prompt Listing Disk/Memmory Ratio of Sort Area Size
Prompt Ideally this should be below .10
Prompt -------------------------------------------------

SET HEADING ON
SET FEEDBACK OFF
COLUMN name FORMAT a30
COLUMN value FORMAT 99999990

SELECT name, value
FROM v$sysstat
WHERE name IN ('sorts (memory)', 'sorts (disk)');

prompt
prompt
Prompt Listing redo log space request/entries ratio
Prompt Ideally this should not be above 0.0002
prompt ---------------------------------------------

SELECT REQ.VALUE/ENTRIES.VALUE "SPACE REQUEST RATIO"
FROM V$SYSSTAT REQ,V$SYSSTAT ENTRIES
WHERE REQ.NAME='redo log space requests'
AND ENTRIES.NAME='redo entries';