I would disagree with not rebuilding at all, even scheduled items.
I would disagree with rebuilding everything often.
I could and would agree or disagree with all of this, depending on the case.
Do what you need to make your system perform for you (despite all pundits).
I "bought in" to the fact that if you're using locally managed tables with uniform allocation its never necessary, and that you never have to rebuild indexes, as long as you've kept up with leaf node depth... etc. etc. I agree that it is MUCH LESS necessary with uniform allocation, but never needed is different thing.
HOWEVER, after 3 years of steadily declining FULLY MEASURED performance (despite monumental recurring tuning tasks which helped tremendously, but still left us with "less performance" than the previous year, I moved/rebuilt all of my hottest and largest target tables and indexes, and total I/O at peak went down to 1/4 of what it used to be.
Those rebuilds were and still are measured via perfview, and I can see the differences at each reorg, over the multi-month peak periods, comparing year to year.
Can I summarize those total changes in Oracle? Probably, but it doesn't show as easily and readily there, even using statspack, it's more inferential. What I can see easily before and after is that latch contention is down, I'm servicing requests faster, I'm doing less physical I/O.
But Perfview shows more about total system throughput than anything else, and it clearly demonstrates that the system handles load more efficiently than before. Like 30% easier and better, despite having much, much more data and simultaneous queries and demands placed on the server from years of custom development and IT requirements growth and features and deliverables.
Put simply, I don't like doing these yearly targeted reorgs, and I certainly don't enjoy it. No on makes any OT money for it (not even comp time sadly enough), and the extra "cost" to our company simply does not exist in any realistic sense, no more than inferring that someone looking over logs for access issues, errors, possible problems and not finding any is a "wasted cost."
However, we've got sustained renewed performance out of older hardware that we've measured, and are quite happy with.
Keep in mind that this was a focused exercise, on only our biggest problem areas, and nothing else (that is just 8 tablespaces out of 500 or so, and most of them only the top 4 to 20 (depending) largest tables/indexes or just indexes in each). The overwhelming majority of tables and indexes in the system were left alone.
So, do what you need to do to make your system run well for yourself. Be cautious of following in lock step "all purpose" rules of thumb, as they usually don't always apply everywhere to everything. Question things more for yourself, measure when possible and use your knowledge to treat issues.
We are the people our parents warned us about --Jimmy Buffett
