ORA-13780: SQL statement does not exist.
The error clearly states that the tuning task is failed because the sql_id is not present in cursor cache.
SQL> DECLARE
l_sql_tune_task_id VARCHAR2(100);
2 BEGIN
3 4 l_sql_tune_task_id := DBMS_SQLTUNE.create_tuning_task (
5 sql_id => '1n7b9udgaz4bv',
6 scope => DBMS_SQLTUNE.scope_comprehensive,
7 time_limit => 3600,
8 task_name => '1n7b9udgaz4bv_tuning_task11',
description => 'Tuning task1 for statement 1n7b9udgaz4bv');
DBMS_OUTPUT.put_line('l_sql_tune_task_id: ' || l_sql_tune_task_id);
END;
9 10 11 12 /
DECLARE
*
ERROR at line 1:
ORA-13780: SQL statement does not exist.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 79
ORA-06512: at "SYS.PRVT_SQLADV_INFRA", line 268
ORA-06512: at "SYS.DBMS_SQLTUNE", line 606
ORA-06512: at line 4
SQL> !oerr ora 13780
13780, 00000, "SQL statement does not exist."
// *Cause: The user attempted to tune a SQL statement that does not exist.
// *Action: Verify the sql_id and the plan hash value of the statement and
// retry the operation.
Extract the sql_id from AWR history and run the advisor report.
SQL> variable v_sql VARCHAR2(64);
SQL> EXEC :v_sql := DBMS_SQLTUNE.CREATE_TUNING_TASK(begin_snap => 769, end_snap => 770, sql_id => '1n7b9udgaz4bv' , scope => 'COMPREHENSIVE', time_limit => 3600, task_name => '1n7b9udgaz4bv_tuning_task');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_SQLTUNE.EXECUTE_TUNING_TASK(task_name => '1n7b9udgaz4bv_tuning_task');
PL/SQL procedure successfully completed.
SQL> set long 65536
set longchunksize 65536
set linesize 100
select dbms_sqltune.report_tuning_task('1n7b9udgaz4bv_tuning_task') from dual;SQL> SQL> SQL>
DBMS_SQLTUNE.REPORT_TUNING_TASK('1N7B9UDGAZ4BV_TUNING_TASK')
----------------------------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name : 1n7b9udgaz4bv_tuning_task
Tuning Task Owner : SYS
Workload Type : Single SQL Statement
Scope : COMPREHENSIVE
Time Limit(seconds): 3600
Completion Status : COMPLETED
Started at : 10/11/2023 17:13:21
Completed at : 10/11/2023 17:13:24
-------------------------------------------------------------------------------
Schema Name : C##HYDRA
Container Name: CDB$ROOT
SQL ID : 1n7b9udgaz4bv
SQL Text : Insert into CUSTOMERS(CUSTOMER_ID,CUST_FIRST_NAME,CUST_LAST_NA
ME,NLS_LANGUAGE,NLS_TERRITORY,CREDIT_LIMIT,CUST_EMAIL,ACCOUNT_
MGR_ID,CUSTOMER_SINCE,CUSTOMER_CLASS,SUGGESTIONS,DOB,MAILSHOT,
PARTNER_MAILSHOT,PREFERRED_ADDRESS,PREFERRED_CARD) values (:1
,:2 ,:3 ,:4 ,:5 ,:6 ,:7 ,:8 ,:9 ,:10 ,:11 ,:12 ,:13 ,:14 ,:15
,:16 )
-------------------------------------------------------------------------------
ERRORS SECTION
-------------------------------------------------------------------------------
- ORA-00904: "PREFERRED_CARD": invalid identifier
-------------------------------------------------------------------------------