This discussion is archived
5 Replies Latest reply: Mar 26, 2013 3:43 AM by Debadatta Kar RSS

SQLSET_ROW

hqt200475 Newbie
Currently Being Moderated
Hi experts,

I defined the table user_workload with utlxaa.sql! Then executed the following steps:

1. enter the same sql-statements as in [url http://docs.oracle.com/cd/E11882_01/server.112/e16638/advisor.htm#PFGRF94919]18.2.9.1 Recommendations From a User-Defined Workload -> Step 1 in user_workload,
2. define workload with: "EXECUTE DBMS_SQLTUNE.CREATE_SQLSET('MYWORKLOAD', 'test purposeV');" ,
3. and tried to import the sql-statements in the workload with:
    DECLARE
      sqlset_cur DBMS_SQLTUNE.SQLSET_CURSOR;    /*a sqlset cursor variable*/
    BEGIN
      OPEN  sqlset_cur FOR
      SELECT SQLSET_ROW(null, sql_text,null,null,username,null,null,0,0,0,0,0,0,0,0,0,null,0,0,0,0) AS ROW
             FROM USER_WORKLOAD;

      DBMS_SQLTUNE.LOAD_SQLSET('MYWORKLOAD', sqlset_cur);
    END; 
or simply:
SELECT SQLSET_ROW(null, sql_text,null,null,username,null,null,0,0,0,0,0,0,0,0,0,null,0,0,0,0) AS ROW
             FROM USER_WORKLOAD;
I got the error:
ERROR at line 1:
ORA-00923: FROM keyword not found where expected
ORA-06512: at line 4
The procedure worked if I removed the term "as row" and modified the SQLSET_ROW as:
 SELECT SQLSET_ROW(null,0, sql_text,null,null,'SH',null,null,0,0,0,0,0,0,0,0,0,0,         
                         null,0,0,null,0,0,null,0,null,null) FROM USER_WORKLOAD;
But after executing the next steps I got no recommendations at all.

My questions:
1. Is "as row" an error of oracle documentation?
2. Although I have the same sql-statements of the sample 18.2.9.1, why did I  get no recommendations at all ?
Thank for any help!

best regards

hqt200475
  • 1. Re: SQLSET_ROW
    rp0428 Guru
    Currently Being Moderated
    >
    1. Is "as row" an error of oracle documentation?
    2. Although I have the same sql-statements of the sample 18.2.9.1, why did I get no recommendations at all ?
    >
    Take my comments as suggestions only since I have no experience with the advisor.

    1. AS ROW is just an alias and isn't even needed. Like when you 'SELECT COLA as my_COLA, COLB . . .' the 'as my_COLA' is the alias for the column.

    The second issue is at least a documentation error.

    The SQLSET_ROW object type definition doesn't match what the documentation is using.
    As you found the doc and your first code use
    SQLSET_ROW(null, sql_text,null,null,username,null,null,0,0,0,0,0,0,0,0,0,null,0,0,0,0)
    But the type speci shows the second argument should be the 'force_matching_signature' parameter which is why your second code compiles
    SQLSET_ROW(null,0, sql_text,null,null,'SH',null,null,0,0,0,0,0,0,0,0,0,0,null,0,0,null,0,0,null,0,null,null)
    As to why you don't get any recommendations - don't know but I did notice another discrepency between the arguments being passed and the type definition.
    The SQLSET_ROW type definition has 10 numbers between the two 'null' arguments in your code but your code (and the doc sample) have only 9.

    So your code is using a NULL for the optimizer_code parameter and maybe a zero should be used.
    There may be other mismatches. Check the type definition against your SQLSET_ROW constructor to see if anything else looks different.

    See the SQLSET_ROW Object Type description in chapter 125 DBMS_SQLTUNE of the packages and types doc
    http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_sqltun.htm
    CREATE OR REPLACE
    TYPE SYS.SQLSET_ROW AS object (
      --
      -- sql tuning set basic attributes
      --
      sql_id                   VARCHAR(13),                      /* unique SQL ID */
      force_matching_signature NUMBER,          /* literals, case, spaces removed */
      sql_text                 CLOB,                    /* unique SQL hache value */
      object_list              sql_objects,    /* objects referenced by this stmt */
      bind_data                RAW(2000),   /* bind data as captured for this SQL */
      parsing_schema_name      VARCHAR2(30),    /* schema where the SQL is parsed */
      module                   VARCHAR2(48),      /* last app. module for the SQL */
      action                   VARCHAR2(32),      /* last app. action for the SQL */
      elapsed_time             NUMBER,     /* elapsed time for this SQL statement */
      cpu_time                 NUMBER,                   /* CPU time for this SQL */
      buffer_gets              NUMBER,                   /* number of buffer gets */
      disk_reads               NUMBER,                   /* number of disk reads  */
      direct_writes            NUMBER,                 /* number of direct writes */
      rows_processed           NUMBER,    /* number of rows processed by this SQL */
      fetches                  NUMBER,                       /* number of fetches */
      executions               NUMBER,            /* total executions of this SQL */
      end_of_fetch_count       NUMBER,    /* exec. count fully up to end of fetch */
      optimizer_cost           NUMBER,             /* Optimizer cost for this SQL */
      optimizer_env            RAW(2000),                /* optimizer environment */
      priority                 NUMBER,           /* user-defined priority (1,2,3) */
      command_type             NUMBER,      /* statement type - like INSERT, etc. */
      first_load_time          VARCHAR2(19),        /* load time of parent cursor */
      stat_period              NUMBER,       /* period of time (seconds) when the */
                               /* statistics of this SQL statement were collected */
      active_stat_period       NUMBER,    /* effecive period of time (in seconds) */
                                     /* during which the SQL statement was active */
      other                    CLOB,  /* other column for user defined attributes */
      plan_hash_value          NUMBER,             /* plan hash value of the plan */
      sql_plan                 sql_plan_table_type,               /* explain plan */
      bind_list                sql_binds, /* list of user specified binds for Sql */
                                 /* NOTICE: bind_list and bind_data are exclisive */
    
      --
      -- define a constructor that has default values for sqlset attributes.
      --
      CONSTRUCTOR FUNCTION sqlset_row(
        sql_id                   VARCHAR2            := NULL,
        force_matching_signature NUMBER              := NULL,
        sql_text                 CLOB                := NULL,
        object_list              sql_objects         := NULL,
        bind_data                RAW                 := NULL,
        parsing_schema_name      VARCHAR2            := NULL,
        module                   VARCHAR2            := NULL,
        action                   VARCHAR2            := NULL,
        elapsed_time             NUMBER              := NULL,
        cpu_time                 NUMBER              := NULL,
        buffer_gets              NUMBER              := NULL,
        disk_reads               NUMBER              := NULL,
        direct_writes            NUMBER              := NULL,
        rows_processed           NUMBER              := NULL,
        fetches                  NUMBER              := NULL,
        executions               NUMBER              := NULL,
        end_of_fetch_count       NUMBER              := NULL,
        optimizer_cost           NUMBER              := NULL,
        optimizer_env            RAW                 := NULL,
        priority                 NUMBER              := NULL,
        command_type             NUMBER              := NULL,
        first_load_time          VARCHAR2            := NULL,
        stat_period              NUMBER              := NULL,
        active_stat_period       NUMBER              := NULL,
        other                    CLOB                := NULL,
        plan_hash_value          NUMBER              := NULL,
        sql_plan                 sql_plan_table_type := NULL,
        bind_list                sql_binds           := NULL)
        RETURN SELF AS RESULT
    )
    /
    Hope these mismatches help you find the problem.
  • 2. Re: SQLSET_ROW
    hqt200475 Newbie
    Currently Being Moderated
    Hi rp0428,

    thanks for your suggestions!

    you wrote:
    1. AS ROW is just an alias and isn't even needed. Like when you 'SELECT COLA as my_COLA, COLB . . .' the 'as my_COLA' is the alias for the column.
    It's true if COLA is an expression ( a univalent field), but it doesn't work if COLA is an object! Constructor SQLSET_ROW returns an object!

    I show here the results in both cases( with and without 'as row' ):
    [oracle@stb ~]$ sqlplus sh/sh@vldb
    
    SQL*Plus: Release 11.2.0.3.0 Production on Wed Apr 4 10:17:26 2012
    
    Copyright (c) 1982, 2011, Oracle.  All rights reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
    With the Partitioning, Real Application Clusters, Automatic Storage Management,                                                OLAP,
    Data Mining and Real Application Testing options
    
    SQL> SELECT SQLSET_ROW(null,0, sql_text,null,null,'SH',null,null,0,0,0,0,0,0,0,0,0,0,null,0,0,null,0,0,null,0,null,null) FROM USER_WORKLOAD;
    
    SQLSET_ROW(NULL,0,SQL_TEXT,NULL,NULL,'SH',NULL,NULL,0,0,0,0,0,0,0,0,0,0,NULL,0,0
    --------------------------------------------------------------------------------
    SQLSET_ROW(NULL, 0, 'SELECT t.week_ending_day, p.prod_subcategory,
    sum(s.amount_sold) AS dollars,
    s.c', NULL, NULL, 'SH', NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL, 0, 0, NU
    LL, 0, 0, NULL, 0, NULL, NULL)
    
    SQLSET_ROW(NULL, 0, ' SELECT t.calendar_month_desc, sum(s.amount_sold) AS dollar
    s
    FROM sales s , tim', NULL, NULL, 'SH', NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0,
     NULL, 0, 0, NULL, 0, 0, NULL, 0, NULL, NULL)
    
    SQLSET_ROW(NULL, 0, 'SELECT ch.channel_class, c.cust_city, t.calendar_quarter_de
    
    SQLSET_ROW(NULL,0,SQL_TEXT,NULL,NULL,'SH',NULL,NULL,0,0,0,0,0,0,0,0,0,0,NULL,0,0
    --------------------------------------------------------------------------------
    sc,
    SUM(s.amount_sol', NULL, NULL, 'SH', NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, N
    ULL, 0, 0, NULL, 0, 0, NULL, 0, NULL, NULL)
    
    SQLSET_ROW(NULL, 0, ' SELECT c.country_id, c.cust_city, c.cust_last_name
    FROM customers c
    WHERE c.cou', NULL, NULL, 'SH', NULL, NULL, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, NULL,
    0, 0, NULL, 0, 0, NULL, 0, NULL, NULL)
    
    
    SQL> SELECT SQLSET_ROW(null,0, sql_text,null,null,'SH',null,null,0,0,0,0,0,0,0,0,0,0,null,0,0,null,0,0,null,0,null,null) as row FROM USER_WORKLOAD;
    SELECT SQLSET_ROW(null,0, sql_text,null,null,'SH',null,null,0,0,0,0,0,0,0,0,0,0,null,0,0,null,0,0,null,0,null,null) as row FROM USER_WORKLOAD
                                                                                                                           *
    ERROR at line 1:
    ORA-00923: FROM keyword not found where expected
    
    
    SQL>
    you wrote:
    The SQLSET_ROW type definition has 10 numbers between the two 'null' arguments  in your code but your code (and the doc sample) have only 9.
    
    So your code is using a NULL for the optimizer_code parameter and maybe a zero should be used.
    There may be other mismatches. Check the type definition against your SQLSET_ROW constructor to see if anything else looks different.
    You're right, I made a type error as I posted the question! It must be 10 zeros after 'SH', 'null','null' arguments.


    you wrote:
    So your code is using a NULL for the optimizer_code parameter and maybe a zero should be used.
    There may be other mismatches.
    My question and answer:
    do you mean optimizer_cost?
    
    If yes, I set 10 zeros after  'SH',null,null,
    thanks and regards

    hqt200475

    Edited by: hqt200475 on Apr 4, 2012 3:02 AM

    Edited by: hqt200475 on Apr 4, 2012 7:00 AM
  • 3. Re: SQLSET_ROW
    rp0428 Guru
    Currently Being Moderated
    >
    It's true if COLA is an expression ( a univalent field), but it doesn't work if COLA is an object! Constructor SQLSET_ROW returns an object!
    >
    Sure it works - it has nothing to do with objects. You can't use a reserved word
    select ename from emp where empno = 7369
    ENAME
    SMITH
    
    select ename as row from emp where empno = 7369
    ORA-00923: FROM keyword not found where expected
    Never use reserved words - all kinds of strange things can happen.
    http://docs.oracle.com/cd/B19306_01/em.102/b40103/app_oracle_reserved_words.htm
  • 4. Re: SQLSET_ROW
    hqt200475 Newbie
    Currently Being Moderated
    Hi rp0428,

    with the assertion:
    1. AS ROW is just an alias and isn't even needed. Like when you 'SELECT COLA as my_COLA, COLB . . .' the 'as my_COLA' is the alias for the column.
    you're right

    regards

    hqt200476
  • 5. Re: SQLSET_ROW
    Debadatta Kar Newbie
    Currently Being Moderated
    I have used the examples given by all of you and tried to run following SQL individually. I am getting some error. Please correct me.

    +SQL>       SELECT SQLSET_ROW(null,0, sql_text,null,null,'DORIS',null,null,0,0,0,0,0,0,0,0,0,0,null,0,0,null,0,0,null,0,null,null)
    2 FROM user_workload;
    SELECT SQLSET_ROW(null,0, sql_text,null,null,'DORIS',null,null,0,0,0,0,0,0,0,0,0,0,null,0,0,null,0,0,null,0,null,null)
    *
    ERROR at line 1:
    ORA-22370: incorrect usage of method+

Legend

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