ORA-13780: SQL statement does not exist.

Cause:

Specific SQL statement is not available in memory (cursor cache) or may be AWR

kIsH@Xhydra<>DECLARE
          v_tune_task_id varchar2(50);
          BEGIN
                  v_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
                  sql_id => '5qmm8vw5dvy8b',
                  time_limit => 1000,
  2    3    4    5    6    7              task_name => '5qmm8vw5dvy8b_tt',
                  description => 'Tuning advisory for 5qmm8vw5dvy8b');
                  DBMS_OUTPUT.PUT_LINE('v_tune_task_id: '|| v_tune_task_id);
          END;
/
  8    9   10   11  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 257
ORA-06512: at "SYS.DBMS_SQLTUNE", line 778
ORA-06512: at line 4

Check if the SQL existing in cache

kIsH@Xhydra<>select * from v$sql where sql_id='5qmm8vw5dvy8b';

no rows selected

Solution:

Specify the AWR snapshots, in case the sql is stored in disk

DECLARE
	  v_tune_task_id varchar2(50);
	  BEGIN
		  v_tune_task_id := DBMS_SQLTUNE.CREATE_TUNING_TASK (
		  begin_snap =>849,
		  end_snap =>853,
		  sql_id => '5qmm8vw5dvy8b',
		  time_limit => 86400,
		  task_name => '5qmm8vw5dvy8b_tt',
		  description => 'Tuning advisory for 5qmm8vw5dvy8b');
		  DBMS_OUTPUT.PUT_LINE('v_tune_task_id: '|| v_tune_task_id);
	  END;
/

If the sql is not stored in AWR, then sql tuning advisor cannot be generated until the next execution of the same sql

kIsH@Xhydra<>select * from dba_hist_active_sess_history where sql_id='5qmm8vw5dvy8b';

no rows selected

Leave a Reply