Inverting the Myths
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."
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.
posted by David Aldridge @ 5:00 PM
9 Comments:
If you want to monitor i/o by
v$segstat, the only reason -- ever, yesterday, today and tomorrow for separating them by tablespace has is and will be -- because it makes you feel better and makes your life better.
period.
create tablespace t1 datafile /d01/d.dbf;
create tablespace t2 datafile
/d01/i.dbf;
use t1 for data, t2 for index, did we accomplish anything in 1989 or 2005 performance wise? No, never did.
However, in 1989:
create tablespace AllStuff
datafile '/d01/f1.dbf', '/d02/f2.dbf' ....
and use small extents for tables and indexes -- we would have achieved striping before striping was cool and more than achieved out goal!!!
But that would have conflicted with the "single extent theory". Which BY THE WAY, was not defeated by locally managed tablespace, but by common sense many many many years before LMTS where invented!
If table and index are on the same drive then excessive head movement is required even in a single user system
actually I disagree with that theory.
indexes are single block IO (random IO) -- root branch leaf
table access by index rowid is single block IO
You are either moving the head 4 times on a single disk (root -> branch -> leaf -> table ) or 4 times on two disks (3 on one and 1 on the other) root-> branch -> leaf on disk one and rowid on the other.
Why is is better to position a head 3 times on one and one on the other other versus 4 times on one. A random seek/io is a random seek/io
the goal, is, was, will be "even IO". index over there, table over here got it partway, striping gets it the rest of the way.
using separate tablespaces IS, HAS BEEN, and WILL BE for the ease and convienence of the DBA. Nothing more, nothing less.
But hey, they have spoken, the advantages of multiple block size tablespaces are well documented so "they say".
only I've not seen the documentation for that, yet.
Hmmm, so what you're saying is that the patient's condition is even more critical!
Yes, after v$segstat was introduced I suppose the entire index/table separation thing is hogwash.
ref: the advantages of multiple block size tablespaces -- I believe that current policy is that no proof needs to be given of the efficacy of Burleson Consulting's remedies, so in looking for more proof I'm afraid we may be flogging a dead horse. ;)
Maybe the problem is that Mr. Peabody and Sherman broke the wayback machine and are now stuck in version 7. That might explain the myths being confused with fact. Or is it the facts being confused with myths?
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.
You realized that this plea is falling on deaf ears as they can't, at least not without violating the carefully crafted set of Burleson's code of conduct.
Peter,
Well not deaf ears, but um ... paralyzed fingers. Of course it's not as if this is some law of physics that we're asking to be broken -- eg. with a wayback machine ;) . It's just a new rule that a consulting company has introduced recently and which excuses a lack of proof for it's favourite myths.
Of course, I don't know what the IRS would say about a company imposing these out-of-work-hours restrictions on 1099 consultants -- some might say that that would imperil their status as non-employees.
How do you determine if a person is an employee or an independent contractor?
The determination is complex, but is essentially made by examining the right to control how, when, and where the person performs services. It is not based on how the person is paid, how often the person is paid, nor whether the person works part-time or full-time. There are three basic areas which determine employment status:
* behavioral control
* financial control and
* relationship of the parties
Anyway, I refer them to IRS Publication 15-A, Employer's Supplemental Tax Guide for more information ;)
David, noticed you're not posting in that forum anymore? Was enjoying reading your posts there, what happened? I was hoping to see more in the Great Discussion Topic thread....
Oh, I just got one too many weird emails ...
"... insulting assumption ... none of your business ... please cut-the-crap ... as if, somehow, you were superior ... You, my ex-friend, are not anon ... I assume you have seen Freeman's 'Evil people' publication ... I'm still not sure on "where you stand" on hurling libelous comments ..."
If Don thinks that the context of these phrases is required then I can post their full text -- I guess he'd stand by their content.
I've only been critical of Don's technical advice -- and maybe of the way he runs the forum also, I guess -- but I went back through the emails I've received and thought, "Fuck it, why do I bother? Obviously he doesn't want me on his forum embarrassing him in front of his clients, and correcting his large block size stuff, or he'd actually be answering the points I made."
So I'm done there for now. Too weird ,too controlling, too technically incorrect for me.
By thew way, note that you can vote for on usefulness of that article ... I think it's at the bottom of the page.
If you were a consultant in the 80s and early 90s, all you had to do was get your foot in the door to do some kind of database health check, and you could spend the next 2 or 3 billable months relaying out the disk subsystem, and separating indexes from tables. I suspect telling them that their block sizes are set up all wrong is the 2000s version of the same make work practice.
Post a Comment
<< Home