thx much
cosmin, April 20, 2004 - 12:53 pm UTC
<<tuning is not done by the buffer cache, it is done by
doing as little work as you really need>>
that's the general idea, however when one does aggregates over aggregates over aggregates etc over tens of millions of records, it pays to have as much of that data cached. Admitedly, most of our processes do little physical reads and mostly consistent gets however there are some convoluted processes (including possible analytic functions) that span a wide dataset and that's our concern.
April 20, 2004 - 3:27 pm UTC
not necessarily.
in fact -- if you use parallel query, sometimes we do a checkpoint first (flush the cache) so we can just do reads instead of asking "hmm, is what we want in the buffer cache, if not go to disk"
cache is good, cache is a tool, cache is not the end all be all.
don't use a tool until need for tool is identified! (eg: don't take medicine until you get sick)
buffer cache advisory
cosmini, April 20, 2004 - 3:58 pm UTC
...the buffer cache advisory in OEM, at times (of heavy use, with more simultaneous users, some of which running batch processes) says we need an extra 500-700mb ram (on top of the 1.5gb already allocated, to attain much less physical reads. Other times it's OK. Nonetheless, the performance of our system under 9i is a magnitude faster than under 8i. I can only expect even faster performance under 10g but we're not there yet.
Would the "extended buffer cache" support degrade performance somewhat or make management that much harder that it should not be considered?
regarding your comments, true, "the proof is in the pudding". If I've learned one good thing on this site (and I've learned a lot of 'em!) is never to take anybody's word for granted but devise testing scenarios to prove or disprove ideas, myths, etc :-)
it still amazes me that so many Oracle authors nowadays still write things without backing them up ("hey, if you use a 16k block size you get 75% hit ratio but if you use a 32k block size you get 99% hit ratio") :-(
thx again, and will continue to do more testing...
April 21, 2004 - 11:56 am UTC
extended buffer cache is just a tad harder to set up -- i've not heard of it having a negative overall effect on performance from anyone.
lowered mapped base
Christo Kutrovsky, April 26, 2004 - 12:15 pm UTC
Just wanted to mention that there is another way to get more SGA (if you really thing it is needed), by lowering your mapped base address. You can get to 2.6 gb for SGA at the expense of having less *addressable* memory available for PGA. At least you can adjust where you want the boundery.
There are some great papers on metalink.