Oracle: To turn off automated tuning tasks

 While you cannot directly turn off the DBA_AUTOTASK_CLIENT_HISTORY view, you can effectively stop it from being populated by disabling the automated maintenance tasks that write to it. The history is a record of these tasks running in their maintenance windows. 

The standard automated tasks are:

• Auto Optimizer Statistics Collection: Gathers optimizer statistics for database objects automatically.
• Auto Space Advisor: Identifies space-related problems, like segments with growth potential.
• SQL Tuning Advisor: Automatically tunes high-load SQL statements. 

How to disable automated tasks

You can disable these tasks individually or all at once using the DBMS_AUTO_TASK_ADMIN.DISABLE PL/SQL procedure. 

Before you begin

First, check the current status of the automated tasks by running the following query in SQL*Plus as a user with DBA privileges (e.g., SYS or SYSTEM):

sql

SELECT client_name, status FROM dba_autotask_client;

Disable all tasks at once

To disable all automated tasks for all maintenance windows, execute this command: 

sql

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE;
END;
/

This will prevent new entries from being added to the DBA_AUTOTASK_CLIENT_HISTORY view. 

Disable specific tasks

To disable specific tasks individually, use the procedure with the client_name argument. For example: 

Disable Auto Optimizer Statistics Collection:

sql

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto optimizer stats collection',
    operation   => NULL,
    window_name => NULL
  );
END;
/

Disable Auto Space Advisor:

sql

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'auto space advisor',
    operation   => NULL,
    window_name => NULL
  );
END;
/

Disable SQL Tuning Advisor: 

sql

BEGIN
  DBMS_AUTO_TASK_ADMIN.DISABLE(
    client_name => 'sql tuning advisor',
    operation   => NULL,
    window_name => NULL
  );
END;
/

How to re-enable automated tasks

To re-enable any specific task, use the DBMS_AUTO_TASK_ADMIN.ENABLE procedure with the same parameters as the disable command, specifying the client_name. To re-enable all tasks at once, execute the DBMS_AUTO_TASK_ADMIN.ENABLE procedure without any arguments. 

Important considerations

Disabling these tasks can negatively impact database performance over time as automatic statistics gathering and SQL tuning are designed to improve query efficiency. If you disable the automated versions, you should implement a manual approach for these maintenance activities. You will need administrative privileges, such as those of the SYS or SYSTEM user, to use the DBMS_AUTO_TASK_ADMIN package.