In Amazon RDS for Oracle 19c, you can disable the SQL Tuning Advisor and Auto Space Advisor jobs using the Amazon RDS procedure rdsadmin.rdsadmin_util.advisor_task_drop. This procedure allows you to disable specific advisor tasks without requiring shell access or advanced privileges.

To disable the SQL Tuning Advisor job (ORA$AT_WGRP_SQ), you can use the following command:

EXEC rdsadmin.rdsadmin_util.advisor_task_drop('AUTO_SQL_TUNING_TASK')

For the Auto Space Advisor job (ORA$AT_WGRP_SA), you can use:

EXEC rdsadmin.rdsadmin_util.advisor_task_drop('AUTO_SPACE_ADVISOR_TASK')

It's important to note that while disabling these tasks can potentially reduce CPU and memory utilization, it may also impact the database's ability to self-tune and optimize performance. Make sure you have carefully considered the implications before disabling these advisor tasks.

If you need to re-enable these tasks in the future, you can use the rdsadmin.rdsadmin_util.dbms_stats_init procedure.

Remember that in Amazon RDS, you don't have the same level of control as in a standalone Oracle database. Always refer to the Amazon RDS documentation for Oracle-specific procedures and best practices when managing advisor tasks and other database features.

Sources
Performing miscellaneous tasks for Oracle DB instances - Amazon Relational Database Service

Hello Customer,

In Oracle RDS (Amazon Relational Database Service), you don't have direct access to the underlying operating system or the ability to modify system-level configurations. However, you can still disable or modify certain database features and jobs through SQL commands or by modifying database parameters.

To disable the SQL Tuning Advisor and Auto Space Advisor jobs in Oracle RDS, you can also follow these steps :

You can use use RDS Oracle Master User to execute below commands to disable these features :

###############################

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

PL/SQL procedure successfully completed.

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

PL/SQL procedure successfully completed.

You can verify whether these features are disabled using below query

SQL> SELECT client_name, status FROM dba_autotask_client;

CLIENT_NAME STATUS

auto optimizer stats collection ENABLED auto space advisor DISABLED sql tuning advisor DISABLED

###########################################

Please note that modifying database parameters may require a database restart or a failover event for the changes to take effect. Additionally, disabling these advisors may impact certain database features or functionalities, so it's recommended to carefully evaluate the potential impact before making any changes.

I sincerely hope that the above information is helpful to you, if you have any additional questions don't hesitate to reach out to me and I will be happy to assist.

References :

[1] Performing miscellaneous tasks for Oracle DB instances - https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Misc.html

The SQL Tuning Advisor and Auto space advisor can be stopped using rdsadmin.rdsadmin_util.advisor_task_drop procedure.

An example : To disable AUTO_STATS_ADVISOR_TASK, use the Amazon RDS procedure rdsadmin.rdsadmin_util.advisor_task_drop.

The following command drops AUTO_STATS_ADVISOR_TASK.

EXEC rdsadmin.rdsadmin_util.advisor_task_drop('AUTO_STATS_ADVISOR_TASK')

Replace the Job name here with SQL Tuning advisor and Auto space advisor name.