Specific sql statement with bind variable tuning

[dbcop]

Hi

I am trying to run dbms_sqltune package on a specific sql . DB version is 10.2.4 OS Platform AIX

I did the following steps

Step1.
SQL> DECLARE
2 my_task_name VARCHAR2 (30);
3 my_sqltext CLOB;
4 BEGIN
5 my_sqltext := 'SELECT T_00 . "KNUMA_TEMP" , T_00 . "VBELN" , T_00 . "AEDAT" , T_00 .
6 "ERDAT" , T_00 . "POSNR" , T_00 . "MEINS" , T_00 . "NETPR" , T_00 .
7 "WAERK" , T_00 . "ABGRU" , T_00 . "GEWEI" , T_00 . "LGORT" , T_00 .
8 "VSTEL" , T_00 . "VRKME" , T_00 . "ROUTE" , T_00 . "WERKS" , T_00 .
9 "MATNR" , T_00 . "MATKL" , T_00 . "VGPOS" , T_00 . "VGBEL" , T_00 .
10 "PRODH" , T_00 . "FAKSP" , T_01 . "KUNNR" , T_01 . "VSBED" , T_01 .
11 "VBTYP" , T_01 . "ERNAM" , T_01 . "VKORG" , T_01 . "AUART" , T_01 .
"AUGRU" , T_01 . "BNDDT" , T_01 . "ANGDT" , T_01 . "KVGR5" , T_01 .
12 13 "KVGR4" , T_01 . "KVGR3" , T_01 . "KVGR2" , T_01 . "KVGR1" , T_01 .
14 "VKGRP" , T_01 . "VKBUR" , T_01 . "AUDAT" , T_01 . "VGTYP" , T_01 .
15 "SPART" , T_01 . "VTWEG" , T_01 . "VDATU" , T_01 . "AUTLF" , T_01 .
16 "LIFSK" , T_01 . "FAKSK" , T_01 . "VBELN" FROM "SAPR3"."VBAP" T_00 , "SAPR3"."VBAK"
T_01 WHERE ( T_01 . "MANDT" = :A0 AND T_01 . "VBELN" = T_00 . "VBELN"
17 18 ) AND T_00 . "MANDT" = :A1 AND T_00 . "ERDAT" BETWEEN :A2 AND :A3';
19 my_task_name := dbms_sqltune.create_tuning_task (sql_text=> my_sqltext,
20 bind_list => sql_binds (anydata.convertnumber (9)),
21 user_name => 'SYS',
22 scope => 'COMPREHENSIVE',
time_limit => 60,
23 24 task_name => 'm41b_tuning_task',
25 description => 'Tuning Task'
26 );
27 END;
28 /

Step2:
BEGIN
dbms_sqltune.execute_tuning_task (task_name => 'm41b_tuning_task');
END;
/ 2 3 4

PL/SQL procedure successfully completed.


Step3:
SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'm41b_tuning_task';

STATUS
-----------
COMPLETED

SQL> SET LONG 1000
SET LONGCHUNKSIZE 1000
SET LINESIZE 100
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'm41b_sql_tuning_task')
FROM DUAL;

SQL> ERROR:
ORA-13605: The specified task or object m41b_sql_tuning_task does not exist for the current user.
ORA-06512: at "SYS.DBMS_SYS_ERROR", line 86
ORA-06512: at "SYS.PRVT_ADVISOR", line 4744
ORA-06512: at "SYS.DBMS_SQLTUNE", line 1082
ORA-06512: at line 1




But I see the task is owned by SYS user
SQL> select task_name,owner from dba_advisor_log where owner in ('SYS','SAPR3') and task_name like'm41%';

TASK_NAME OWNER
------------------------------ ------------------------------
m41b_tuning_task SYS

Actually I had gone thru the
Create SQL_ADVISOR Job Fails on Error ORA-13605 [ID 549285.1] in metalink article before posting this message . I am not using EM/Grid Control etc for this sql tuning instead I am trying it from sqlplus

So where do I set the following then


SQL> sho parameter nls_language

NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
nls_language string AMERICAN
SQL>



Please help me resolving this.

[dbcop]

THis issue is now resolved.

[vnktummala]

would you mind to share the resolution??

Thanks,

[dbcop]

A small typo error can sometimes drive one crazy .

SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'm41b_sql_tuning_task')
FROM DUAL;


Should have been 'm41b_tuning_task'

My mistake sorry ...