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