Mike Ault's thoughts on various topics, Oracle related and not. Note: I reserve the right to delete comments that are not contributing to the overall theme of the BLOG or are insulting or demeaning to anyone. The posts on this blog are provided “as is” with no warranties and confer no rights. The opinions expressed on this site are mine and mine alone, and do not necessarily represent those of my employer.

Saturday, April 09, 2005

Rumor Control

It seems numerous rumors and comments are being posted in the comments sections about things I may have once said, but upon research found were incorrect and retracted. Let me reiterate:

1. I have not found a case were an index rebuild, without re-arrangement of columns or change to the index structure, changes the clustering factor. Any prior advice I gave (nearly 2 years ago or so) to the contrary is incorrect.

2. I completely understand that the multiple block size feature in Oracle has limitations. I recommend them where the limitations are not a factor. That I haven't written a multi-page report on this fact with several more pages of complex proof, simply to prove what others have already done and is well documented in Oracle documentation I apologize for, I simply don't have the time to waste reinventing wheels.

3. The simple rules I mentioned in previous posts referred to the rules for database physical, PL/SQL and SQL design that are put forth in many of the Oak Table books, Oracle Press books and Rampant Books. Unfortunately, people don't follow these rules, the rules were made after the applications were designed, the people bought third party solutions that never follow any rules (good or bad), the people can't/won't change the code or a myriad set of other reasons that the good practices are not put in place. This is the real-world I dwell in and have to deal with.

4. I have never claimed that my advice on rebuilding indexes only applied to bitmaps, never have, never will. There are several situations where rebuilding or coalescing of indexes is beneficial and when I see a need for it, I will recommend it.

5. Why have I not attacked Don Burleson. Well, why have Howard and Tom never attacked Oracle for the many stupid things Oracle has done in the design and implementation of the Oracle product? For Oracle’s handling of support issues? For Oracle supports use of their own set of silver bullets? Also, Don has not attacked me. I am not confrontational by nature, I tend to let others have their opinions. When I can, I take Don to task in private for things which may be inaccurate, it is up to Don to change if he agrees with me. If I don't agree with everything Don advises, well, that is my opinion. It is rather like the TV, if you don't like what is being said, switch to another channel.

6. As to my opinions about asktom, yes, there are many good things there, there are also bad things as with any public forum. My major problem is that while it is supported by Oracle Corp. and supposedly moderated, I see things there that violate Oracles rules of conduct and should not be allowed on a moderated site. The examples are in the article, you can go to those links and see for yourself. Tom is excellent at providing proofs for his environment. But he rarely states "This proof should only be taken for proof in a similar environment to mine, your mileage may differ" and the very neofites you fear Don's and my advice will mislead, will be just as easily misled by correct proofs for an improper environment.

I attempt to avoid name-calling, bashing of others, and in general clouding issues with emotion charged clap-trap. In specific cases I have fought back when attacked by others and will continue to do so.

Oracle users tend to be a vocal lot. If advice causes problems it is generally well shouted from the roof tops. I task you Howard in presenting these shouts, if you cannot then you are but another chicken little proclaiming the sky is falling.

Any advice, when used incorrectly or without consideration is bad advice. Anyone who blindly follows suggestions, no matter what the source, without testing it in a test environment, deserves what they get.

If an automotive expert suggested I put water in my gas tank, I would question that advice, not blindly follow it. Compared to virtually all auto experts I am a neophyte in that area but I know not to blindly follow advice. When I go to a Doctor, an area I am definitely not trained in, if the advice sounds absurd I go to a second Doctor for their advice. There is no shortage of advice on Oracle, a quick check of Metalink, OTN and Google will find a plethora of opinions, demos, and advice on virtually any facet of Oracle administration you can imagine.

As I have said before, I have real work to do, I need to consolidate a list of 32 findings into something that can be accomplished with maximum return in 5 days. Not 2 weeks, not a month of study and testing, 5 days. Again, this is the real world.


Howard J. Rogers said...

Why do you do this, Mike?

"Advice I gave nearly 2 years ago..."????

Really? Care to explain why Google has you claiming a rebuild would fix the clustering factor as recently as May 2004. That's 11 months ago, Mike, not 24.

Please. Don't make things up. The archives are always there (unless Don's asked for them to be removed again, of course).

As for "re-iterating" that you haven't found a case where the clustering factor can be affected by a rebuild... Well, that would imply you'd ever "iterated" it in the first place. But in fact you haven't. You grudgingly accepted (again, in May 2004) that simple tests seemed to indicate that you were wrong, but you told us all that you would do further tests on more complex environments. Even now, you claim not to "have found a case"... which kind of suggests that there may be one out there you haven't been able to pin down. Why not just come 100% clean and say, "It isn't going to happen. Given what the clustering factor represents, I now accept that it will *never* be affected by an index rebuild"? That would be the simplest, most honest, and most straightforward acknowledgement of prior error I can think of.

As for your point 2: No-one is asking you to do "complex proof" or"reinventing wheels". Just a simple acknowledgement that suggesting people should rebuild all their indexes into 32K block tablespace, period, without qualification, is just plain daft would be a start. And make the acknowledgement public, not something only your pay-for clients get to hear about.

Your point 4 again misses the point: only *you* initiated the mention of bitmap indexes in the context of rebuilds. And you did so, because in that specific case, rebuilds are indeed beneficial. But everyone also knows that's not what you originally were talking about: that your 'rebuild when appropriate' advice was for B*Tree indexes, first, last and always. To throw bitmaps into the mix now is simply an attempt to salvage some positive meaning from otherwise deeply damaging advice.

So we know full well that your rebuilding advice never "only applied to bitmaps". It was made, by you, in relation to B*Trees, and it is in that context that it has been trashed by me, Tom, Richard, Jonathan (name your traher here). In fact, claiming that the discussion about rebuilding has anything at all to do with Bitmap Indexes is just plain disingenuous. It never was. Only you keep suggesting it might be.

Why have I never attacked Oracle for "stupid things" in the design of the Oracle product???!! You are kidding, right?? I take it you haven't read my article on (to take just one example) the Automatic Workload Repository in 10g??? That's readable at http://www.dizwell.com/html/workload_repository.html, if you're interested, and you might care to read the first 5 paragraphs. Tell me it's a ringing endorsement for Oracle Corporation, and I'll sell you a bridge.

I could go on and on and on. I had a particularly sticky time working for Oracle Corporation precisely because I called a spade a spade. Their documentation can be crappy, and I point it out as such when I see it.

But generally, Oracle (the database) is a magesterially-designed product, and deserves to be praised as such.

Equally generally, Don Burleson is wrong, slipshod, technically incompetent and hypocritical, and he deserves to be described as such.

But I understand Mike: if you don't want to get into hot water with your employer, that's fine. Perhaps you could tell Don that it's a bit iffy to get others into hot water with *their* employers in like manner, so perhaps he could stop ringing them up in the first place?

Tom's proofs don't need qualifying in the way you suggest he should. Because he shows *everything* needed for a user to validate the results for themselves, on their own platforms and in their own environment. His test results are *reproducible*, Mike. That's the big difference between claim and proof. And Tom would not be upset in the least if a reader wrote up and said "I did what you said and got this result, instead". And neither would I, who try to follow the same approach whenever possible.

That's rather different from claiming things, and then -when pressed- saying "Well, this is too complex and too difficult to tell you how to test for yourself. Just trust me, I've written 19 books, including the official performance tuning guide to Oracle".

"Any advice, when used incorrectly or without consideration is bad advice. Anyone who blindly follows suggestions, no matter what the source, without testing it in a test environment, deserves what they get." And just to prove we can all be reasonable, I totally, 100% agree with every part of that paragraph.

"Again, this is the real world." But that one I won't accept! Not that this isn't the real world, of course. But what you imply in that statement, of course, is actually: "Tom and Howard are just theorists, whereas I do it for real". But the whole point of the discussion of the past few weeks has been to demonstrate that "theory" simply means Tom and I know how Oracle works in particular and specific respects, whereas you've been so busy in the real world, you've not spent the time to find that out for yourself. It's why poor clustering factor, block size and index rebuilding advice has come from your direction, and not from mine (or Tom's).

Can I seriously suggest you stop digging when the hole is deep enough? I'm certainly not out to "get" you as a person, an individual. All I want to see is better quality Oracle advice being offered, by whoever, whenever. I know that you can (and have) done that. But you keep poor company in that regard, and it shows.


Mike said...

The original advice was from at least 2 years ago. Yes, the first retraction was 11 months ago.

I can see you are still as gracious as always.

I have publicly stated again and again, that I only suggest index rebuild into larger blocksizes when it would be beneficial to do so. Ditto on index rebuild and/or coalesces. Yet you seem stuck in a loop saying the same thing over and over again.

The quote was "and if anomalous behavior (i.e. reduction in CF) does occur, I will report back to the list with the scenario
and the statistics." since I haven't reported back it should be obvious that I haven't found it to occur. I have kept my word.

No one qualified the intial statements about index rebuilds as pertaining to only B-Tree, you and the other detractors seemed to indicate in your posts that all rebuilding of indexes was daft. I was simply pointing out there are other tpes of indexes other than B-Tree. Tom Kyte even says "but it is indexes on sequences that in general need watching (and the
del_lf_rows ratio so commonly used won't work as shown above!). when they sweep from the left to the right (like a queue -- put rows in, process them, delete
them) but you leave a couple behind -- it can get bad over time. A coalesce would fix them right up." and "So, some "right handed indexes" like that can benefit from a coalesce from time
to time, or maybe even a rebuild... " In the same thread I also stated: "I would like to apologize to folks who read my articles about rebuild of indexes
fixing cluster factors, I believe I recinded that advice where ever I could"

Give it a rest.

Mike said...

Why do I do what? Respond to unfair, outdated attacks? I gave the inital advice over two years ago, recinded it 11 months ago. By the quote you posted in asktom, I said if I found a case where it happened I would report it, since I haven't found a case, I haven't reported any, what part of this don't you understand?

In your and other experts postings you stated over and over again rebuilding ANY index was bad. In my inclusion of bitmap indexes I was simply pointing out that this doesn't apply to all indexes.

In thread "http://asktom.oracle.com/pls/ask/f?p=4950:8:3005220955240849280::NO::F4950_P8_DISPLAYID,F4950_P8_CRITERIA:2913600659112," Tom himself states that certain B-tree indexes benefit from rebuilding, I think his stated percentage of occurance is a bit low (how did he get that value? Looks a bit brown to me) but he does support the coalesce of sparse indexes and rebuilds of the so-called right-handed indexes.

Agan, let me state for the record, hopefully the last time, I do not advocate the rebuild of all indexes on a schedule. I advocate analysis of indexes to determine which, if any, will benefit from a rebuild, determine how they should be rebuilt (should parameters be changed to improve their structure to reduce required rebuilds?) and then only rebuild them as needed to maintain performance. I support the coalesce of sparse indexes, again, they may need to be examined to determine if they can be rebuilt with better storage to decrease the chances of becoming sparse again.

I advocate rebuild of bitmap indexes on an on going basis if they are used in high transaction environments.

What is unclear about any of this?

David Aldridge said...

Mike, are you thinking of writing a report on the use of multiple block sizes in a database?

Howard J. Rogers said...

"I have publicly stated again and again, that I only suggest index rebuild into larger blocksizes when it would be beneficial to do so. Ditto on index rebuild and/or coalesces. Yet you seem stuck in a loop saying the same thing over and over again."

That you keep claiming this rather means it's you in the loop, not me, Mike! But whatever... let's stick to technical facts and figures. Under what circumstances should people be contemplating rebuilding their indexes, or using large blocksize tablespaces? Or do I have to pay you before you can inform me in general terms when these sorts of things might be called for? It's no good saying "I only advise it when it's indicated"... reasonable people would like to know when it's indicated!

I know you'd like me to "give it a rest", Mike, because it's a darn'd inconvenient thing, having to explain and justify yourself. But here you are, saying that you can tell when a rebuild, or a large block tablespace, would be beneficial, and we're not to be told *how* you know that? Reasonable people might reasonably wonder whether they have to pay to get this crucial information, or whether your claim to be able to tell is just that: a claim without foundation.

I can show you many measured and reproducible cases where an index rebuild will cause grief, for example, not a benefit. So if you can tell the opposite, I'd like to see how you do it. And I'm sure others would too.

"By the quote you posted in asktom, I said if I found a case where it happened I would report it, since I haven't found a case, I haven't reported any, what part of this don't you understand?"

What I don't understand, Mike, is that you said on Tom's site that when you were wrong, you admitted it and apologised. Now I am supposed to assume that 11 months of silence on the topic is the same thing as admitting you were wrong and apologising? I rather thought the "I was wrong, I am sorry" words were more useful in that regard.

The point of quoting you from so long ago was to show that your initial admission of error was gruding and heavily qualified. You have not subsequently made it fulsome and unqualified.

And this isn't about being stuck in a timewarp, or being obsessed, or anything else you might want to deflect the issue with, Mike. It's important we know you have completely and definitely dropped clustering factor as an indicator of when to rebuild an index, otherwise some might think there is still merit in the measure under certain circumstances.

And rather more importantly, perhaps, because it affects the future, it's important to know that when you do make a mistake, it's acknowledged clearly in a timely fashion. That way, potential clients will know that they won't have to follow poor advice for 11 months before they are set unambiguously straight.

I am really happy that you don't advocate rebuilding indexes to a schedule, Mike. But I would love to know what measures you use to tell you that a rebuild is ever called for. I think we can agree that clustering factor isn't one of them, so what other ones do you use? I've seen you proposing LF_ROWS/DEL_LF_ROWS, but as we all know that will fix itself up for nearly all indexes over time, presumably you must have some other indicators or measures.

And you can keep on bringing bitmap indexes into the discussion if you like, but no-one was talking about them, and you're only doing so to throw up a smokescreen. Bitmap indexes are only available in the Enterprise Edition, after all, so not everyone will have them -whereas we all have b*trees. And by the way: if you spot bitmap indexes in a "high transaction environment", the better approach would generally be to drop them altogether. Bitmaps and DML don't mix... which is presumably why, at http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96531/ch5_edit.htm, they are labelled as being a "Data Warehousing and VLDB" feature.

Mike said...


Tom Kyte and several of the Oakies as well as many other experts support the limited rebuild or coalesce of indexes. These sources have also shown how to determine if an index may benefit using various trace methods. I am not going to cut and paste or waste time reinventing the wheel. I suggest you go there for your proofs.

I have stated, in the current blog, that I have found no case in which a rebuild, without changing the index structure, will cahnge the cf, how much clearer can I make it?

Sorry if you expect me to kiss your foot and beg forgiveness, ain't going to happen, not now, not ever. I made a mistake, I admitted it in many locations on line, even in my own blog. If that isn't good enough tough s***. I don't really care what you feel I should or shouldn't do.