5 Replies Latest reply: Mar 26, 2013 5:43 AM by Debadatta Kar RSS

    SQLSET_ROW

    hqt200475
      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
          >
          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
            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
              >
              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
                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
                  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+