Re: DB Buffer Cache Size

Hi Howard,

> Even if we were to accept your statement at face value, you've totally
> ignored, forgotten or not realised that nothing in life comes free. As the
> cache increases in size, so the administrative overhead for Oracle
> increases. There comes a point where, for a particular 'unit' of hardware,
> the marginal cost of the increase in administrative overhead is greater
> than the marginal decrease in costs due to to less physical I/O.

Like everything else in Oracle, it depends!

For reads, disk I/O is almost always shower then an LIO.

For writes, you are correct. That's why many DBA's place high DML objects in a separate tablespace (with a different blocksize), and map it to a smaller buffer.

>
> If you buy me more CPUs and better hard disk arrays, as well as more RAM, I
> can probably push my caches bigger than before and not notice a cost being
> incurred. But if you just keep buying more and more RAM, and making your
> caches bigger and bigger, then you are going to overwhelm your existing
> CPUs with memory management issues, and kill DBWn with a workload he can't
> keep up with.

Yes for DML-intensive databases. But not for DSS, OLAP and DW systems!

> But I won't even accept your statement at face value, either. To say that
> increasing the buffer cache reduces physical disk I/O is simply another,
> slightly fancier, way of saying 'tune by getting a good buffer cache hit
> ratio'. Now the buffer cache hit ratio is known to be a lousy way to tune
> the buffer cache, and has been done to death here and elsewhere many, many
> times. Dress it up anyway you like, but your formulation simply seeks to
> re-instate it as being of some merit.

The DBHR is a metric that measures the probability that a block will be in the buffer on re-read, nothing more.

Howard, what do you think about the AMM method for predicting when to add more buffers? It's kinda like v$db_cache_advice.

> Do you honestly think that cache tuning comes down to making things as big
> as possible? If it were so, how in heaven's name has an entire industry
> been beguiled into paying considerable consulting fees to so many 'experts'
> for so long? Instead of shelling out a couple of grand to get Don in to
> tune for a day, why don't I just add a few noughts to the DB_CACHE_SIZE
> setting for free??

Interesting point. When I visit a client I usually find thousands of sub-optimal SQL statements that would take months to manually tune. To get a head-start, I tweak the instance parms to broad-brush tune as much as possible to the lowest common denominator. Then I can sweep v$sql_plan and tune the exceptions. Tuning the instance first saves buckets of manual tuning effort and lowering optimizer_index_cost_adj will sometimes remove sub-optimal full-tables scans for hundreds of statement in just a few minutes. What's wrong with that?

>
> The reason we have experts is because the problem is a complex one. And no,
> just throwing more memory at the problem does NOT make it go away.

Hmmm, consider this. I had a client last month will a REALLY messed-up database and it was HEAVILY I/O bound (85% read waits). Instead of charging $100k to fix the mess I replaced the disks to high-speed RAM-SAN (solid-state disk)for only $40k. The system ran 15x faster, in less than 24 hours, and the client was VERY happy. Granted, it's not elegant, but hey, why not throw hardware at lousy code if it's cheap and fast?  

> People talk a lot of nonsense quite often, don't they?

Like it or not, disk will soon be as obsolete as drums! I remember when 1.2 gig of disk costs $250k and I can now get 100 gig of SSD for only $110k.

I have several fully-cached clients (some using SSD and other with a huge KEEP pool), and it runs great. . .

I hear rumors that Redwood is working on a solid-state Oracle in a future release where the caches will disappear complete, so let's enjoy this discussion while we still can!

BTW, how as your visit to the USA? Received on Sun Aug 22 2004 - 09:09:30 CDT