Mike Ault has an article at searchoracle.com titled “Oracle myths debunked”.
* sigh *
The problem is that he has the myths backwards! The way that he phrases them it it the lack of taking some action that is the problem — apparantly not seperating tables and indexes is a myth, and not reorganising tables and indexes is a myth, and not using multiple blocksizes is a myth.
All of these are of course convenient actions that a person could advise someone with a performance problem to take — if your client can’t touch the code, then what are you going to advise them to do? Rebuild indexes, reorganize tables, recreate your databases with a 32kb system and temp tablespaces block size?
Setting aside that quandry, Mike’s arguments do not stand up to scrutiny, and in some cases just represent straw men.
Take the seperate table/index tablespace issue. If you want to monitor i/o by seperating them, then that’s fine — use the tablespaces as administrative tools. No problem, and I’m not aware that anyone would disagree with that opinion. But Mike lets himself down with some self-defeating logic when it comes to performance issues here. Let’s follow Mike’s logic.
- Table and index access is sequential (OK, common basis of understanding)
- If table and index are on the same drive then excessive head movement is required even in a single user system (I can see that — sure)
- “In a multi-user environment, it fails to take into consideration all of the above plus the effects of multi-user access against co-located tables and indexes.”
But in a multi-user environment, the heads are always moving around anyway. If any environment was going to benefit from seperating tables and indexes it would be with a single-user! To me this is forehead-slappingly obvious — that the argument in favour of not seperating tables and indexes for performance reasons is strengthened by consideration of a multi-user environment. Ah, go figure.
The multiple blocksize issue is in my opinion, the most nonsensical. The only evidence that has ever been put forward for this is a single script to demonstrate that with double the block size you get half the number of logical i/o’s (as if anyone needed a script to tell them that!), and a single anecdotal comment with no detail to back it up. That’s it! Oh, apart from “Trust me”. I’ve seen the most appalling technical nonsense talked about using large block sizes for temporary tablespaces, when it just does not (and cannot) make a difference to performance.
And don’t think I haven’t tried to get some technical discussion going on this — but there is apparantly no further evidence available. Oh yes, you’ll hear that TPC-C benchmarks use multiple blocksizes — that’s true, but I haven’t seen one yet where they were used for TEMP tablespaces or for storing indexes. Draw your own conclusions, men.
So please, for the love of God will someone from Burleson Consulting please step up to the plate and tell us how TEMP tablespaces benefit from large block sizes, and why TPC-C benchmarks don’t put indexes on 32kb tablespaces.
Please guys, I’m begging you.
I’m on my knees right now as I write this.
Perhaps in the second half of this two-part article, Mike could actually explain this in technical terms, and address his critics.
Because you know fellas, this is really how myths get started.