My Oracle Support Banner

Automatic SQL Tuning Advisor Task Does Not Run At PDB Level with Error ' ORA-13605: The specified task or object SYS_AUTO_SQL_TUNING_TASK does not exist ' (Doc ID 2538576.1)

Last updated on MAY 21, 2021

Applies to:

Oracle Database - Enterprise Edition - Version 12.1.0.2 and later
Information in this document applies to any platform.

Goal

Goal of this document is to explain that Auto SQL Tuning Advisor job runs only at CDB level and does not run at PDB level in multitenant environment.

In a PDB, the Automatic SQL Tuning Advisor job is enabled:

SQL> select status from dba_autotask_client where client_name='sql tuning advisor';
STATUS
--------
ENABLED

However, the below code snippet of AUTO_SQL_TUNING_PROG shows the that it is disabled at PDB level.

SQL> select program_action from dba_scheduler_programs where program_name = 'AUTO_SQL_TUNING_PROG';

...
-- check if we are in a pdb,
-- since auto sqltune is not run in a pdb
IF (exec_task AND -- tuning pack enabled
sys_context('userenv', 'con_id') <> 0 AND -- not in non-cdb
sys_context('userenv', 'con_id') <> 1 ) THEN -- not in root
exec_task := FALSE;
END IF;
-- execute auto sql tuning task
IF (exec_task) THEN
ename := dbms_sqltune.execute_tuning_task(
'SYS_AUTO_SQL_TUNING_TASK');
END IF;

 

 

Solution

To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!


In this Document
Goal
Solution
References


My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.