Rebuilding Indexes Every Sunday Afternoon !! October 28, 2009

Regular readers of this blog can no doubt pick the various inaccuracies and strawman arguments with this article. It’s was nice however to reminisce and take a stroll down memory lane back to the 80’s and 90’s when many DBAs indeed did spend every Sunday afternoon rebuilding indexes and the such during so-called maintainance windows.

However, if you’re like me and now work on sites where there is no such Sunday maintainance window because your users actually require and demand 24 x 7 access to their applications, because organisations still want to sell their products and services online during Sunday afternoons, because governments still want border control applications functioning on Sunday afternoons, because consumers still want access to their bank savings on Sunday afternoons, because police still need to access critical information about possible criminal activities on Sunday afternoons, because airlines still want to fly aircraft on Sunday afternoons, etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. etc. ……..

then perhaps the article may not be that useful to you afterall.

Of course, maybe you do have a maintainance window of some description but then have that other really annoying problem associated with modern databases, of them being big, really really big and so more difficult to maintain. Back in the 80’s and 90’s, databases were relatively small by todays standards and it was conceivable for indexes to be rebuilt with little or no thought. There was generally little point, but at least you could get away with it. However, if you’re like me and you have databases with indexes that total in the many terabytes, it just isn’t feasible or practical to rebuild all such indexes, even if you wanted to.

No. If you do have a maintainance window, you may want to make sure the valuable time is spent well, on activities that actually make a difference.

Of course you may also look after databases like I do where the percentage of indexes that actually benefit from a rebuild is not around 30% as suggested by the article but significantly below 1%. Therefore rebuilding 99+% of indexes for no practical gain or purpose may not be considered a “wise” use of maintainance opportunities.

One thing this article did make me ponder though is the number of database sites out there where the DBAs demand and force their businesses and associated end-users into having forced downtimes, into having their applications and business processes impacted and made unavailable (say) every Sunday morning, not because it’s actually necessary or because there’s a business or technical benefit but just because their DBAs say or think they still need to follow the processes and maintainance activities of the 80’s and 90’s. Interesting question that …

One last point I would make with this article (which BTW will change and be modified in the future, I can guarantee that, just save a copy and see). Why would you bother with validating the structure of all indexes if you plan to rebuild them all anyways ? And who is the more inept ? Someone in the Netherlands who attempts to run a script that attempts to validate structure of all indexes or someone who writes a script to validate structure indexes with an ALTER INDEX command😉

Personally, I spend every Sunday afternoon relaxing and enjoying the weekend, playing sport, perhaps doing a bit of gardening and on a lovely sunny day, just sitting back with family and friends enjoying a barbecue and a few cold beers.

Meanwhile, all my database applications just run on happily along with no impact on business activities at all …

Too bad I never had a chance to experience such nice index-rebuilding Sunday. All of those huge DB I have supported are built on IOT and very little real indexes to play with. Maybe Burleson can lend me his production DB for a rebuild exercise ….

Not tuesday, but I used to work for a company with a strict rule that the latest point in time during the week for any releases/upgrades or maintanance was thursday afternoon, primarily because no one wanted to spend all weekend at the office if something went wrong , and most of our activity was at the weekend anyway.
Anyway I’m not qualified enough to rebuild indexes as my (almost) degree in physics doesn’t qualify me as a database professional.

but then he did not even need any big downtime or maintenance windo if he rebuilds all the index with online option because he is “World’s top Oracle DBA” to adminster any Oracle database lower than Enterprise Edition would be demaning to his skills😉🙂🙂

The interesting part is MR Burleson never mentioned about the amount of redo generated during this operation. It was madness amount of redo for 1TB database when we had to do it at my old company because of the managers who reads Mr Burleson.

Time consuming
Space consuming

For what ?

Nothing.

If Mr Burleson do only believe real production systems not the test ones, I can proudly say that as a DBA of a very very important and big production system after I convinced my managers that, this is wrong and complete bollocks, we never rebuild %99.9 of our indexes again.

That’s a very important point. Not only does one have to manage the redo stream and somehow ensure recoverability is not comprosed (having several TB archive destinations is somewhat tricky for many sites) but if you have standby databases, the subsequent load on the network and keeping them up to date as well can be problematic to say the very least.

Generating redo is extremely expensive and managing tons of redo is a right pain.

And yet the article says:

“Rebuilding indexes in a schedule is a DBA best practice because the cost of rebuilding indexes is zero.”

I’ve decided not to give away too many of the blatantly wrong technical details in the article. No doubt some of the mistakes will silently disappear over time but as it stands, it’s a nice example of misunderstanding key indexing concepts.

In my view, a very important point of this whole “We have to rebuild these indexes regulary – therefore need maintenance time!”-thing is, that it makes Oracle Databases really look bad.

“Oh Man, this d*** Database needs that much downtime! Why do we pay that much money for it?”

When in fact that is more than often a complete waste of precious maintenance time and effort. I really do appreciate that you and the other Oak Table members spread the knowledge that it aint so! Similar could be said about repeatedly reorganization of tables also🙂

I think people within Oracle are very conscious of the fact false suggestions such as all indexes should be rebuilt weekly can impact the perception of the value of the Oracle database software. I’m sure it’s one of the reasons why my index seminar has been so successful. People, be they DBAs or Developers or Solution Architects or whoever need a better understanding of how to actually proceed with index maintenance and usage, because the option given in the article is simply a non-option for most sites these days.

In the end , it comes down to a misunderstanding of how indexes actually work and function within Oracle. This article with the nonsense regarding freelists for example is a classic case of where a misunderstanding of index behaviour can lead to an inappropriate course of action.

Some applies to medicine, to mechanics, to law, etc. Don’t understand what is really going on, make the wrong diagnosis, make the wrong decision and you end up potentially doing the wrong thing, maybe even making things worse, not better.

Once you begin to really understand Oracle indexes, one begins to really understand those scenarios when a rebuild or coalesce might actually be beneficial. However, get the foundations wrong and the whole argument above just falls to pieces.

Richard,
regarding your index semiar: Incidentally, I am in the same office as the Oracle University EMEA Marketing Director. We talked about seminars recently, when I mentioned you as one with a very high reputation inside the Oracle Community, especially when it comes to indexes. She then rolled her eyes and said that your charge is also very high🙂 And there was apparently a little misunderstanding, whether you will be paid that amount you charged in Dollars or in Pound – which is a big difference… Anyway, I hope to be able to attend your seminar when you are in Germany! I will be the guy in black (first row), asking all the annoying nitpicking questions🙂

This is a feedback from OTN forum discussion. It’s pity that Mr Burleson cut only very very tiny part of it as a example that other people do that same – but Hemant AFAIR was writing about special case which can be included in Richard 1 % of indexes which required that kind of operation.

I think people, including those that run OTN, are really beginning to get tired of the Burleson nonsense that continually ruins so many of the OTN discussions.

I remember reading a recent discussion involving Hemant and he certainly appeared to have a scenario where a rebuild/coalesce might be beneficial. If you have an example where it does indeed help, great, go for it. However, understanding why and whether the issue could be avoided is always a good course of action to take as well.

This is a point.
If you know why you have to rebuild your index and you can’t do anything else due to 3rd party code – do it.
In my opinion Hemant should ask Mr Burleson to remove this quotation from his article – this is something which has been cut off from whole discussion without any additional comments.

I didn’t know that Burleson had quoted my posting on forums. I’ve updated my forums posting with a clarification.
That “I have scheduled weekly rebuild” is for the indexes on a specific table (and I have found only 3 such tables whose indexes might behave in that manner). So it is less than the 1% of cases.
Other likely needs for a Rebuild are also posted on my blog. So I have added links to those posts in my update on forums.

Since I updated my forums posting, I see that Mr Burleson, too, has updated his article. He adds “However. Hemant notes that scheduling an index rebuild was a rare case for him and the root cause for the scheduled rebuild was an unresolved Oracle bug” and then quotes me “”I do not recommend rebuilding of ALL indexes weekly. This was a specific table ….”
So I’ll give him credit for that !

I suspect when he realises that your comments and link now kinda matches those “Neophytes claim that 99% of index rebuilding is a waste of time”, as you indeed only schedule rebuilds of less that 1% of your indexes, the link might just be deleted altogether.

Fingers crossed your name is removed eventually.

Interestingly, all the technical errors mentioned in the article as reasons for rebuilding indexes have yet to be addressed.

Perhaps because I purposefully haven’t mentioned them😉

It’s a classic example of how misunderstanding how things work leads to incorrect assumptions which in turn leads to bad DBA practices. I don’t want to giveaway too much and lose my lovely example altogether😉

I don’t think pointing out these issues is necessarily a total waste of time. One could certainly argue there are more inertesting things to write since most people would react the same way upon reading the article, that’s it’s a hillarious piece of nonsense. But there are always the newbies to consider who might read such suggestions and think it actually is a “DBA best practice” to blindly schedule index rebuilds or that “the cost of rebuilding indexes is zero”.

You know it’s not true, so do I, so do most DBAs but it never hurts to highlight the obvious every now and then🙂

I just read an OTN thread where this Burleson goes on and on abusing Jonathan Lewis, calling Lewis a godless heathen who quotes Carl Sagan, that he has no experience and hence no right to call himself an expert and all sorts of nuttiness.

A very strange man..

But the guy seems to be good in Google optimization. Whenever you query something related to Oracle his website (or one of his numerous websites) pops up at the first page, sometimes at the top of first page on Google.

He should stick to Google optimization and stop wandering into Oracle world like a drunken elephant damaging everything on it’s path and also misleading most junior DBAs. Many junior DBAs probably read him and believe what he writes.

But once they become veterans of 1 or 2 years in Oracle , I doubt they take him seriously.

Some of the things on the OTN forums lately have been simply disgraceful. Absolutely shameful.

I believe and hope that action will finally be taken, let’s hope so anyways.

A little fact for you. Every single DBA and Oracle professional who I know and respect and there are a great many of them around the world now whom I’ve had the pleasure to work with or met in person or via a computer link, every single one of them bar none, have the same opinion as me on “certain matters”.

Sometimes one has to say the same thing again and again and again and highlight the same errors and inconsistancies again and again and again for the message to get through to those a little slower on the uptake than you and I😉

Sorry you can’t make Paris, I believe there’s going to be quite a crowd. I’m not sure when I’ll get the chance to run the seminar again in EMEA ?

The good news is this: There may be a time where indexes on some databases are no longer required. Then these folks will have to find something else to waste their time on.

“We saw significant improvements in the tests we did on Exadata. The minimum improvement was 27x with an average of 470x improvement on the queries we tested compared to our current system. This was achieved with no tuning and after removing all indexes. In fact, eliminating indexes is going to save us on half the disk capacity.” — Mark Win, Director

It’s malfeasance for a DBA to not use the downtime windows to perform database administration tasks.”

Why would one just slap on histograms to a production database? Couldn’t there be serious repercussions to explain plans. Why would one consider that an “administrative” task? Typical “administrative”, in this sense, mean perfunctory, not investigatory problem solving.

Also, isn’t there real risk to a rebuilt index causing delays in inserts? the empty space eliminated may be needed again and cause an insert to wait for a block split? He says it’s ok because there’s either some improve or no improvement but never anything bad. That’s not completely true. Any change, every change could make things better, the same, or worse. Are there really any production changes with zero risk?

Exactly my point. I made a totally innocuous change, added a view, broke a process. How could a view that didn’t exist and now does break something.

USER_TAB_COLUMNS contains columns for views, not just tables as the name suggests. Add a view when you’re expecting only tables and it breaks… yes, yes, should join to user_tables. We know that…….. now.

You wouldn’t slam on a histogram of course, you would usually determine a possible requirement and test the implementation thoroughly.

All changes have an implied risk. Hopefully, the benefits and possible gain, in combination with proper testing ensures such benefits outweigh the possible risks.

But certainly, you rebuild an index and things can possibly change and not necessarily for the best. As mentioned by other, the simple fact of having new stats on the index as a result of the rebuild could on its own, make matters better or worse.

There are certainly possible overheads in an index expanding out again due to subsequent block splits following a rebuild. The risk might be minimal but they’re not necessarily zero either.

I’m quite impressed with an article that in paragraph 1 can link to itself. I like recursion me. (Now wash your hands notices anyone). The thing I’ve never got about the ‘zero cost’ activity is why you would be arranging to do it in a maintenance window at all. Maintenance windows are for necessary but costly activities (rather than unnecessary but costless ones!).

Oh and the article has already changed since it’s been published, and refuted, previously.

Just the downtime itself, regardless of anything else, generally comes at a huge cost and inconvenience to the business. there just ain’t that many organisations that have a Sunday afternoon each week to spare.

Therefore, if you don’t have the downtime opportunity and you really really believe in rebuilding all your indexes, guess what, you make the business suffer a downtime regardless.

That’s when things get really sad and unfortunate.

BTW, I still haven’t got over the mental picture you painted with your recent email !!

Indeed, that’s why you want maintenance windows to be planned (so it isn’t an unexpected cost) and infrequent. In addition when I was responsible for managing E-Business suite (for which the answer to every problem is a new patch😦 ) we arranged downtime windows once a month, and then had a system of notifications if we were actually going to use them. This was rather better than the previous ‘system’ where every dept (HR,Finance etc) determined that they needed a downtime *right now* to fix a *critical* business issue, and IT got all the flak for the system being ‘down again’. Entertainingly the number of critical issues fell by approximately 60% when people knew they had to a) wait till the scheduled window and b) had to explain to the rest of the business what the business driver was.

So I’m all in favour of maintenance windows, especially those that don’t get used, the maintenance though has to have a business case (yes a real one with costs and benefits attached).

As for the mental image, well I know that you are a musical man of taste and judgement so I thought it only fair😦

Regarding the seminar, you should have seen my eyes roll when I walked into a packed room in Germany (Dusseldorf) last year and I thought just have much (or little) I was making out of all the revenue generated.

There’s two sides to every story😉

Unfortunately, I’m not sure what’s happening yet for next year, nothing has been decided yet. Hopefully I’ll get the opportunity to try and answer all your tricky questions🙂

Thanks for that Richard, that article really is a scream, though I haven’t yet worked out whether that’s in the excruciatingly horrifying or excruciatingly comical sense. Perhaps both, if you can’t laugh…

Excellent points. The problem with downtimes / maintenance windows having to have a business case, it that often those that actually need to approve the business case are dependent on those requesting the window for the justification. “We need to bring the application down every Sunday else the application will run slow” is difficult to refute if it’s your (so-called) technical experts making the claim.

A few years ago, I picked up a book on tuning by old Don as it was going for 2 quid in one of those second rate book stores. I must say that I was horrified by most of it’s content (not that I anywhere near finished it). This was around the same time that I was reading Tom Kyte’s Expert Oracle Database Architecture, so you can imagine the difference!

However, I have found one good thing to come from his 5 million page website(s)…

On a Friday morning everyone is given 5 minutes to find the most outrageous quote they can from the ‘great’ man – that becomes the quote of the day and you have to use it as much as possible, that quote can not then be used again.

A few years ago I started with a new employer. There was a Rampant book by Mr Burleson that was handed on to me as the new starter to litter my desk. I sat there unopened for over a year until one day we had Jonathan Lewis on site to dig us out of a hole. I didn’t really get to speak with him until during a stroll around the office he wandered my way, saw the book and asked me how I’d found it! Dammit, if only “Practical Oracle 8i” hadn’t been at home on the bedside table!🙂

Foote, you are doing a service. Actually its extremely necessary to expose such misleading authors and their articles.

I tell you from experience that hundreds of thousands of people get misled through the author and his article you mentioned. Especially in countries where the only way to get Oracle information is Internet, the searches for many terms go to Mr. Don’s site, because he spends more time in SEO of his site, and has filled it with every imaginable Oracle keywords.

Back in 2003, when I was just 1 year old in Oracle DBA, fresh from Oracle University, I got misled by the same author sometimes and still know many who still follow his articles, because they simply DONT KNOW. I make my own humble efforts to redirect them to asktom or such sites.

Nayab reminded me that Google gives you a way of defining your own search … parameters… you can include the sites you want to search… not sure if there’s a way to just ignore sites and search the rest of the internet. I’ll try it… see what it takes to share the list editing with others.

Yes you could rebuild an index with NOLOGGING, but you miss the point that such an option is simply not possible with you’re one of the many sites with standby databases.

And it also misses the point that redo is but a part of the potentially huge costs associated with rebuilding all your indexes (think undo, think massive I/Os, think massive sorting, think locking implications, etc.etc.

The point being that rebuilding indexes is not a zero cost operation. It requires lots of resources or a downtime that many also just can’t afford.

BTW, it’s hard to chill out when you have weeks of 30+ degree C on end😉

As I predicted, the article has changed somewhat. No surprise there although all the changes are illuminating, especially the subtle little ones (those that have the original will see what I mean). Classic quotes such as “I am a production DBA and I’ve been rebuilding my indexes every Sunday afternoon for decades” are sadly now gone.

It’s still very amusing however and rather than “Neophytes”, it now makes references to “foreign people” and “foreign Oracle scientists”.

Hi, I like some of the changes even more, especially the link to the SAP guy at http://www.sdn.sap.com/irj/scn/weblogs;jsessionid=(J2EE3417400)ID0437089950DB00792386764563478157End?blog=/pub/wlg/8750%3Fpage%3Dlast who doesn’t want material reproduced outside the SAP domain and uses a queue table for his example, which is to say the least a somewhat specialized, though not uncommon, scenario. In addition I’d have thought his comment that “I am not recommending blindly rebuilding indexes” and “we rebuild indexes following large purges” and “you should know what effect you are intending to achieve” all somewhat run counter to the tenor of Don’s article. There’s a moral here somewhere, something about what to do when in a hole, methinks.😦

Thanks for cool article i think is worth service. I am not oracle veteran and i sometimes find – via google of course – some cool dons tips with which i disagree. And sometimes i am thinking who is wrong me or this dba guru😉.

Latest topic with which i disagree found on this web was that histogram is good only for columns with indexes🙂.

Keep saving all the changes. If you have the complete set, it will be far more valuable in years to come😉

The latest version is just hilarious. It has a new introduction:

“While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout and other cases where it has been necessary to schedule a rebuild of problematic production indexes”

which totally contradicts his introduction in the original version:

“I am a production DBA and I’ve been rebuilding my indexes every Sunday afternoon for decades. My end-users say that they notice a definite improvement in response times on Mondays, but now I hear people making bizarre statements that because their testing on a PC shows case single case where rebuilding does not help, they make faulty over-generalizations and say that index rebuilding is largely a waste of time and that index rebuilding rarely improves query performance.”

Ummm, don’t these “bizarre comments” now match his back-peddling new comments ???

“vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding” vs. “rebuilding rarely improves query performance” kinda sounds the same to me.

And:

“there are isolated cases whereby rebuilding has been proven to improve SQL throughout” vs. “index rebuilding is largely a waste of time” except perhaps for those isolated cases …

Considering the number of changes to this thing now, I guess anything is possible !!

However, I suspect I might still be considered one of those “foreign people making bizzare comments” with claims such as “index rebuilding rarely improves query performance”.

Then again, as the latest version (as of 2 Dec 09) now has a new introduction that says:

“While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout …”

Excellent Wikipedia link😉 Definitely all of those D-grade articles are not suitable for children under 17. If you think about it deeper, it should not even be NC-17. It should be NC-70. But according to one particular Oracle Expert, any opinion written by anyone who does not publish his/her CV/credentials cannot be trusted. :p

I know this is slightly off topic, but I read the article and this heading leapt out at me.
“Keeping dangerous techniques out of the hands of inapt DBA’s”

I suspect that the phrase was meant to be inept. But it set me to wondering where a DBA would be inapt, and why you would only want to keep a technique out of their hands at this time and not others.

I can think of several locations in which a DBA would be considered inapt (operating theatre, wrestling ring, car assembly line). The bit I am struggling with is the appropriate use of the techniques when the DBA is not being inapt (ie at work). Any ideas?

I think these articles need a similar system. Only read if you have say at least 17 weeks of experience with Oracle. Any less and you might be fooled into thinking they might have merit or be correct. Any more and you’ll have enough experience to recognise all the errors for yourself😉

Don’t apologise if you think you might have amused me, I enjoy being amused😉

There’s no simple way to answer such a question within a comment, except to say that the vast majority of indexes in the vast majority of databases don’t need to be rebuilt.

Even Burleson now says “While it remains true that the vast majority of Oracle indexes will not see any SQL performance benefit from rebuilding, there are isolated cases whereby rebuilding has been proven to improve SQL throughout …”

To find out these “isolated cases”, I suggest having a good read through my various blog entires on this matter and checking out this presentation of mine: