9 Replies Latest reply: Feb 3, 2013 12:29 AM by user522961 RSS

    sql_tuning_advisor

    user522961
      Hi,
      on 11g R2 on Win 2008 I run sql_advisor like follow. Would you be kind to say me why I receive ORA-00920 ?
      SQL> DECLARE
        2   my_task_name VARCHAR2(30);
        3   my_sqltext   CLOB;
        4  BEGIN
        5   my_sqltext := 'SELECT A.BUSINESS_UNIT, D.DESCR  FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D
        6   WHERE D.BUSINESS_UNIT LIKE ''I%''  AND D.BUSINESS_UNIT=A.BUSINESS_UNIT  AND D.LAN
        7  GUAGE_CD=''FRA''  UNION ALL SELECT  BUSINESS_UNIT, DESCR FROM PS_SP_RECV1_NONVW A
        8  WHERE BUSINESS_UNIT LIKE ''I%''  AND NOT EXISTS (SELECT ''X'' FROM PS_SP_RCV1_NONVWL
        9  N D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD=''FRA'') ORDER BY 1';
       10
       11   my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
       12           sql_text    => my_sqltext,
       13           user_name   => 'SYSADM',
       14           scope       => 'COMPREHENSIVE',
       15           time_limit  => 60,
       16           task_name   => 'my_sql_tuning_task',
       17           description => 'Task to tune a query on a specified employee');
       18  END;
       19  /
      
      Executed successfully.
      
      SQL> BEGIN
        2    DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
        3  END;
        4  /
      
      Executed successfully.
      
      SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
      
      STATUS
      -----------
      COMPLETED
      
      SQL> SET LONG 5000
      SQL> SET LONGCHUNKSIZE 1000
      SQL> SET LINESIZE 100
      SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task')
        2    FROM DUAL;
      
      DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
      ----------------------------------------------------------------------------------------------------
      GENERAL INFORMATION SECTION
      -------------------------------------------------------------------------------
      Tuning Task Name   : my_sql_tuning_task
      Tuning Task Owner  : SYS
      Workload Type      : Single SQL Statement
      Scope              : COMPREHENSIVE
      Time Limit(seconds): 60
      Completion Status  : COMPLETED
      Started at         : 01/22/2013 09:28:01
      Completed at       : 01/22/2013 09:28:04
      
      
      DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
      ----------------------------------------------------------------------------------------------------
      -------------------------------------------------------------------------------
      Schema Name: user
      SQL ID     : bq7v179ug4ju4
      SQL Text   : SELECT A.BUSINESS_UNIT, D.DESCR  FROM PS_SP_RECV1_NONVW A,
                   PS_SP_RCV1_NONVWLN D
                    WHERE D.BUSINESS_UNIT LIKE 'I%'  AND
                   D.BUSINESS_UNIT=A.BUSINESS_UNIT  AND D.LAN
                   GUAGE_CD='FRA'  UNION ALL SELECT  BUSINESS_UNIT, DESCR FROM
                   PS_SP_RECV1_NONVW A
                   WHERE BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                   PS_SP_RCV1_NONVWL
      
      DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
      ----------------------------------------------------------------------------------------------------
                   N D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                   D.LANGUAGE_CD='FRA') ORDER BY 1
      
      -------------------------------------------------------------------------------
      ERRORS SECTION
      -------------------------------------------------------------------------------
      
      ORA-00920: invalid relational operator 
      Thank you.
        • 1. Re: sql_tuning_advisor
          Fran
          SQL> DECLARE
          2 my_task_name VARCHAR2(30);
          3 my_sqltext CLOB;
          4 BEGIN
          5 my_sqltext := 'SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D
          6 WHERE D.BUSINESS_UNIT LIKE ''I%'' AND D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LAN
          7 GUAGE_CD=''FRA'' UNION ALL SELECT BUSINESS_UNIT, DESCR FROM PS_SP_RECV1_NONVW A
          8 WHERE BUSINESS_UNIT LIKE ''I%'' AND NOT EXISTS (SELECT ''X'' FROM PS_SP_RCV1_NONVWL
          9 N D WHERE D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD=''FRA'') ORDER BY 1';
          10
          11 my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
          12 sql_text => my_sqltext,
          13 user_name => 'SYSADM',
          14 scope => 'COMPREHENSIVE',
          15 time_limit => 60,
          16 task_name => 'my_sql_tuning_task',
          17 description => 'Task to tune a query on a specified employee');
          18 END;
          19 /
          you are using " instead of ' in some cases like "I%" or "FRA"
          • 2. Re: sql_tuning_advisor
            user522961
            Thank but,
            it is not " but two times '.
            Origin it is :
            Like 'I%'
            But since query should be between '; then there was an error. So I inserted extra '.
            • 3. Re: sql_tuning_advisor
              Fran
              ok. then discarded that.
              did you check for any blank spaces or line break?
              • 4. Re: sql_tuning_advisor
                Girish Sharma
                DECLARE
                my_task_name VARCHAR2(30);
                my_sqltext   CLOB;
                BEGIN
                my_sqltext := 'SELECT A.BUSINESS_UNIT, D.DESCR ' ||
                              'FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D ' ||
                              'WHERE D.BUSINESS_UNIT LIKE ''I%'' AND D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LAN GUAGE_CD=''FRA'' ' ||
                              'UNION ALL '||
                              'SELECT  BUSINESS_UNIT,DESCR ' ||
                              'FROM PS_SP_RECV1_NONVW A ' ||
                              'WHERE BUSINESS_UNIT LIKE ''I%''  AND NOT EXISTS ' ||
                                  '(SELECT ''X'' ' ||
                                  'FROM PS_SP_RCV1_NONVWLN D ' ||
                                  'WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD=''FRA'') ORDER BY 1';
                my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                sql_text    => my_sqltext,
                user_name   => 'SYSADM',
                scope       => 'COMPREHENSIVE',
                time_limit  => 60,
                task_name   => 'my_sql_tuning_task',
                description => 'Task to tune a query on a specified employee');
                END;
                /
                I did not test on my machine, but I guess there is error on line no. 9 which is showing something like N D WHERE D., so this seems me typo error though.

                Regards
                Girish Sharma

                Edited by: Girish Sharma on Jan 22, 2013 8:27 PM

                I just edited above post and shall try tomorrow on my test db machine.
                • 5. Re: sql_tuning_advisor
                  Dom Brooks
                  You've got linebreaks in your SQL in the middle of words.
                  Just try running the sql that you've posted as is (without the double quotes though) and it's obvious.

                  It seems much easier to load the sqltext from v$sql or dba_hist_sqltext into the clob variable using the sql_id
                  • 6. Re: sql_tuning_advisor
                    user522961
                    thank you Grisha.
                    ERROR at line 15:
                    ORA-06550: line 15, column 14:
                    PLS-00103: Encountered the symbol "=" when expecting one of the following:
                    . ( * @ % & = - + ; < / > at in is mod remainder not rem
                    <an exponent (**)> <> or != or ~= >= <= <> and or like like2
                    like4 likec between || member submultiset
                    ORA-06550: line 22, column 1:
                    PLS-00103: Encountered the symbol "END" when expecting one of the following:
                    begin function pragma procedure subtype type <an identifier>
                    <a double-quoted delimited-identifier> current cursor delete
                    exists prior
                    Regards.
                    • 7. Re: sql_tuning_advisor
                      user522961
                      Thank you Dom.
                      It is no more in v$sql. Where else can I find its SQL_ID ?
                      Regards.
                      • 8. Re: sql_tuning_advisor
                        Girish Sharma
                        Since you did not provided table structure, I created tables :

                        CREATE TABLE PS_SP_RCV1_NONVWLN(DESCR VARCHAR2(10),LANGUAGE_CD VARCHAR2(10),BUSINESS_UNIT VARCHAR2(10));

                        CREATE TABLE PS_SP_RECV1_NONVW(BUSINESS_UNIT VARCHAR2(10),DESCR VARCHAR2(10));

                        Now, since I created tuning task to test the SQL, so now I drop tuning task :
                        BEGIN
                        DBMS_SQLTUNE.DROP_TUNING_TASK(TASK_NAME=> 'my_sql_tuning_task');
                        END;
                        /
                        
                        PL/SQL procedure successfully completed.
                        
                        Then I said :
                        
                        DECLARE
                        my_task_name VARCHAR2(30);
                        my_sqltext   CLOB;
                        BEGIN
                        my_sqltext := 'SELECT A.BUSINESS_UNIT, D.DESCR ' ||
                                      'FROM PS_SP_RECV1_NONVW A, PS_SP_RCV1_NONVWLN D ' ||
                                      'WHERE D.BUSINESS_UNIT LIKE ''I%'' AND D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD=''FRA'' ' ||
                                      'UNION ALL '||
                                      'SELECT  BUSINESS_UNIT,DESCR ' ||
                                      'FROM PS_SP_RECV1_NONVW A ' ||
                                      'WHERE BUSINESS_UNIT LIKE ''I%''  AND NOT EXISTS ' ||
                                          '(SELECT ''X'' ' ||
                                          'FROM PS_SP_RCV1_NONVWLN D ' ||
                                          'WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD=''FRA'') ORDER BY 1';
                        my_task_name := DBMS_SQLTUNE.CREATE_TUNING_TASK(
                        sql_text    => my_sqltext,
                        user_name   => 'SCOTT',
                        scope       => 'COMPREHENSIVE',
                        time_limit  => 60,
                        task_name   => 'my_sql_tuning_task',
                        description => 'Task to tune a query on a specified employee');
                        END;
                        /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> BEGIN
                          2  DBMS_SQLTUNE.EXECUTE_TUNING_TASK( task_name => 'my_sql_tuning_task' );
                          3  END;
                          4  /
                        
                        PL/SQL procedure successfully completed.
                        
                        SQL> SELECT status FROM USER_ADVISOR_TASKS WHERE task_name = 'my_sql_tuning_task';
                        
                        STATUS
                        -----------
                        COMPLETED
                        
                        SQL> SET LONG 5000
                        SQL> SET LONGCHUNKSIZE 1000
                        SQL> SET LINESIZE 100
                        SQL> SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK( 'my_sql_tuning_task') FROM DUAL;
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                        GENERAL INFORMATION SECTION
                        -------------------------------------------------------------------------------
                        Tuning Task Name   : my_sql_tuning_task
                        Tuning Task Owner  : SCOTT
                        Workload Type      : Single SQL Statement
                        Scope              : COMPREHENSIVE
                        Time Limit(seconds): 60
                        Completion Status  : COMPLETED
                        Started at         : 01/23/2013 10:46:17
                        Completed at       : 01/23/2013 10:46:17
                        
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                        -------------------------------------------------------------------------------
                        Schema Name: SCOTT
                        SQL ID     : 4qbjrvsd40pdr
                        SQL Text   : SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A,
                                     PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND
                                     D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION
                                     ALL SELECT  BUSINESS_UNIT,DESCR FROM PS_SP_RECV1_NONVW A WHERE
                                     BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                                     PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                                     D.LANGUAGE_CD='FRA') ORDER BY 1
                        
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                        -------------------------------------------------------------------------------
                        FINDINGS SECTION (3 findings)
                        -------------------------------------------------------------------------------
                        
                        1- Statistics Finding
                        ---------------------
                          Table "SCOTT"."PS_SP_RCV1_NONVWLN" was not analyzed.
                        
                          Recommendation
                          --------------
                          - Consider collecting optimizer statistics for this table.
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                            execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
                                    'PS_SP_RCV1_NONVWLN', estimate_percent =>
                                    DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
                                    AUTO');
                        
                          Rationale
                          ---------
                            The optimizer requires up-to-date statistics for the table in order to
                            select a good execution plan.
                        
                        2- Statistics Finding
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                        ---------------------
                          Table "SCOTT"."PS_SP_RECV1_NONVW" was not analyzed.
                        
                          Recommendation
                          --------------
                          - Consider collecting optimizer statistics for this table.
                            execute dbms_stats.gather_table_stats(ownname => 'SCOTT', tabname =>
                                    'PS_SP_RECV1_NONVW', estimate_percent =>
                                    DBMS_STATS.AUTO_SAMPLE_SIZE, method_opt => 'FOR ALL COLUMNS SIZE
                                    AUTO');
                        
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                          Rationale
                          ---------
                            The optimizer requires up-to-date statistics for the table in order to
                            select a good execution plan.
                        
                        3- Index Finding (see explain plans section below)
                        --------------------------------------------------
                          The execution plan of this statement can be improved by creating one or more
                          indices.
                        
                          Recommendation (estimated benefit: 59.97%)
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                          ------------------------------------------
                          - Consider running the Access Advisor to improve the physical schema design
                            or creating the recommended index.
                            create index SCOTT.IDX$$_06810001 on SCOTT.PS_SP_RCV1_NONVWLN("LANGUAGE_CD"
                            ,"BUSINESS_UNIT","DESCR");
                        
                          - Consider running the Access Advisor to improve the physical schema design
                            or creating the recommended index.
                            create index SCOTT.IDX$$_06810003 on SCOTT.PS_SP_RECV1_NONVW("BUSINESS_UNIT
                            ","DESCR");
                        
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                          - Consider running the Access Advisor to improve the physical schema design
                            or creating the recommended index.
                            create index SCOTT.IDX$$_06810004 on SCOTT.PS_SP_RCV1_NONVWLN("BUSINESS_UNI
                            T");
                        
                          Rationale
                          ---------
                            Creating the recommended indices significantly improves the execution plan
                            of this statement. However, it might be preferable to run "Access Advisor"
                            using a representative SQL workload as opposed to a single statement. This
                            will allow to get comprehensive index recommendations which takes into
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                            account index maintenance overhead and additional space consumption.
                        
                        -------------------------------------------------------------------------------
                        EXPLAIN PLANS SECTION
                        -------------------------------------------------------------------------------
                        
                        1- Original
                        -----------
                        Plan hash value: 1458605046
                        
                        -------------------------------------------------------------------------------------------
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                        | Id  | Operation            | Name               | Rows  | Bytes | Cost (%CPU)| Time     |
                        -------------------------------------------------------------------------------------------
                        |   0 | SELECT STATEMENT     |                    |     2 |    56 |    10  (20)| 00:00:01 |
                        |   1 |  SORT ORDER BY       |                    |     2 |    56 |     9  (56)| 00:00:01 |
                        |   2 |   UNION-ALL          |                    |       |       |            |          |
                        |*  3 |    HASH JOIN         |                    |     1 |    28 |     5  (20)| 00:00:01 |
                        |*  4 |     TABLE ACCESS FULL| PS_SP_RECV1_NONVW  |     1 |     7 |     2   (0)| 00:00:01 |
                        |*  5 |     TABLE ACCESS FULL| PS_SP_RCV1_NONVWLN |     1 |    21 |     2   (0)| 00:00:01 |
                        |*  6 |    HASH JOIN ANTI    |                    |     1 |    28 |     5  (20)| 00:00:01 |
                        |*  7 |     TABLE ACCESS FULL| PS_SP_RECV1_NONVW  |     1 |    14 |     2   (0)| 00:00:01 |
                        |*  8 |     TABLE ACCESS FULL| PS_SP_RCV1_NONVWLN |     1 |    14 |     2   (0)| 00:00:01 |
                        
                        DBMS_SQLTUNE.REPORT_TUNING_TASK('MY_SQL_TUNING_TASK')
                        ----------------------------------------------------------------------------------------------------
                        -------------------
                        SQL>
                        
                        Why I run successfully, because the sql_text is now looks like this : (got from above sql_text)
                        
                        SQL> SELECT A.BUSINESS_UNIT, D.DESCR FROM PS_SP_RECV1_NONVW A,
                          2  PS_SP_RCV1_NONVWLN D WHERE D.BUSINESS_UNIT LIKE 'I%' AND
                          3  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND D.LANGUAGE_CD='FRA' UNION
                          4  ALL SELECT  BUSINESS_UNIT,DESCR FROM PS_SP_RECV1_NONVW A WHERE
                          5  BUSINESS_UNIT LIKE 'I%'  AND NOT EXISTS (SELECT 'X' FROM
                          6  PS_SP_RCV1_NONVWLN D WHERE  D.BUSINESS_UNIT=A.BUSINESS_UNIT AND
                          7  D.LANGUAGE_CD='FRA') ORDER BY 1
                          8  /
                        
                        no rows selected
                        
                        SQL>
                        
                        It means, if sql_text is running fine on SQL prompt then there will no issue with DBMS_SQLTUNE.REPORT_TUNING_TASK.
                        Regards
                        Girish Sharma
                        • 9. Re: sql_tuning_advisor
                          user522961
                          Thanks.