How to determine SGA size
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 7 of 7

Thread: How to determine SGA size

  1. #1
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    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.


    Nagesh

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    Ohhhhh, little SGA ;-) is a joke

    Read note 1008866.6 in metalink.

    This will help you

    Regards

    Angel

  3. #3
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938

    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?


  4. #4
    Join Date
    Aug 2000
    Location
    Singapore
    Posts
    323
    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.
    Nagesh

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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 ...

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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?

  7. #7
    Join Date
    Apr 2001
    Location
    London
    Posts
    725
    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';


    Once you have eliminated all of the impossible,
    whatever remains however improbable,
    must be true.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width