Many Oracle professionals schedule Oracle index and table rebuilds
during downtime when their system is idle or use the
dbms_redefinition utility to reorg a table during up-time. However,
some DBA "scientists" believe that no DBA task should be done
without a "proof" of the benefit, in advance. A complex task, true,
but one that could be done with the help of historical SQL access
data from the dba_hist_sql_plan table.
There is a great-deal of controversy about the benefits of
rebuilding tables and indexes, and some say that indexes and tables
"seldom" need re-building, a very dangerous "don't worry about it"
approach IMHO.
I think that 10g has all the data needed to predict the reduction in
LIO and PIO from a reorganization. The reorganization of a
table/index also gives you the opportunity to adjust the object
structure so prevent a re-occurrence of the fragmentation issue.
It's well understood that adjusting how the table and index data are
stored on the data blocks can have a positive impact on query
response time.
In an OracleWorld 2003 presentation titled “Oracle Database 10g:
The Self-Managing Database” by Sushil Kumar of Oracle Corporation,
Kumar states that the Automatic Maintenance Tasks (AMT) Oracle10g
feature will automatically detect and re-build sub-optimal indexes.
Also, see these notes on
using multiple blocksizes in Oracle.
In fact, there was a great "peek" presentation at RMOUG that
suggests that
predictive modeling
will be an important new feature in 11g.
