Scheduling table and index reorganizations?

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.