It's useful to run the advisor sometimes on large indexes as it should be able to report whether the index is efficiently storing index records, or if it could be re-built.
Oracle Enterprise Manager is already capable of pulling Space Advisor information from Oracle database.
Here are some links to the Oracle docs:
Oracle Doc: 10g Advisors
Oracle Doc: 10g Segment Advisor
The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
You can check the last run of the job using the SQL below:
-- Check the DBA SEGMENT ADVISOR job is collecting data.
COL actual_start_date FORMAT A20
COL run_duration FORMAT A15
COL job_name FORMAT A30
SELECT TO_CHAR(actual_start_date,'DD-MM-YY HH24:MI:SS') start_date,
AND job_name = 'AUTO_SPACE_ADVISOR_JOB'
ORDER BY actual_start_date;
Once the collector is running, you can query the database using the DBMS_ADVISOR package to create analysis tasks that will query the recommendations and produce a recommendation report.
NOTE: The collector job does not analyse every object.
Below is the process I used to create a simple task to analyse a specific table and an index:
VARIABLE TASK_ID NUMBER;
VARIABLE TASK_NAME VARCHAR2(100);
VARIABLE OBJECT_ID NUMBER;
-- Create a new empty task, which will populate the variables just defined.
EXEC DBMS_ADVISOR.CREATE_TASK('Segment Advisor', :TASK_ID, :TASK_NAME);
-- Assign a table and index check to the new task (replace <TABLE NAME> and <INDEX NAME>).
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'TABLE', '<SCHEMA>', '<TABLE NAME>', NULL, NULL, :OBJECT_ID);
EXEC DBMS_ADVISOR.CREATE_OBJECT(:TASK_NAME, 'INDEX', '<SCHEMA>', '<INDEX NAME>', NULL, NULL, :OBJECT_ID);
-- Execute the task.
NOTE: On a 40GB table this took approximately 10 minutes.
-- Check the results in two ways:
-- Query the tables DBA_ADVISOR_FINDINGS, DBA_ADVISOR_RECOMMENDATIONS, DBA_ADVISOR_ACTIONS.
-- or use DBMS_SPACE package (recommended).
TASK_ID = :TASK_ID;
-- Delete the task at the end.