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

[dbafreak]

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 apologise ovidius. Somehow I am not conviced with answers such as "it depends...", "its relative...." etc etc.

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.

[ovidius]

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!

[gandolf989]

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.

[ovidius]

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...

[dbafreak]

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"

!!

[TheSlob]

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.

[pando]

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?

[ronnie]

Originally posted by Raminder
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!)

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

[KenEwald]

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.

[ronnie]

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.