Oracle Segment Advisor
Oracle Tips by Burleson Consulting

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.

Traditional Tracing Methods

The older, more traditional tracing methods are listed here with brief discussion only as a reference. More detailed information can be found in Oracle Utilities Using Hidden Programs, Import/Export, SQL Loader, oradebug, Dbverify, Tkprof and More (Dave Moore, Rampant TechPress, 2003).

The tracing can be viewed as either top down or bottom up, and from the specific to the general. A low-level trace would be the explain plan generated in SQL*Plus. The output there is an approximation of what the optimizer knows and is going to do. The hint syntax can be tested here as well, recalling the caution that if the hint type syntax is incorrect, the hint becomes a useless comment.

For more details on Oracle utilities, see the book "Advanced Oracle Utilities" by Bert Scalzo, Donald K. Burleson, and Steve Callan. You can buy it direct from the publisher for 30% off directly from Rampant TechPress.