Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation







 Oracle Segment Advisor
Oracle Tips by Burleson Consulting

Advanced Oracle Utilities: The Definitive Reference by Rampant TechPress is written by the top Oracle database experts (Bert Scalzo, Donald Burleson, and Steve Callan).  The following is an excerpt from the book.

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.

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.

r 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.


Remote DBA Service

Oracle Tuning Book


Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software










BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.