This discussion is archived
9 Replies Latest reply: Feb 2, 2013 10:29 PM by user522961 RSS

sql_tuning_advisor

user522961 Newbie
Currently Being Moderated
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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    ok. then discarded that.
    did you check for any blank spaces or line break?
  • 4. Re: sql_tuning_advisor
    Girish Sharma Guru
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Guru
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    Thanks.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points