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.

 

 

A history of Segment Advisor jobs can also be viewed.

 

 

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: