The collector job (Automatic Segment Advisor) analyses segments in the database and compares to AWR reports to produce recommendations which can be reported on.
— 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,
run_duration,
job_name
FROM dba_scheduler_job_run_details
WHERE owner=’SYS’
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.
Below is the process I used to create a simple task to analyse a specific table and an index:
— Create some variables to hold our task details.
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.
EXEC DBMS_ADVISOR.EXECUTE_TASK(:TASK_NAME);
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).
SELECT
RECOMMENDATIONS RECOMMENDATION,
C1 ACTION1,
C2 ACTION2,
C3 ACTION3
FROM
TABLE(DBMS_SPACE.ASA_RECOMMENDATIONS)
WHERE
TASK_ID = :TASK_ID;
— Delete the task at the end.
EXEC DBMS_ADVISOR.DELETE_TASK(:TASK_NAME);
