DISABLE AUTOMATIC SQL ADVISOR

Check if anything is enabled

select client_name, status from dba_autotask_operation;


SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME                                                      STATUS
---------------------------------------------------------------- --------
auto optimizer stats collection                                  ENABLED
auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

How to Enable or DISABLE Automatic SQL Tuning

To ENABLE Automatic SQL Tuning, use the ENABLE procedure as shown below

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

To DISABLE Automatic SQL Tuning, use the DISABLE procedure as shown below

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

Connected to:

Oracle Database 18c Standard Edition 2 Release 18.0.0.0.0 - Production

Version 18.3.0.0.0

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               ENABLED

SQL> BEGIN

DBMS_AUTO_TASK_ADMIN.DISABLE(

client_name => 'sql tuning advisor',

operation => NULL,

window_name => NULL);

END;  2    3    4    5    6

  7  /

PL/SQL procedure successfully completed.

SQL> select client_name, status from dba_autotask_operation;

CLIENT_NAME                                                      STATUS

---------------------------------------------------------------- --------

auto optimizer stats collection                                  ENABLED

auto space advisor                                               ENABLED

sql tuning advisor                                               DISABLED

SQL>