Rebuilding Indexes Every Sunday Afternoon !! October 28, 2009
Posted by Richard Foote in Index Rebuild, Oracle Indexes, Richard's Musings.trackback
I just had to share this amusing article on “Scheduling Oracle Index Rebuilding“.
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 …
You are so wrong, Richard.
Didn’t you read that part of the article that reads “the latest consensus on index rebuilding”? Since it is ‘latest’ and it is a ‘consensus’, that means you are … outdated and … outnumbered. :p
He even has a ‘common definition for ‘unbalanced-index”. *cough*
By the way, I have saved a copy of that page, as per your instruction. Am waiting for the article to be ‘updated silently’ … :p
Hi Andy
I just love all the inconsistencies, they just make it such a fun read.
It all starts so well with:
“Important 2009 update: The rules for identification of candidates for index rebuilding are changing.”
It makes one wonder what has changed since 2008 as he then goes on to say:
“I am a production DBA and I’ve been rebuilding my indexes every Sunday afternoon for decades” !!
It’s the latest consensus of just one me thinks
Love it, just hope future changes don’t ruin it too much
I propose all the web sites be built on Oracle with flashback turned on so that we all have past references at our fingertips …. :p
Heh Heh.
You should know by now, that guy only talks garbage. It’s amazing that he still gets away with it.
I have to confess, I don’t rebuild my indexes on Sunday afternoons… or the rest of the week. Can I still call myself a DBA? Please?
Hi Fred
You would think in all those many decades there would have been at least one site that would demand all indexes be rebuilt on a Tuesday ???
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
Hi Raj
Remembering of course that there can still be significant and performance crippling locking implications of rebuilding indexes, even with the online enterprise edition option.
You have to wait until 11g for significant improvements in this regard and 11g hasn’t unfortunately been available for “decades” …
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.
Hi Coskan
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.”
Yeah right, of course it is …
Well, unless using FORCE_LOGGING, then one could rebuild NOLOGGING, surely? No redo (to speak of) then.
This thread’s turned into a slagging-off of DKB… AGAIN!!!
FFS: get the BBQ lit, open a tube and chill!
Hi Andy
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
Kind regards
Uwe
Thanks Uwe
Indeed with tables reorgs as well.
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,
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 
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
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.
Hi Marcin
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.
Richard,
Do you really need to waste time pointing out these issues. Get back to the good stuff!
Hi Brian
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.
Hi Ajit
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”.
Our views, bar none, are totally consistent …
I’m with Brian.
(and with Coskan on redo, and with Uwe on effort and management-bs and quality. But mainly with Brian.. tired of this yes-no game – get on with the important stuff)
Mud sticks, even to those who point out where the mud comes from.
Btw: sorry to have to miss your seminar in Paris, but I’m not in the neighborhood. Some other time perhaps.
Hi Piet
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.
http://www.oracle.com/us/corporate/press/036688
Hi Greg
I heard the same message when I attended a Exadata presentation lately. Hopefully, I’ll be long retired before indexes finally die out else I’ll just have to talk about David Bowie a lot more
Instead of all the bashing (which I do enjoy some), I have a serious question.
It’s one thing to rebuild an existing index but Burlson says,
“DBA – The DBA will apply patches, reanalyze optimizer statistics, add histograms and reorganize tables and rebuild indexes.
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?
I don’t pretend to be an expert but these are just my observations.
@Mark
Tom Kyte did a great presentation on Reorgs. Download it here.
Take note to slide 27 about the increased redo created after rebuilding indexes. There certainly are risks associated with any change, even one as seemingly innocent as index rebuilds.
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.
@Greg
Thanks for posting the link to that presentation. It was an excellent presentation. Concise. Simple. Easy to understand.
Thanks again!
Hi Mark
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.
Hi Niall
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 !!
Hi,
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
Sunday afternoons must be really boring down there, if rebuilding indexes is the highlight…
Hi Noons
That’s because not everyone is lucky enough to live in Australia. In some places, maybe you need those index rebuilds to fill in the time
The horrible grammatical and spelling errors alone make the article (in its current form) a real hoot.
Hi David
Oh yes !!
Shhusssh, don’t give away too much
Hi Uwe
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…
Hi Sean
Given the choice, always go for the laugh whenever possible
Hi Niall
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.
Try it, it brightens the end of each week!
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!
Hi Neil
LOL !! Yes, I not too sure that a Burleson book would have created the best of impressions with Jonathan
I’m now with you on “certain matters” (your point 9 response). Thank you for this article Richard (and many others!), I’ll be more selective with my google search results from now on.
[...] Foote and his readers discuss another time-based ritual of old, rebuilding indexes every sunday afternoon. “I just had to share this amusing article on ‘Scheduling Oracle Index [...]
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.
Is it a way to tell Google which content is right and which is wrong?
This is what I have been doing from last several years.
In google.com you can actually filter results like this.
oracle index -burleson
Google will filter out all the results where it finds “burleson” .
Or like this
oracle index -dba-oracle.com
Or
oracle index -dbaforums.org -dba-oracle.com
Hope this helps.
Thanks,
Nayab
Hi Nayab
All good tips
Hi Fahd
Thank you.
See Nayab’s post on a way to filter out the rubbish.
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.
we could pass that around the community.
Thanks for the tip Nayab. I’ve been darting around those urls for years to try and decrease the rank order now I don’t even have to see them.