重庆思庄Oracle、Redhat认证学习论坛

 找回密码
 注册

QQ登录

只需一步,快速开始

搜索
查看: 532|回复: 0

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

[复制链接]
发表于 2023-6-2 11:28:20 | 显示全部楼层 |阅读模式
本帖最后由 jiawang 于 2023-6-2 11:28 编辑

报错现象:
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.


回复

使用道具 举报

您需要登录后才可以回帖 登录 | 注册

本版积分规则

QQ|手机版|小黑屋|重庆思庄Oracle、Redhat认证学习论坛 ( 渝ICP备12004239号-4 )

GMT+8, 2024-2-29 00:30 , Processed in 0.091462 second(s), 20 queries .

重庆思庄学习中心论坛-重庆思庄科技有限公司论坛

© 2001-2020

快速回复 返回顶部 返回列表