DISADVANTAGES of LARGE SHARED POOL and INCREASED DB BLOCK BUFFERS??? - Page 4
DBAsupport.com Forums - Powered by vBulletin
Page 4 of 4 FirstFirst ... 234
Results 31 to 37 of 37

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

  1. #31
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by dbafreak
    So does that mean SGA tuning is as simple as "Hey, just dont bother. If you can afford to finance, just increase Physical RAM, Shared Pool and DB Block Buffers substantially and Bingo you are done with it"
    In short - yes, it is like this. But then again, might not be exactly like this. You demanded an exact answer, but neglect to provide some basic information. Like the version of Oracle you are using. Because the version might be crucial to answer this simple question with a simple answer.

    So the thing is (provided that OS memory management e.g. paging and swapping etc are not an isue here):
    - If you use 8.1.5 or earlier, oversizing your shared pool might lead to severe shared pool latch contention due to the library cache fragmentation. In 8.1.6 and later this isue has been resolved internaly by substatntialy larger number of shared pool chunks free lists.
    - Oversizing buffer cache can not harm your performance in any way.

    So if you at least have a clue what you are doing and you have a lot of physical memory to waste, there is no harm (but also no benefit) to oversize your buffer cache, but with shared pool you should be more carefull - it depends on your database release.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  2. #32
    Join Date
    Mar 2002
    Location
    Mesa, Arizona
    Posts
    1,204
    Jurij, you are 100% right. Thanks for the clarification.

    ovidius, let's be real nice here please.
    "I do not fear computers. I fear the lack of them." Isaac Asimov
    Oracle Scirpts DBA's need

  3. #33
    Join Date
    Apr 2002
    Posts
    5
    The bigger the SGA, especially if it involves a pathalogically high number of buffers in the buffer cache, the more CPU Oracle will expend in maintaining this cache. It may not be huge in the overall scheme of things, but the SGA needs to be maintained, and the bigger it is, the more CPU it will take to maintain it ..

    Don't forget guys! we have three main resources we need to contend with, Physical I/O, Memory and CPU! Don't narrow your focus so much you ignore the others...

  4. #34
    Join Date
    Mar 2002
    Posts
    171
    Originally posted by ronnie

    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!)
    Pardon me Sir. When I meant increase SGA to 2GB, I ofcourse meant that physical RAM will be atleast 4 GB. Sorry for the mis-coception.

    BTW, the thread is gettng more and more interesting. Now we have a new point by Ronnie saying its over load on the CPU. Ronnie, could you please explain us how CPU is overloaded with increased SGA. The CPU comes into picture during the "parsing" and "processing" stages etc. Why will an increase of SGA affect the CPU. Kindly explain.




  5. #35
    Join Date
    Apr 2002
    Posts
    5
    The buffer cache has to be maintained, you need to cycle through the cache finding dirty blocks etc.

    imagine a loop where you had

    for i in 1 .. NUM_BUFFERS
    LOOP
    do stuff;
    END LOOP;


    The more buffers, the more time you spend inside the loop "doing stuff"...


  6. #36
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    Originally posted by Dropbear
    The buffer cache has to be maintained, you need to cycle through the cache finding dirty blocks etc.

    imagine a loop where you had

    for i in 1 .. NUM_BUFFERS
    LOOP
    do stuff;
    END LOOP;
    Well, the fact is that Oracle never do it the way you demonstrated. It never performs "full cache scan".

    It doesn't have to, because it maintains "dirty buffers list". Whenever a buffer is modified it is immediately added to that list, so there is no need to scan all the buffers in the cache.

    Once more: Oversizing the buffer cache can not impact the performance of Oracle server.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  7. #37
    Join Date
    Sep 2001
    Location
    NJ, USA
    Posts
    1,287
    Originally posted by dbafreak
    The CPU comes into picture during the "parsing" and "processing" stages etc. Why will an increase of SGA affect the CPU. Kindly explain.
    If u increase SGA with fixed db buffer poll, that will increase size on sql area and
    may increase CPU utilisation up to 3-6% on search and insert sql statments
    operations (not parse or processing).
    It depend from quality "hash function" that use intermal oracle modules.
    -------------------------------------------------
    This is my guess only.
    If we have large or very large sql area then "hash function with fixed number range"
    may made more "hash errors" and oracle should increase # of operation of
    sequence search of sql statments.

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