6 Replies Latest reply: Oct 27, 2012 11:22 AM by odie_63 RSS

    Xquery ,Clob and Ora:view being used..Kindly suggest

    user8941550
      Our approach is like:

      - We are doing all the processing /business logic in Xquery.
      Then in Oracle we store our entire xquery code in the form of clob.


      The code is like:

      Create Function FunctionName(ExecDate In Date)
      Return XMLTYPE AS

      v_result XMLTYPE;

      v_xquerycode1 clob := to_clob('declare.......all the xquery code');
      v_xquerycode2 clob := to_clob('remaining code from v_xquerycode1.......');
      v_xquerycode3 clob := to_clob('let $p1 := ora:view("T1")/ROW//Root:MessageStart
      'let $p2 := ora:view("T2")/ROW//Root:MessageStart
      Func(p1, p2, asofdate) ');
      --Then we club the clobs as:
      DBMS_LOB.APPEND(v_xquerycode1 ,v_xquerycode2);
      DBMS_LOB.APPEND(v_xquerycode1 ,v_xquerycode3 );

      -- Call the xquerycode as
      select "COLUMN_VALUE"
      INTO v_result
      from XMLTable(v_xquerycode1
      PASSING ExecDate AS "reportDate"
      )Name;

      return v_result ;

      End FunctionName;

      - In the DB we have stored xml files to be processed in tables T1 and T2.
      T1 structure:
      XMLID Number
      XMLSource varchar2(20)
      XMLContent XMLType
      .............

      T1 structure:
      XMLID Number
      XMLSource varchar2(20)
      XMLContent XMLType
      .............

      XmlContent column is storing the XML Files.

      Then when I query as :
      select XMLType.getCLOBVal(FunctionName(to_date(Sysdate, 'yyyymmdd'))) from dual


      It takes hours without executing
      If I reduce the trades to 100 instead of 1000s then it gets executed within a minute.

      Please suggest a better way here.
        • 1. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
          odie_63
          - We are doing all the processing /business logic in Xquery.
          Then in Oracle we store our entire xquery code in the form of clob.
          What's the database version?
          I guess probably 11.x since you can use a dynamic XQuery string...

          That would certainly not fit in my top-10 list of the best designs regarding XML in the db.

          Dynamic XQuery : security issues, no optimization possible...
          Please suggest a better way here.
          Why do you need dynamic XQuery?
          What's the underlying storage of the XMLType columns?

          Could you give a test case? Sample data + expected result?
          • 2. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
            user8941550
            Hi Odie,

            Thanks for the reply. We are using Oracle 11g R2.

            So when you say:
            Dynamic XQuery : security issues, no optimization possible...

            Does that mean, even if I create any XMLIndexes on the Xpath, they won't be used?



            Could you give a test case..Sample data.. expected result..
            There are more than 50,000 xml files. One XML file is around 350 lines. We need to derive around 7-8 tags from every xml based on some conditions and perform calculations on them.

            Kindly suggest any ways to improve the process..

            Edited by: user8941550 on Oct 25, 2012 8:18 PM

            Edited by: user8941550 on Oct 25, 2012 8:19 PM
            • 3. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
              odie_63
              We are using Oracle 11g R2.
              As there are differences between versions in this release (new parser, new features etc.) it's important to know the exact version :
              SELECT * FROM v$version
              So when you say:
              Dynamic XQuery : security issues, no optimization possible...

              Does that mean, even if I create any XMLIndexes on the Xpath, they won't be used?
              Exactly.
              It's easy to check for yourself, even on a simple example.

              With a static XQuery string :
              Connected to:
              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
              
              SQL> drop table tmp_xml;
              
              Table dropped.
              
              SQL> create table tmp_xml of xmltype;
              
              Table created.
              
              SQL> create index tmp_xml_ix on tmp_xml (object_value)
                2  indextype is xdb.xmlindex;
              
              Index created.
              
              SQL> insert into tmp_xml values(xmlparse(document '<root><item>ABC</item></root>'));
              
              1 row created.
              
              SQL> commit;
              
              Commit complete.
              
              SQL> set autotrace traceonly explain
              SQL> set lines 120
              SQL> select x.*
                2  from tmp_xml t
                3     , xmltable('/root/item' passing t.object_value
                4         columns item varchar2(30) path '.'
                5       ) x
                6  ;
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 4160540934
              
              ---------------------------------------------------------------------------------------------------------------
              | Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
              ---------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT             |                                |     1 |  3034 |     3   (0)| 00:00:01 |
              |   1 |  NESTED LOOPS                |                                |       |       |            |       |
              |   2 |   NESTED LOOPS               |                                |     1 |  3034 |     3   (0)| 00:00:01 |
              |   3 |    TABLE ACCESS FULL         | TMP_XML                        |     1 |    12 |     3   (0)| 00:00:01 |
              |*  4 |    INDEX RANGE SCAN          | SYS77583_TMP_XML_IX_PIKEY_IX   |     1 |       |     0   (0)| 00:00:01 |
              |*  5 |   TABLE ACCESS BY INDEX ROWID| SYS77583_TMP_XML_IX_PATH_TABLE |     1 |  3022 |     0   (0)| 00:00:01 |
              ---------------------------------------------------------------------------------------------------------------
              
              Predicate Information (identified by operation id):
              ---------------------------------------------------
              
                 4 - access("T".ROWID="SYS_P0"."RID" AND "SYS_P0"."PATHID"=HEXTORAW('2DF1') )
                 5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
              
              Note
              -----
                 - dynamic sampling used for this statement (level=2)
              With a dynamic XQuery string :
              SQL> var xq varchar2(80)
              SQL> exec :xq := '/root/item'
              
              PL/SQL procedure successfully completed.
              
              SQL> select x.*
                2  from tmp_xml t
                3     , xmltable(:xq passing t.object_value
                4         columns item varchar2(30) path '.'
                5       ) x
                6  ;
              
              Execution Plan
              ----------------------------------------------------------
              Plan hash value: 1822248632
              
              ------------------------------------------------------------------------------------------------------------
              | Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
              ------------------------------------------------------------------------------------------------------------
              |   0 | SELECT STATEMENT                   |                       |  8168 |    15M|    32   (0)| 00:00:01 |
              |   1 |  NESTED LOOPS                      |                       |  8168 |    15M|    32   (0)| 00:00:01 |
              |   2 |   TABLE ACCESS FULL                | TMP_XML               |     1 |  2002 |     3   (0)| 00:00:01 |
              |   3 |   COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |  8168 | 16336 |    29   (0)| 00:00:01 |
              ------------------------------------------------------------------------------------------------------------
              
              Note
              -----
                 - dynamic sampling used for this statement (level=2)
                 - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
              There are more than 50,000 xml files. One XML file is around 350 lines. We need to derive around 7-8 tags from every xml based on some conditions and perform calculations on them.
              That's the point of a test case, it doesn't have to be the full picture but something simple enough that you can post here, yet sufficient to show us what kind of process you're doing.

              Kindly suggest any ways to improve the process..
              Difficult to help someone when we don't know what he's doing.
              As you didn't answer the previous questions (storage method? why dynamic XQ? XQuery logic?), I'm afraid I can't suggest anything useful.

              - Are you doing joins between XML rowsets returned by ora:view?
              - What kind of calculations are we talking about here?

              Still stabbing in the dark, but in this situation, I would probably look towards extracting everything I need as relational data and use SQL to perform the calculations.
              • 4. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
                mdrake-Oracle
                Our Architect's response to this is

                Use Execute Immediate to execute the SELECT statement..
                • 5. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
                  Marco Gralike
                  mdrake wrote:
                  Our Architect's response to this is

                  Use Execute Immediate to execute the SELECT statement..
                  The "EXECUTE IMMEDIATE" alternative has the same (current ? implementation) disadvantage, as using a "dynamic XQuery string" in this case, its very difficult for the CBO to performance optimize this: too much unknowns regarding the predicates and methods to be used
                  • 6. Re: Xquery ,Clob and Ora:view being used..Kindly suggest
                    odie_63
                    Marco Gralike wrote:
                    The "EXECUTE IMMEDIATE" alternative has the same (current ? implementation) disadvantage, as using a "dynamic XQuery string" in this case
                    That's not true.
                    The statement will build an SQL cursor and it'll be treated by the CBO just like any other cursor.
                    If the XQuery expression is known at parse time then the CBO will know what to do :
                    SQL> declare
                      2  
                      3    v_xq_str   varchar2(80) := '/root/item';
                      4    v_sql_str  varchar2(2000) := q'{
                      5  SELECT /*FIND_ME*/ x.item
                      6  FROM tmp_xml t
                      7     , XMLTable(':xq_str' passing t.object_value
                      8        columns item varchar2(30) path '.'
                      9       ) x
                     10    }';
                     11  
                     12    v_result   varchar2(30);
                     13  
                     14  begin
                     15  
                     16    execute immediate replace(v_sql_str, ':xq_str', v_xq_str) into v_result;
                     17    dbms_output.put_line('result = '||v_result);
                     18  
                     19  end;
                     20  /
                     
                    result = ABC
                     
                    PL/SQL procedure successfully completed
                     
                    SQL> select sql_text, sql_id from v$sql where sql_text like '%FIND_ME%';
                     
                    SQL_TEXT                                                                         SQL_ID
                    -------------------------------------------------------------------------------- -------------
                     SELECT /*FIND_ME*/ x.item FROM tmp_xml t    , XMLTable('/root/item' passing t.o 4j3vcczhb2r04
                     select sql_text, sql_id from v$sql where sql_text like '%FIND_ME%'              b2ndymq7u2s7y
                     declare    v_xq_str   varchar2(80) := '/root/item';   v_sql_str  varchar2(2000) b0ju35kdk7bwv
                     
                    SQL> select * from table(dbms_xplan.display_cursor('4j3vcczhb2r04'));
                     
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    SQL_ID  4j3vcczhb2r04, child number 0
                    -------------------------------------
                     SELECT /*FIND_ME*/ x.item FROM tmp_xml t    , XMLTable('/root/item'
                    passing t.object_value       columns item varchar2(30) path '.'      ) x
                    Plan hash value: 3806182995
                    --------------------------------------------------------------------------------
                    | Id  | Operation                    | Name                           | Rows  |
                    --------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT             |                                |       |
                    |   1 |  NESTED LOOPS                |                                |       |
                    |   2 |   NESTED LOOPS               |                                |     1 |
                    |   3 |    TABLE ACCESS FULL         | TMP_XML                        |     1 |
                    |*  4 |    INDEX RANGE SCAN          | SYS28105_TMP_XML_IX_PIKEY_IX   |     1 |
                    |*  5 |   TABLE ACCESS BY INDEX ROWID| SYS28105_TMP_XML_IX_PATH_TABLE |     1 |
                    --------------------------------------------------------------------------------
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                     
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                       4 - access("T".ROWID="SYS_P0"."RID" AND "SYS_P0"."PATHID"=HEXTORAW('2DF1') )
                       5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P0"."LOCATOR")=1)
                    Note
                    -----
                       - dynamic sampling used for this statement (level=2)
                     
                    28 rows selected
                     
                    Now this approach has the disadvantages of dynamic SQL in general :
                    - no dependency tracking in stored programs
                    - no compile-time validation, hence error-prone
                    - SQL injection (in the worst case)
                    - harder maintenance
                    - could generate more parsing (if cursors aren't cached)

                    and that takes us back to the original question, why dynamic XQuery?