Tuesday, July 24, 2012

ORA-13767 ORA-13780 creating SQL Tuning Task in 11.2.0.3


Tried to create a SQL Tuning Task from Grid Control 11g in a 11.2.0.3 database and I got a ORA-13767 error.
Tried to create it by running DBMS_SQLTUNE.CREATE_TUNING_TASK directly to the target database and got a ORA-13780.
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 L_SQL_TUNE_TASK_ID VARCHAR2(100);
3 BEGIN
4 L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
5 SQL_ID => 'fcs0af97qvuft',
6 SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
7 TIME_LIMIT => 600,
8 TASK_NAME => 'fcs0af97qvuft_task',
9 DESCRIPTION => 'Tuning task for fcs0af97qvuft');
10 DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
11 END;
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 125
ORA-06512: at "SYS.DBMS_SQLTUNE", line 655
ORA-06512: at line 4


Installed DB Control in the target database and I got a ORA-13780 again.

If I specify in the task 2 snapshots that include an execution of the particular SQL statement, I get no error:
SQL> SET SERVEROUTPUT ON
SQL>
SQL> DECLARE
2 L_SQL_TUNE_TASK_ID VARCHAR2(100);
3 BEGIN
4 L_SQL_TUNE_TASK_ID := DBMS_SQLTUNE.CREATE_TUNING_TASK (
5 BEGIN_SNAP=>3935,
6 END_SNAP=>3937,
7 SQL_ID => 'fcs0af97qvuft',
8 SCOPE => DBMS_SQLTUNE.SCOPE_COMPREHENSIVE,
9 TIME_LIMIT => 600,
10 TASK_NAME => 'fcs0af97qvuft_task',
11 DESCRIPTION => 'Tuning task for fcs0af97qvuft');
12 DBMS_OUTPUT.PUT_LINE('l_sql_tune_task_id: ' || L_SQL_TUNE_TASK_ID);
13 END;
14 /
l_sql_tune_task_id: fcs0af97qvuft_task

PL/SQL procedure successfully completed.

You can find in which snapshots your query exists, by running the following query:
SELECT SNAP_ID
FROM DBA_HIST_SQLSTAT
WHERE SQL_ID='fcs0af97qvuft'
ORDER BY SNAP_ID;

The only relevant Oracle Note is 1445188.1, but it states that Bug 9957725 is fixed in DB Control 11.2.0.3 and 12g.

Oracle filed Bug 14342949 for this issue.

20/11/2012: From Oracle Development:
It looks like the error message is expected. Once the SQL is selected and you are on the SQL Details page : If the Statistics tab / panel shows "Snapshot Time : date/time to date/time : No data is available for this statement in this snapshot duration".

Selecting the "Schedule SQL Tuning Advisor" will run the dbms_sqltune.create_tuning_task with that begin_snap and end_snap, which correctly results in the ORA-13780 error. The screen will default to the current date/time, so customers need to select a snapshot on the statistics tab/pane with data prior to calling the "Schedule SQL Tuning Advisor".

It seems to be expected behaviour.

No comments:

Post a Comment