DISADVANTAGES of LARGE SHARED POOL and INCREASED DB BLOCK BUFFERS???
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 4 123 ... LastLast
Results 1 to 10 of 37

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

  1. #1
    Join Date
    Mar 2002
    Posts
    171
    As we have seen, in most cases of SGA tuning, we tend to increase the shared pool size or increase the number of db block buffers.

    What are the filp side disadvantages of simply having a large shared pool/increased no. db block buffers??

    Can someone please explain some precise points of what the disadvatage is and why? Really appreciate your time and patience.


  2. #2
    Join Date
    Aug 2001
    Location
    Waterloo, On
    Posts
    547
    I can't think of any disadvantage except that you might page SGA out of 'real' memory (which will make the performance worse) or make applications other than Oracle suffer on your machine.

    Raminder Singh

    Oracle Certified DBA: Oracle 8i, 9i


    Mail me at raminderahluwalia@rediffmail.com.

  3. #3
    Usually you will use SGA_MAX_SIZE, for preventing paging of your oracle zone. The SGA_MAX_SIZE will never be overpassed. If you do not make correct correlations between SGA_MAX_SIZE, buffer cache, log buffer and shared pool, the variable zone (and I think the log buffer is there) will be experiencing problems.
    This is my oppinion. But I'd like to read some more ellaborated, from gurus here. Thanks in advance!
    ovidius over!

  4. #4
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    What I can think of is that over time you may have due to the large shared pool a lot of chunks in memory, thus fragmentation can get real bad. You may end up with the free chunks getting smaller. Consequently the free list chunks will become very long and performance might suffer.

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  5. #5
    Join Date
    Mar 2002
    Posts
    171
    This is a little interesting. Still not much of constructive replies so far. Because if increasig SGA does not have some major disadvantages, its just the cost then for the memory.

    "Fragmentation" inside the SGA - that sounds interesting to me !?!

    BTW, can someone please explain what is memory paging? I am naive in this area. Thanks in advance.

  6. #6
    Oh, I forgot to make an observation: what do you mean by "having a large shared pool/increased no. db block buffers"??? This have no meaning in absolute values. This is only valid when you are talking about correlation between them and SGA_MAX_SIZE/total phis. memory or between them themselves. There is no meaning in "I have 1Gb shared pool. Is it too much?"...
    And Julian was talking about another important link, or correlation, not between themselves, but between the memory size of some zones and THE NEEDED memory size for them.
    I can correlate very well all that values if I allocate 2Gb SGA_MAX_SIZE on my working machine, where I have one test database. But this db does almost NOTHING! Will there be much fragmentation? I do not think so. Will there be LOST memory? That is my business. Will happen something wrong with database? NO!
    But if I allocate more than 1 Gb for shared pool, etc. and a liitle over 1Gb for SGA_MAX_SIZE, the variable size could be too small, and I COULD have some problems.


    I think there is no universal solution for tuning, tough there could be an universal solution for methodology of tuning. And I think the absolute values themselves tell nothing.

    Oh, dbafreak, right now I've seen your reply. So, I need to make an observation again. I was talking about jeopardizing the variable size by non-correlated parameters for memory.

    [Edited by ovidius on 04-05-2002 at 02:49 AM]
    ovidius over!

  7. #7
    Paging. Windows make paging, UNIX make swapping. As I know. There are no very large differences between them, from oracle point of view.
    It is about the model virtual memory, the phisical memory, and the disk swap file. Paging/swaping is occured when the OS is moving your memory pages to the disk due to lack of needed phis memory. Swap differs from paging by some uninteresting details, like how is the context created/recreated, how a "sleeping" process is moved entirely to disk or only partial etc. And that craps differs between OSes
    NOW:
    Think to this situation. You have 1Gb phis memory, 1Gb swap, total virtual memory 2Gb. You have 800Mb SGA_MAX_SIZE (or, worse, more!). But there are the OS and another applications that eat memory. So, partial, your Oracle zone arrives on disk. It is in virtual memory space, ok, but is ON DISK, is paged, and you could have 99% hit ratio of everything, but your db moving very slow. And your disk moving desperately its heads...

    So, is very dangerous to fall in this situation.
    ovidius over!

  8. #8
    Join Date
    Mar 2002
    Posts
    171
    ovidus: Thanks for the reply.

    Where does the "swap" memory sit? In the Hard disk? and what do you mean when you say "And your disk moving desperately its heads... ". Could you please substantiate.

    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?

    Please explain how paging can happen with an example and why at all "Paging" occurs. I mean if SGA can hold enough buffers to handle the appliation, why the need to swap the data into the disk (virtual memory)??? I am very new to OS understandings. Kindly explain.

    [Edited by dbafreak on 04-05-2002 at 03:09 AM]

  9. #9
    Join Date
    Jun 2001
    Location
    Helsinki. Finland
    Posts
    3,938
    Originally posted by dbafreak
    BTW, can someone please explain what is memory paging? I am naive in this area. Thanks in advance.
    Paging means moving inactive processes from memory to disk. Swapping is the moving of active processes from memory to disk.

    You get the idea which is more dangerous, right?

    Oracle Certified Master
    Oracle Certified Professional 6i,8i,9i,10g,11g,12c
    email: ocp_9i@yahoo.com

  10. #10

    Let me be more specific.

    Paging occurs when a process needs a page of memory that is no longer in real memory, but in virtual memory. The block must be read ("paged") in from disk. Swapping is similar to paging, except that the entire process is removed from memory. If there are too many processes running at a time, swapping may increase to an unacceptable level.
    Both swaping and paging require adequate disk space to temporary hold the memory blocks on disk. These files are I/O intensive, so they also need to be considered when balancing I/O.
    Some OS such as NT/w2000 DO NOT USE SWAPPING, but only paging, and most others are swapping only as a last resort when the amount of free memory is getting unacceptable low.

    On some OSes, the Oracle DBA can lock the SGA into real memory by setting the LOCK_SGA parameter to TRUE. (9i)
    Be aware!, this isn't the universal solution, even if aplicable! You could degrade the performance in other areas of OS that could majorly impact on Oracle! Do not forget, Oracle is functioning on the OS...
    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