DBAsupport.com Forums - Powered by vBulletin
Page 2 of 4 FirstFirst 1234 LastLast
Results 11 to 20 of 37

Thread: DISADVANTAGES of LARGE SHARED POOL and INCREASED DB BLOCK BUFFERS???

  1. #11
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    well in oracle 8 if you set excessive SGA you will increase latch contention, in 8i they fixed the prob for shared pool by increasing LRU list from 2 to 255, for data buffer I think problem still exists

    for example if I have 2MB data buffer it's mcuh faster to scan it and find the block I need than if I have 2GB data buffer

  2. #12
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    Originally posted by dbafreak:
    Say Oracle is the only application on my Server. Say I have 512MB RAM. I get occasional bad cache hits. So instead if increasing the shared pool size eachtime or instead of increasing the db block buffers each time (though occasional), if I make my SGA (I mean shared pool and increase of db block buffers) very large say 2GB (Assuming I can support the cost) -- how does the performance affect?


    Well, if you have 512 MB RAM and you make SGA 2 GB, there will be very serious consequences on the database.
    RAM is the 'Real' memory available on the system, which is 512 MB. However, since you have allocated much more than this for SGA, your OS will page the SGA out of real memory into virtual memory, which is nothing but space on hard disk. So, everytime you issue command for Oracle, the disk and disk controllers come into picture. Working onto real memory is much faster (that is why we have SGA in the first place!)

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #13
    Join Date
    Mar 2002
    Posts
    171
    Thanks for the explainations. The only thing I didn't understand is: Why does paging increase with increase in the SGA size? How is it related??

  4. #14
    The larger the SGA is, the more it does NOT fit into the phisical memory ...
    ovidius over!

  5. #15
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by ovidius
    The larger the SGA is, the more it does NOT fit into the phisical memory ...
    OOps...I think you have (perhaps all) have GROSSLY misunderstood me.

    I meant "Are there any performance issues if I increase the SGA. I OFCOURSE ASSUMED THAT MY RAM WILL BE INCREASED ACCORDINGLY :-) :-)"


  6. #16

    Smile

    You missunderstood me, then, . Because if you put enough RAM to really support your SGA, then the paging does not grow...
    I told you once, the absolute values does not tell anything alone. All values have sense only related to each other and to the needs.
    ovidius over!

  7. #17
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by ovidius
    You missunderstood me, then, . Because if you put enough RAM to really support your SGA, then the paging does not grow...
    I told you once, the absolute values does not tell anything alone. All values have sense only related to each other and to the needs.
    So lets then say I have 512 MB RAM. My SGA is 200 MB. Hit Ratios are bad occasionally and I am forced to increase the Shared Pool and db block buffers once in a while.

    What I do is - I increase the RAM to 1GB. Increase the SGA to 512MB (Substantially increase Shared Pool and DB Block Buffers). By this I am almost positive that chances of bad hit ratios are very remote.

    How will this now affect my performance? Back to Sq One :-)

  8. #18
    If you have a good piece of hell of activity there, then your performance will increase, maybe dramatically if before the system was lacking in resources.
    If your activity was not so large to "cover" your previously 512 Mb, then mainly nothing happens, because even if you will have a very very very bad SGA fragmentation after many many many time (the frag does not come instantaneously), the decrese of performance due to this is probably less than 1%.
    This is my oppinion.
    Any others?
    ovidius over!

  9. #19
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by ovidius
    If you have a good piece of hell of activity there, then your performance will increase, maybe dramatically if before the system was lacking in resources.
    If your activity was not so large to "cover" your previously 512 Mb, then mainly nothing happens, because even if you will have a very very very bad SGA fragmentation after many many many time (the frag does not come instantaneously), the decrese of performance due to this is probably less than 1%.
    This is my oppinion.
    Any others?
    So if this the scenario, there is no harm in increasing the SGA! Hmm...interesting. Rather really amusing cos after all Majority of the SGA tuning is solved by a simple resolution - increase SGA!! I am somehow skepitcal. We are for sure missing something...something very important.

    I am posititve there are some adverse effects by increased SGA, but really dont know what. Someone must eye-open us here. Gurus, pls advise.

  10. #20
    Third time , I have to tell you. The issue when you tune the SGA is not the SGA itself. The SGA is as big as you can afford without entering in paging or swaping state. And no problem if you can afford a lot.
    The issue is how you ORGANIZE your SGA, by this meaning shared pool, db buffer cache, log buffer, large pool, java pool etc. It is a relative issue. Tunning some of the parts is easy. Tunning other is not so easy. So...
    ovidius over!

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