 |
|
Oracle 10g segment advisor Tips
Oracle Tips by Burleson Consulting |
There has been an ongoing
debate about the value of periodic rebuilding of tables and indexes, along two
dimensions:
- Reclaimed storage -
The Oracle 10g segment advisor identifies tables and indexes that have
become "sparse" as a result of high DML, as candidate for rebuilding.
- Improved speed -
There are documented cases where rebuilding a table or index will reduce
consistent gets and make the SQL fun faster, but this workload features is
not yet in the Oracle 10g segment advisor.
Also, see the new
dbms_redefinition package which allows online table reorganization:
Graham Wood, the architect
behind the Oracle 10g segment advisor has suggested that Oracle is going to
improve the segment advisor, and many are hoping that enhancements to the
segment advisor will incorporate "workload analysis", like the 10g SQL Access
advisor. There is also work to
predict Oracle table index benefits from rebuild
reorganization.
Chris Foot, author of the
bestselling "OCP
Instructors Guide for Oracle DBA Certification",
has these
notes on the 10g segment advisor output:
The output page also
displays information that will help us determine if shrinking the segment is
justified. The far right of each row displays:
- The amount of space
allocated to the object.
- The amount of space
that the object actually consumes.
- The amount of space
that will be reclaimed if a shrink operation is performed.
- A recommendation
stating if the shrink operation should be performed and if so, what
steps should be taken to perform the shrink effectively.
Also, see my notes on index
rebuilding:
