Oracle Advisors (AutoTasks)

Automatic Database Diagnostic Monitor
TODO:
  • Compression Advisor
  • SPM Evolve Advisor
  • SQL Repair Advisor
  • SQL Performance Analyzer
  • Tune MView
  • SQL Workload Manager
  • Undo Advisor
  • Default Advisor

Check

SET LINESIZE 200

SET PAGESIZE 100

SELECT client_name,

status,

max_duration_last_30_days

FROM dba_autotask_client;


CLIENT_NAME STATUS MAX_DURATION_LAST_30_DAYS---------------------------------------------------------------- -------- ---------------------------------------------------------------------------auto optimizer stats collection ENABLED +000 02:57:53auto space advisor ENABLED +000 01:17:13sql tuning advisor ENABLED +000 01:00:15

SET PAGESIZE 40

SET LINESIZE 200

COLUMN task_name FORMAT a40

SELECT task_name,

TO_CHAR(execution_start,'DD-MON-YYYY HH24:MI:SS'),

TO_CHAR(execution_end,'DD-MON-YYYY HH24:MI:SS'),

status,

recommendation_count

FROM dba_advisor_log

ORDER BY execution_start;

STATUS

  • INITIAL - Initial state of the task; no recommendations are present
  • EXECUTING - Task is currently running
  • COMPLETED - Task successfully completed the analysis operation. Recommendation data can be viewed and reported.
  • INTERRUPTED - Task analysis was interrupted by the user. Recommendation data, if present, can be viewed and reported at this time.
  • CANCELLED
  • FATAL ERROR - A fatal error occurred during the analysis operation. All recommendation data is unusable.

Enable

To enable specific Advisors, see the relevant sub-page of this page

EXEC DBMS_AUTO_TASK_ADMIN.enable;

Disable

To disable specific Advisors, see the relevant sub-page of this page

EXEC DBMS_AUTO_TASK_ADMIN.disable;

Views

  • DBA_ADVISOR_LOG

  • DBA_ADVISOR_TASKS

  • DBA_ADVISOR_FINDINGS

  • DBA_ADVISOR_RECOMMENDATIONS

  • DBA_ADVISOR_ACTIONS

  • DBA_ADVISOR_RATIONALE

  • V$ADVISOR_PROGRESS

  • DBA_AUTOTASK_CLIENT

  • DBA_AUTOTASK_CLIENT_HISTORY

  • DBA_AUTOTASK_CLIENT_JOB

  • DBA_AUTOTASK_JOB_HISTORY

  • DBA_AUTOTASK_OPERATION

  • DBA_AUTOTASK_SCHEDULE

  • DBA_AUTOTASK_TASK

  • DBA_AUTOTASK_WINDOW_CLIENTS

  • DBA_AUTOTASK_WINDOW_HISTORY

Parameters

See subpages for more details

SELECT advisor_name, parameter_name, parameter_value, is_default

FROM dba_advisor_def_parameters