I face with that error while try to set SQL Plan on one of my sql.

This is the one of he published bug.
Bug 10313110 SQL Advisor raises an ORA-13786 when Trying to Implement a Recommended Parallel Profile
If I try to use below syntax its also through error message such as:
Check the current plan advise:
SET LONG 10000000; COLUMN RECOMMENDATIONS FORMAT A200 WORD_WRAPPED SET pagesize 0 SET LONG 10000000 SET feed OFF SET TRIMSPOOL ON SET linesize 200 SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS RECOMMENDATIONS FROM DUAL; Enter value for task_name: SQL_TUNING_123456789 old 1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('&TASK_NAME') AS RECOMMENDATIONS new 1: SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('SQL_TUNING_1606725746461') AS RECOMMENDATIONS
And then run below
DECLARE l_sql_tune_task_id VARCHAR2(200); BEGIN l_sql_tune_task_id := DBMS_SQLTUNE.accept_sql_profile ( task_name => 'SQL_TUNING_1606725746461', force_match => TRUE, profile_type => DBMS_SQLTUNE.PX_PROFILE); END; /
It may throw below error:
ERROR at line 1: ORA-13786: missing SQL text of statement object "1" for tuning task "SQL_TUNING_1606725746461" ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95 ORA-06512: at "SYS.DBMS_SQLTUNE_INTERNAL", line 16442 ORA-06512: at "SYS.PRVT_SQLPROF_INFRA", line 31 ORA-06512: at "SYS.DBMS_SQLTUNE", line 7544 ORA-06512: at line 4
So please try to set your sql plan by using below syntax:
SQL> execute dbms_sqltune.accept_sql_profile(task_name =>'NEW_PLAN', task_owner => 'ABC ',replace => TRUE);
2 yorum
Comments feed for this article
Nisan 1, 2022 7:36 pm
abansal2001
Use PROFILE_XML CLOB
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_TEXT CLOB IN
PROFILE SQLPROF_ATTR IN
NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
CATEGORY VARCHAR2 IN DEFAULT
VALIDATE BOOLEAN IN DEFAULT
REPLACE BOOLEAN IN DEFAULT
FORCE_MATCH BOOLEAN IN DEFAULT
PROCEDURE IMPORT_SQL_PROFILE
Argument Name Type In/Out Default?
—————————— ———————– —— ——–
SQL_TEXT CLOB IN
PROFILE_XML CLOB IN
NAME VARCHAR2 IN DEFAULT
DESCRIPTION VARCHAR2 IN DEFAULT
CATEGORY VARCHAR2 IN DEFAULT
VALIDATE BOOLEAN IN DEFAULT
REPLACE BOOLEAN IN DEFAULT
FORCE_MATCH BOOLEAN IN DEFAULT
Nisan 3, 2022 9:35 pm
Gunes Erol
Thanks for advice