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.
DBA_HIST_SNAPSHOT
To know what interval should be used, you may check AWR history in Enterprise Manager Express, or perform a query on DBA_HIST_SNAPSHOT for sure.
column begin_interval_time format a20;
alter session set nls_timestamp_format='yyyy-mm-dd hh24:mi:ss';
select snap_id, begin_interval_time from dba_hist_snapshot order by 1;
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.