alter日志ORA-13785: missing target object for tuning task

jiawang

报错现象：
2023-04-17T22:00:03.642441+08:00
Errors in file /opt/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_j003_58308.trc:
ORA-12012: error on auto execute of job "SYS"."ORA$AT_SQ_SQL_SW_22107"
ORA-13785: missing target object for tuning task "SYS_AUTO_SQL_TUNING_TASK"
ORA-06512: at "SYS.PRVT_ADVISOR", line 3546
ORA-06512: at "SYS.PRVT_ADVISOR", line 932
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14147
ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 14174
ORA-06512: at "SYS.WRI$_ADV_SQLTUNE", line 8
ORA-06512: at "SYS.PRVT_ADVISOR", line 915
ORA-06512: at "SYS.PRVT_ADVISOR", line 3451
ORA-06512: at "SYS.DBMS_ADVISOR", line 276
ORA-06512: at "SYS.DBMS_SQLTUNE", line 1224
ORA-06512: at line 19


Applies to:  Oracle Database - Standard Edition - Version 12.2.0.1 and later
Information in this document applies to any platform.
SymptomsORA-13785: missing target object for tuning task "SYS_AUTO_SPM_EVOLVE_TASK"
OR
ORA-13785: missing target object for tuning task "SYS_AUTO_SQL_TUNING_TASKK"
Changes
CauseMissing entry from SYS.WRI$_ADV_TASKS object


Sample output
SELECT ID "TASK_ID", NAME, ADVISOR_NAME, DESCRIPTION
FROM SYS.WRI$_ADV_TASKS
WHERE NAME IN ('SYS_AUTO_SPM_EVOLVE_TASK', 'SYS_AUTO_SQL_TUNING_TASK')
TASK_ID NAME ADVISOR_NAME DESCRIPTION
---------------------------------------- ----------------------- ------------------------
2 SYS_AUTO_SPM_EVOLVE_TASK SPM Evolve Advisor Automatic SPM Evolve Task
1 SYS_AUTO_SQL_TUNING_TASK SQL Tuning Advisor Automatic SQL Tuning Task


Solutionfor Symptom of Automatic SPM Evolve Task
SQL> SELECT DBMS_SPM.REPORT_AUTO_EVOLVE_TASK FROM DUAL;
ERROR:
ORA-13785: missing target object for tuning task "SYS_AUTO_SPM_EVOLVE_TASK"  

Verify
SELECT ID, TYPE, TASK_ID, ATTR4, ATTR8 FROM SYS.WRI$_ADV_OBJECTS
WHERE TASK_ID IN (SELECT ID FROM SYS.WRI$_ADV_TASKS WHERE NAME = 'SYS_AUTO_SPM_EVOLVE_TASK')
AND ID = 1;   =>>>>>>>>>> Here this number matters, verify this from cause section

Rectify
INSERT INTO SYS.WRI$_ADV_OBJECTS (TASK_ID, ID, TYPE, ATTR8)
VALUES (<SPM_EVOLVE_TASK_ID>,1,25,0);
COMMIT;
EXECUTE DBMS_SPM.RESET_EVOLVE_TASK('SYS_AUTO_SPM_EVOLVE_TASK');
EXECUTE DBMS_SPM.RESUME_EVOLVE_TASK('SYS_AUTO_SPM_EVOLVE_TASK');

Verify if it is working
select DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK from dual;
#########################################################################################
For Symptom of Automatic SQL Tuning Task (if Licensed for Diag & Tuning Pack only!)
SQL> select DBMS_AUTO_SQLTUNE.REPORT_AUTO_TUNING_TASK from dual;
ERROR:
ORA-13785: missing target object for tuning task "SYS_AUTO_SQL_TUNING_TASK"

Verify
SELECT ID, TYPE, TASK_ID, ATTR4, ATTR8 FROM SYS.WRI$_ADV_OBJECTS
WHERE TASK_ID IN (SELECT ID FROM SYS.WRI$_ADV_TASKS WHERE NAME = 'SYS_AUTO_SQL_TUNING_TASK')
AND ID = 1;    =>>>>>>>>>> Here this number matters, verify this from cause section

Rectify
INSERT INTO SYS.WRI$_ADV_OBJECTS (TASK_ID, ID, TYPE, ATTR4)
VALUES (<SQL_TUNING_TASK_ID>,1,22,'SQL Auto Workload Object');
COMMIT;
EXECUTE DBMS_SQLTUNE.RESET_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK');
EXECUTE DBMS_SQLTUNE.RESUME_TUNING_TASK('SYS_AUTO_SQL_TUNING_TASK');

Verify if it is working
SELECT DBMS_AUTO_SQLTUNE.SYS_AUTO_SQL_TUNING_TASK FROM DUAL;

Above statement might take time depending on the previously interrupted SQL Tuning task query complexity.