About the Segment Advisor

The Segment Advisor identifies database objects that have unused space that you can reclaim. It performs its analysis by examining usage and growth statistics and by sampling the data in the object. By default, it is configured to run automatically at regular intervals, during all maintenance windows (time periods). You can also run the Segment Advisor manually.

The regularly scheduled Segment Advisor task is known as the Automatic Segment Advisor. Results from the advisor are summarized on the Space Summary section of the home page as Segment Advisor Recommendations.

During each run of the Automatic Segment Advisor, only a subset of the segments in the database are analyzed. If you suspect that segments have reclaimable space, but these segments do not appear among the Segment Advisor recommendations, then the Automatic Segment Advisor may not have chosen them for analysis. You can run the Segment Advisor manually on those segments at any time. See Oracle Database Administrator's Guide for information about how the Automatic Segment Advisor selects tablespaces and segments for analysis.

Segment Advisor Recommendations

Segment Advisor recommendations are grouped by tablespace. Only tablespace names appear on the Segment Advisor Recommendations page in Database Control.

If a tablespace contains any segments for which a shrink operation or reorganization is recommended, then the tablespace appears on the Segment Advisor Recommendations page. This page estimates the amount of reclaimable space for each tablespace. If you request recommendation details for a tablespace, then Database Control displays the segments in that tablespace for which recommendations were generated.

The recommendations provided by the Segment Adviser depend on the characteristics of the tablespace in which the segment is located. If the tablespace was created with default options, then the Segment Advisor typically recommends shrinking. If the segment is not eligible for online segment shrinking, or if it is in a dictionary-managed tablespace or a tablespace created with manual segment space management, then the Segment Advisor recommends reorganization instead.

In addition, the Automatic Segment Advisor evaluates tables that are 10MB or larger and have at least three indexes to determine the amount of space that could be saved if the tables are compressed with the OLTP compression method.

About the Segment Advisor

Segment Advisor Recommendations

Related Topics