How to calculate SGA Size ?
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: How to calculate SGA Size ?

  1. #1
    Join Date
    Oct 2001
    Posts
    127
    How to calculate SGA Size.

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    the parameteres which most affect the SGA size are :
    db_block_size : size of an oracle (or data) block, in bytes
    db_block_buffers : number of database buffers, each the size of db_block_size
    log_buffer : number of bytes in the redo log buffer
    shared_pool_size : self explicit, in bytes

    if you do sho sga under SQL+, you'll see the SGA size and what it's made of. you can also look in v$parameter for above parameters and compute your SGA size

  3. #3
    Join Date
    Oct 2001
    Posts
    127
    Only sga part of the database remains in the RAM.If the RAM of the server is 1gb .Can we have two databases having the same SGA on a single server.
    SGA size of the database is as below.
    Total System Global Area 965519388 bytes
    Fixed Size 75804 bytes
    Variable Size 397840384 bytes
    Database Buffers 567525376 bytes
    Redo Buffers 77824 bytes

  4. #4
    Join Date
    Oct 2000
    Posts
    467
    Well..you need to know that from 1GB available memory, the OS takes some part of it. So your combined SGA for both the databases should be less then 1GB
    Vinit

  5. #5
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    wooaaww, you should REALLY decrease db_block_buffers !!!
    I dunno how big your instance is and how many connections you have, but having a SGA size which is about the physical RAM size of the server is performance-killing ...
    you can perfectly have multiple instances with any SGA size, but the important thing is that the total of the SGA sizes should not be more than about half to 60% maximum of the physical RAM ...

  6. #6
    Join Date
    Oct 2001
    Posts
    127
    As of now ,sga size nearly 50% of the RAM,but if we i am having two databases ,with the same SGA size it will hamper
    the performance ,or it will be ok.

  7. #7
    Join Date
    Oct 2000
    Posts
    467
    As far as the figures posted by you , your SGA is around 89% of your total RAM. To bring up your 2nd instance, you have to reduce the SGA for your current database to 50% less. Monitor your db's performance before you bring up the 2nd instance. As pipo said - total combined SGA should be 30-40% less then total RAM.

    Vinit

  8. #8
    Join Date
    Oct 2001
    Posts
    127
    My Database server is having 4GB RAM.I think pipo said that you can have as many instances on the server.
    My question now is ,if i am having two databases on one server ,so the combined SGA should be 50 to 60 % or SGA of one database should be 50 to 60 %.

  9. #9
    Join Date
    Jun 2001
    Posts
    316

  10. #10
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    it's the total size.
    Oracle says it should be 30 to 40%, but you can go up to 50-60%, nevertheless be aware that NT is totally unable to properly manager memory, so it is advisable not to go over 60% which is already a big percentage.

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