Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Oracle segment advisor Tips

Oracle Database Tips by Donald Burleson

There has been an ongoing debate about the value of periodic rebuilding of tables and indexes, along two dimensions:

  • Reclaimed storage - The Oracle 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.

Read this outstanding article on the Oracle segment advisor.

According to the Administrator's Guide, the main purpose of the Segment Advisor is to identify 'segments that have space available for reclamation.? For the advisor to work, it must examine the contents of the Automatic Workload Repository, and already discussed, use of the AWR requires additional licensing. Therefore, the use of the Segment Advisor is restricted if not licensed.


The advisor can run on a scheduled basis in addition to a user-directed manual one. If the advisor finds a significant amount of free space, the advice will be to perform an online segment shrink. If not eligible for shrinking, the advice may be to perform an online table redefinition. The advisor will also report on row chaining if the amount found is above a threshold value.


In the automatic mode of analyzing segment information contained in the AWR, the segments of interest are those which:

  • Have the most activity

  • Have the highest growth rate

  • Have exceeded a critical or warning threshold (by tablespace)

The Automatic Segment Advisor job is the entity which selects the segments to be analyzed. If a segment is being analyzed when the maintenance window closes, that segment will be included at the start of the next window.


The advisor advises on three levels:

  • Segment level - for a particular segment, including a partition, index or LOB column

  • Object level - table or index, including partitions, and can include dependent objects

  • Tablespace level - runs for all segments in the tablespace

In OEM, the DBA is taken through a guided workflow consisting of scope, objects, schedule and review.


Figure 8.15:  Tablespace List


A history of Segment Advisor jobs can also be viewed.


Figure 8.16:  Segment Advisor History


The command-line API is surfaced by the DBMS_ADVISOR (and optionally, DBMS_SPACE) PL/SQL built-in. The subprograms are CREATE_TASK, CREATE_OBJECT (identify the target object), SET_TASK_PARAMETER and EXECUTE_TASK.


To view the results, use OEM, query the DBA_ADVISOR_* dynamic views, or use the DBMS_SPACE.ASA_RECOMMENDATION procedure. The dynamic views are categorized under recommendations, findings, actions, and objects. The corresponding views are DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_FINDINGS, DBA_ADVISOR_ACTIONS and DBA_ADVISOR_OBJECTS.


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:

If you like Oracle tuning, you may enjoy the new book "Oracle Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning tips & scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.