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.
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.
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!
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.
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.
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]
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... :D
So, is very dangerous to fall in this situation.
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]
Paging means moving inactive processes from memory to disk. Swapping is the moving of active processes from memory to disk.Quote:
Originally posted by dbafreak
BTW, can someone please explain what is memory paging? I am naive in this area. Thanks in advance.
You get the idea which is more dangerous, right?
:D
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...
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
Originally posted by dbafreak:
Quote:
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!)
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??
The larger the SGA is, the more it does NOT fit into the phisical memory ... :D
OOps...I think you have (perhaps all) have GROSSLY misunderstood me.Quote:
Originally posted by ovidius
The larger the SGA is, the more it does NOT fit into the phisical memory ... :D
I meant "Are there any performance issues if I increase the SGA. I OFCOURSE ASSUMED THAT MY RAM WILL BE INCREASED ACCORDINGLY :-) :-)"
You missunderstood me, then, :D. 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.Quote:
Originally posted by ovidius
You missunderstood me, then, :D. 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.
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 :-)
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.Quote:
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?
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.
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...
I apologise ovidius. Somehow I am not conviced with answers such as "it depends...", "its relative...." etc etc.Quote:
Originally posted by ovidius
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...
I have a precise question with a precise scenario and example. The answer if not very precise will atleast have to be close. This is my understanding.
Oh, I think you want a very precise scenario, that will assure you your final results ok in any situation. I'd like to have it, but without some more vague methodology that exists in manuals, there is no such a thing!
But there are very precise explained the basic concepts there. The solution is not a "wonder" scenario, but appliance of this basic concepts all at once, that is a very difficult task, because there are many opposite necesities needing to be harmonized.
I do not like neither words like "harmonise", "equilibrium", "balance", "more", "less", etc. but the word "relative" I used in a very precise manner, trying to explain that all that values have impact one on each other.
Believe me, you are looking for something that do not exists. The best way is to learn very well the concepts and to accumulate experience. Good luck!
I'll just throw in my two cents. I usually try to keep the shared pool at least 60meg and as mucha as 100meg depending on physical ram. Then I look at to total physical ram and make sure that the sga takes up about 60% of the total physical memory. Although my databases mostly run on windows and are small by Oracle standards.
I think that we can all agree that the best improvements in database performance will happen because of SQL tuning not sga tuning. As long as Oracle can fit everything it needs in physical memory, then the sga size is ok.
Yes, Gandulf989, but here we try to answer dbafreak at his questions, not at your questions, nor at your 2 cents. If you have nothing to say, is enough to say nothing...
:D
What really amused me at the end of this thread is -- Despite the fact that this is one of the best boards for DBA support and despite the fact that there are so many experienced Gurus -- No one was able to explain the flip side of increased SGA.
Only one statement was made on the flip side -- "Paging" and I already said my issue is with increased Physical RAM. So "Paging" is ruled out.
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"
!!
dbafreak, just make sure increasing the SGA wont cause your OS to page/swap and that there is still room for other process to execute. other than that, there is nothing wrong with increasing your SGA.
noone gives you a precise an answer because there is not a rule of thumb, according to you if the ratio is bad then your sga is badly sized but that is not always the case, base your tuning on hit ratio is an old method for Oracle 6 and 7
I also told you that most probably is you will increase your latch contention if you size too big your sga
I have a linux production box with 13gb ram and 2 instances running but hey I am not gonna give 5GB sga to each, why should I if with current settings, 1GB SGA each, the instances are running smooth with good response time?
Quote:
Originally posted by Raminder
Originally posted by dbafreak:
Quote:
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!)
I was wondering if the machine has multiple disks on which disk will the sga be paged out. In short on which disk does the virtual memory reside.
Thanks
I hope I'm interpreting the original question correctly: How large is too large? Here's my experience with the shared_pool and db_block_buffers.
shared_pool... If you're spending more time searching the shared_pool for code, it's too big. We had performance problems on a database where reducing the shared_pool and modifying code to uses bind vars actually increased the performance.
db_block_buffers .. If you've got the memory, you might as well use it. This is a good place to put it to use. Just be careful not to use too much so you can't scale your sessions. The newer versions of the db use much larger amounts of per session memory.
This is what I experienced a few days ago with shared pool.
RAM=512 MB
Virtual Memory = 512 MB
This was a test system and somebody just bumped up
the shared pool size from 200MB to 800 MB.
Things were working fine for sometime and I did not notice the change since this was a test/development machine.
Then one day the CPU started reaching 100% usage and database and the O/S itself was very very slow.
Rebooted the server but did not help.
The problem Hard Disk was running put of space and virtual memory could not expand to accomodate the SGA.
Decreased the SGA to 300MB and things were fine.
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.Quote:
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"
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, you are 100% right. Thanks for the clarification.
ovidius, let's be real nice here please.
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...
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.Quote:
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!)
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.
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"...
Well, the fact is that Oracle never do it the way you demonstrated. It never performs "full cache scan".Quote:
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;
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.
If u increase SGA with fixed db buffer poll, that will increase size on sql area andQuote:
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.
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.