ORA-13780
Tried to create a SQL tuning task for specific SQL ID, but it failed with ORA-13780.
SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01') task_name from dual;
select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01') task_name from dual
*
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 257
ORA-06512: at "SYS.DBMS_SQLTUNE", line 778
ORA-06512: at line 1
ORA-13780 does not complain about the existence of package DBMS_SQLTUNE. ORA-13780 means that the SQL ID you provided cannot be found in memory, it could be a wrong SQL ID or has been cleared from the SQL area.
Solution
First of all, you should make sure the SQL ID is correct and did exist earlier.
BEGIN_SNAP and END_SNAP
To recover SQL ID, we have to specify a snapshot interval of AWR, BEGIN_SNAP and END_SNAP as input arguments in the function CREATE_TUNING_TASK.
To properly set both parameters in the statement, we should check the most recent AWR snapshot ID and inspect the time interval where SQL ID exists.
In this case, we have a snapshot interval from 16725 to 16728 can be used for the SQL ID.
SQL> select DBMS_SQLTUNE.CREATE_TUNING_TASK ( sql_id => '21zq47mj49f7w', scope => 'COMPREHENSIVE', time_limit => 1800, task_name => 'SQLTUNE_21zq47mj49f7w_0105_01', begin_snap => 16725, end_snap => 16728) task_name from dual;
TASK_NAME
--------------------------------------------------------------------------------
SQLTUNE_21zq47mj49f7w_0105_01
The SQL tuning task has been created. Next, we may execute the SQL tuning task without problem.