Don Burleson: The Correct Default Buffer Cache Size Debate: An Easy One

OK Don, while you're munching on your cheese and crackers, I thought I'd give you an easy one, the last in our trilogy of debatable topics. You know what they say, good things come in 3s. I thought this topic would be appropriate as it brings this whole sorry "discussion" full circle as it's kinda discussed to Howard's excellent 9i New Features E-Book.

In it, Howard discusses the new buffer cache parameters, you know the ones, db_cache_size, db_keep_cache_size and db_recycle_cache_size. Now we all know that the total size of the buffer cache in relation to these specific parameters is db_cache_size + db_keep_cache_size + db_recycle_cache_size. I mean this is elementary stuff right, you can't get any more basic and any more fundamental than knowing how much memory one's allocated to an instance. The size of the Default buffer pool is db_cache_size, the size of the Keep buffer pool is db_keep_cache_size and the size of the Recycle buffer pool is db_recycle_cache_size. OCPs, SPGs (Self Proclaim Gurus) and even DBA students with one days training and experience behind them can get this right.

However Don, your "expert" advice in this matter is somewhat confusing. You claim that the total size of the buffer cache in relation to these specific parameters is actually just the db_cache_size and that the size of the default pool is db_cache_size - db_keep_cache_size - db_recycle_cache_size. If fact you claim that these parameters behave just like their 8i counterparts and that the keep and recycle values are a subset of the db_cache_size. And you've claimed this quite a number of times. You claim this in the myth ridden Oracle Space Management Handbook available at DBAzine (http://www.dbazine.com/oracle.shtml), you claim it again in your recent "Creating a Self Tuning Database" presentation at the recent RMOUG conference (http://www.rmoug.org/TD2004_Pres_Sum.htm) in Feb this year and I believe you claim this in one or more of your Tuning Books (which I don't own so I can't be sure).

So Don, please do explain why you think differently to most people and advice that the buffer caches behave as you suggest. Please explain why we all have it wrong and you're correct. Please show us a demo, an example, please provide an explanation on how Oracle behaves as you claim. It's been *years* since the release of 9i, so no doubt you've actually tested this theory of yours and have *proof* to back it up by now. Remember, you still made this claim only *months* ago. Perhaps Don, you can give us a couple of 9i sites you manage where sizing these parameters displays this behaviour. Surely Don there aren't sites out there with system memory problems because the expert DBA can't correctly calculate the buffer cache sizes. Please tell me it isn't so .

How does one determine the size of the default buffer cache in 9i ? I mean, you can't get any easier than that can you ? Guess what Don. If I were to interview for a *junior* DBA position and they couldn't tell me the correct size of the initial default buffer cache when presented with the db_cache_size parameter (not that I would ordinarily ask such a simple, mundane question), I would immediately say bye bye. And if they were to complain, "you can't do that, I've written heaps of books, I'm an Oracle Guru", I would say "yeah right, getting this wrong and being an Oracle Guru is a contradiction in terms, get out now" (note dramatic effect only, I would actually be far more polite and sympathetic).

So Don, please, please explain to us all why you think so differently and why you shouldn't be shown the door .

That's it from me, over to you now Don (and of course anyone else who wishes to participate). I must say Don that the quality of your posts since we've been focusing of technical issues has vastly improved (if not so much the quantity .)

Richard Received on Wed Jun 09 2004 - 07:47:51 CDT