Specific sql statement with bind variable tuning
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Specific sql statement with bind variable tuning

  1. #1
    Join Date
    Jul 2009
    Posts
    6

    Specific sql statement with bind variable tuning

    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.

  2. #2
    Join Date
    Jul 2009
    Posts
    6
    THis issue is now resolved.

  3. #3
    Join Date
    Mar 2006
    Location
    Charlotte, NC
    Posts
    865
    would you mind to share the resolution??

    Thanks,
    Vijay Tummala

    Try hard to get what you like OR you will be forced to like what you get.

  4. #4
    Join Date
    Jul 2009
    Posts
    6
    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 ...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width