13 Replies Latest reply: Apr 12, 2012 3:37 AM by Michiel Weggen RSS

    Bad performance with many xmlqueries in select and big resultset

    Michiel Weggen
      I'm running into big performance problems with the following query (edited the query a bit to remove sensitive information):
      SELECT id "id",created "created",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/element2/name' passing xml returning content) as varchar2(182))"element2Name",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/element3/name' passing xml returning content) as varchar2(182))"element3Name",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/ror/gcor' passing xml returning content) as varchar2(5))"gcor",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:status/c:cStatus' passing xml returning content) as integer)"cStatus",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:status/c:lrrm' passing xml returning content) as integer)"lrrm",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:status/c:sent' passing xml returning content) as integer)"sent",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:status/c:success' passing xml returning content) as integer)"success",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:status/c:processStep' passing xml returning content) as integer)"processStep",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/header/status/aseod' passing xml returning content) as number(1))"aseod",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/submission/deferred' passing xml returning content) as number(1))"deferred",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/header/status/eventReportReceived' passing xml returning content) as number(1))"eventReports",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:status/c:isOpen' passing xml returning content) as number(1))"isOpen",
        xmlcast(xmlquery('declare default element namespace "http://www.example.com/myproject/schema/namespace2";declare namespace c="http://www.example.com/myproject/schema/common";/element1/c:element1Header/c:hasNotes' passing xml returning content) as number(1))"hasNotes" 
      FROM tablename,xmltable(xmlnamespaces(default 'http://www.example.com/myproject/schema/namespace2','http://www.example.com/myproject/schema/common' as "c"),'/element1' passing xml columns 
      created timestamp path 'c:element1Header/c:creationTime/text()',
      organization_id integer path 'c:element1Header/c:organization/c:organizationId/text()',
      is_sender number(1) path 'c:element1Header/c:isSender/text()')
      WHERE organization_id = 5 AND is_sender = 1 AND created >= sysdate-20 AND created <= sysdate+1;
      This query is fast as long as the results is small (<1000), but when the resultset grows bigger, the performance seems to decrease exponentially. The cause of this slowdown seems to be in the xmlqueries; commenting out the xmlqueries makes the query very fast again (in the order of a few seconds), even with a 15000+ resultset, while including the xmlqueries makes the query take many minutes.

      Workaround I tried: using a rownum < 1000 works fairly well, but only if I don't use order by (which is required). order by forces the resultset to be full built regardless of the rownum limit. (this was done with a subquery orderby and a rownum on the superquery)

      Other workaround i tried: Having the subquery only return the xml column, and doing the xmlqueries in the superquery. I couldn't get this to work, something about no longer having a key-preserved table.

      Background info about the database: Oracle 11.2.0.3.0, binary xmltype column, with a xmlindex unstructured component on all paths in this query, and a structured component with secondary indexes on the paths used in the WHERE (created, organization_id and is_sender). Database has about 140k records total.

      My question is, if anyone knows if this xmlquery bottleneck can be remedied somehow?

      Addition: graph i made indicating the performance at different resultset sizes. Horizontal axis is size of resultset, vertical axis is time spent in seconds: http://i.imgur.com/F2tyg.png
        • 1. Re: Bad performance with many xmlqueries in select and big resultset
          odie_63
          My question is, if anyone knows if this xmlquery bottleneck can be remedied somehow?
          There's a good chance we can do something about it.

          Could you give :

          - the explain plan of the current query?
          - DDL for the unstructured index?
          - DDL for the structured index?

          Ideally, a sample XML document would be great (with dummy values if necessary) as well as cardinalities for the projected columns in the filter predicate (i.e. how many distinct organization_id? etc.)

          Thanks.
          • 2. Re: Bad performance with many xmlqueries in select and big resultset
            Marco Gralike
            My query is probably not right, but the following FORMAT makes more sense to me than yours, avoiding all this passing of 15 times the "XML" document ("passing xml"). Is there no way to minimize all this "passing" ???
            SELECT id           as "id",
                   created      as "created",
                   element2Name as "element2Name",
                   element3Name as "element3Name",
                   gcor         as "gcor",
                   cStatus      as "cStatus",
                   lrrm         as "lrrm",
                   sent         as "sent",
                   success      as "success",
                   processStep  as "processStep",
                   aseod        as "aseod",
                   deferred     as "deferred",
                   eventReports as "eventReports",
                   isOpen       as "isOpen",
                   hasNotes     as "hasNotes" 
            FROM tablename
               , xmltable(xmlnamespaces(default 'http://www.example.com/myproject/schema/namespace2','http://www.example.com/myproject/schema/common' as "c"),'/element1' 
                          passing xml 
                          columns 
                           created            timestamp     path 'c:element1Header/c:creationTime/text()',
                           organization_id    integer       path 'c:element1Header/c:organization/c:organizationId/text()',
                           is_sender          number(1)     path 'c:element1Header/c:isSender/text()',
                           element2Name       varchar2(182) path 'element2/name',
                           element3Name       varchar2(182) path 'element3/name',
                           gcor               varchar2(5)   path 'ror/gcor',
                           cStatus            integer       path 'c:element1Header/c:status/c:cStatus',
                           lrrm               integer       path 'c:element1Header/c:status/c:lrrm',
                           sent               integer       path 'c:element1Header/c:status/c:sent',
                           success            integer       path 'c:element1Header/c:status/c:success',
                           processStep        integer       path 'c:element1Header/c:status/c:processStep',
                           aseod              number(1)     path 'header/status/aseod', 
                           deferred           number(1)     path 'submission/deferred',
                           eventReports       number(1)     path 'header/status/eventReportReceived',
                           isOpen             number(1)     path 'c:element1Header/c:status/c:isOpen',
                           hasNotes           number(1)     path 'c:element1Header/c:hasNotes'
                 )
            WHERE organization_id = 5 
              AND is_sender = 1 
              AND created >= sysdate-20 
              AND created <= sysdate+1;
            Edited by: Marco Gralike on Apr 10, 2012 6:55 PM
            • 3. Re: Bad performance with many xmlqueries in select and big resultset
              Marco Gralike
              In short:

              = Parsing XML is Expensive; Keep it Stupid Simple and do as much as possible in one run; As restrictive/precise as possible =
              • 4. Re: Bad performance with many xmlqueries in select and big resultset
                Michiel Weggen
                Just count(*), nothing else in the select:
                COUNT(*)               
                ---------------------- 
                15432                  
                
                Plan hash value: 1584286506
                 
                --------------------------------------------------------------------------------------------------------
                | Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                --------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT               |                       |     1 |    39 |    31   (0)| 00:00:01 |
                |   1 |  SORT AGGREGATE                |                       |     1 |    39 |            |          |
                |*  2 |   FILTER                       |                       |       |       |            |          |
                |   3 |    NESTED LOOPS                |                       |    15 |   585 |    31   (0)| 00:00:01 |
                |   4 |     TABLE ACCESS BY INDEX ROWID| TABLENAME_SC          |    15 |   405 |    16   (0)| 00:00:01 |
                |*  5 |      INDEX RANGE SCAN          | TABLENAME_SC_SUB_IX   |    15 |       |     3   (0)| 00:00:01 |
                |   6 |     TABLE ACCESS BY USER ROWID | TABLENAME             |     1 |    12 |     1   (0)| 00:00:01 |
                --------------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                   2 - filter(SYSDATE@!-500<=SYSDATE@!-50)
                   5 - access("SYS_SXI_0"."ORGANIZATION_ID"=6 AND "SYS_SXI_0"."IS_SENDER"=1 AND 
                              "SYS_SXI_0"."CREATION_TIME">=SYSDATE@!-500 AND "SYS_SXI_0"."CREATION_TIME"<=SYSDATE@!-50)
                Original query with ORDER BY on 2 structured component columns, 1 descending and 1 ascending (NOTE: While the explain plan says the time is 00:00:01, the query takes 763 seconds to complete)
                -----------------------------------------------------------------------------------------------------------------
                | Id  | Operation                      | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
                -----------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT               |                                |    15 | 23880 |    32   (4)| 00:00:01 |
                |   1 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |*  2 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |*  3 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |   4 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |*  5 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    16 |   656 |     5   (0)| 00:00:01 |
                |*  6 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    16 |       |     3   (0)| 00:00:01 |
                |   7 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |*  8 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    15 |   615 |     4   (0)| 00:00:01 |
                |*  9 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    15 |       |     3   (0)| 00:00:01 |
                |  10 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 11 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 12 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  13 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 14 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 15 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  16 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 17 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 18 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  19 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 20 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 21 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  22 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 23 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 24 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  25 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 26 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |     1 |    41 |     4   (0)| 00:00:01 |
                |* 27 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |     1 |       |     3   (0)| 00:00:01 |
                |  28 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 29 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |     8 |   328 |     4   (0)| 00:00:01 |
                |* 30 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |     8 |       |     3   (0)| 00:00:01 |
                |  31 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 32 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |     1 |    41 |     4   (0)| 00:00:01 |
                |* 33 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |     1 |       |     3   (0)| 00:00:01 |
                |  34 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 35 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 36 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  37 |  SORT ORDER BY                 |                                |    15 | 23880 |    32   (4)| 00:00:01 |
                |* 38 |   FILTER                       |                                |       |       |            |          |
                |  39 |    NESTED LOOPS                |                                |    15 | 23880 |    31   (0)| 00:00:01 |
                |  40 |     TABLE ACCESS BY INDEX ROWID| TABLENAME_SC                   |    15 |  1350 |    16   (0)| 00:00:01 |
                |* 41 |      INDEX RANGE SCAN          | TABLENAME_SC_SUB_IX            |    15 |       |     3   (0)| 00:00:01 |
                |  42 |     TABLE ACCESS BY USER ROWID | TABLENAME                      |     1 |  1502 |     1   (0)| 00:00:01 |
                -----------------------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1)
                   3 - access("SYS_P1"."RID"=:B1 AND "SYS_P1"."PATHID"=HEXTORAW('509D') )
                   5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
                   6 - access("SYS_P3"."RID"=:B1 AND "SYS_P3"."PATHID"=HEXTORAW('4FDE') )
                   8 - filter(SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1)
                   9 - access("SYS_P5"."RID"=:B1 AND "SYS_P5"."PATHID"=HEXTORAW('7129') )
                  11 - filter(SYS_XMLI_LOC_ISNODE("SYS_P7"."LOCATOR")=1)
                  12 - access("SYS_P7"."RID"=:B1 AND "SYS_P7"."PATHID"=HEXTORAW('73C0') )
                  14 - filter(SYS_XMLI_LOC_ISNODE("SYS_P9"."LOCATOR")=1)
                  15 - access("SYS_P9"."RID"=:B1 AND "SYS_P9"."PATHID"=HEXTORAW('3092') )
                  17 - filter(SYS_XMLI_LOC_ISNODE("SYS_P11"."LOCATOR")=1)
                  18 - access("SYS_P11"."RID"=:B1 AND "SYS_P11"."PATHID"=HEXTORAW('30AA') )
                  20 - filter(SYS_XMLI_LOC_ISNODE("SYS_P13"."LOCATOR")=1)
                  21 - access("SYS_P13"."RID"=:B1 AND "SYS_P13"."PATHID"=HEXTORAW('3415') )
                  23 - filter(SYS_XMLI_LOC_ISNODE("SYS_P15"."LOCATOR")=1)
                  24 - access("SYS_P15"."RID"=:B1 AND "SYS_P15"."PATHID"=HEXTORAW('4972') )
                  26 - filter(SYS_XMLI_LOC_ISNODE("SYS_P17"."LOCATOR")=1)
                  27 - access("SYS_P17"."RID"=:B1 AND "SYS_P17"."PATHID"=HEXTORAW('745F') )
                  29 - filter(SYS_XMLI_LOC_ISNODE("SYS_P19"."LOCATOR")=1)
                  30 - access("SYS_P19"."RID"=:B1 AND "SYS_P19"."PATHID"=HEXTORAW('6BA9') )
                  32 - filter(SYS_XMLI_LOC_ISNODE("SYS_P21"."LOCATOR")=1)
                  33 - access("SYS_P21"."RID"=:B1 AND "SYS_P21"."PATHID"=HEXTORAW('62DB') )
                  35 - filter(SYS_XMLI_LOC_ISNODE("SYS_P23"."LOCATOR")=1)
                  36 - access("SYS_P23"."RID"=:B1 AND "SYS_P23"."PATHID"=HEXTORAW('1FD9') )
                  38 - filter(SYSDATE@!-500<=SYSDATE@!-50)
                  41 - access("SYS_SXI_0"."ORGANIZATION_ID"=6 AND "SYS_SXI_0"."IS_SENDER"=1 AND 
                              "SYS_SXI_0"."CREATION_TIME">=SYSDATE@!-500 AND "SYS_SXI_0"."CREATION_TIME"<=SYSDATE@!-50)
                 
                Note
                -----
                   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
                The same query with ORDER BY only on CREATED DESC (takes 15 seconds to complete):
                -----------------------------------------------------------------------------------------------------------------
                | Id  | Operation                      | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
                -----------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT               |                                |    15 | 23880 |    31   (0)| 00:00:01 |
                |   1 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |*  2 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |*  3 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |   4 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |*  5 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    16 |   656 |     5   (0)| 00:00:01 |
                |*  6 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    16 |       |     3   (0)| 00:00:01 |
                |   7 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |*  8 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    15 |   615 |     4   (0)| 00:00:01 |
                |*  9 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    15 |       |     3   (0)| 00:00:01 |
                |  10 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 11 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 12 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  13 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 14 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 15 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  16 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 17 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 18 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  19 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 20 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 21 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  22 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 23 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 24 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |  25 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 26 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |     1 |    41 |     4   (0)| 00:00:01 |
                |* 27 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |     1 |       |     3   (0)| 00:00:01 |
                |  28 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 29 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |     8 |   328 |     4   (0)| 00:00:01 |
                |* 30 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |     8 |       |     3   (0)| 00:00:01 |
                |  31 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 32 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |     1 |    41 |     4   (0)| 00:00:01 |
                |* 33 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |     1 |       |     3   (0)| 00:00:01 |
                |  34 |  SORT GROUP BY                 |                                |     1 |    41 |            |          |
                |* 35 |   TABLE ACCESS BY INDEX ROWID  | SYS63339_TABL_XML_I_PATH_TABLE |    17 |   697 |     5   (0)| 00:00:01 |
                |* 36 |    INDEX RANGE SCAN            | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |* 37 |  FILTER                        |                                |       |       |            |          |
                |  38 |   NESTED LOOPS                 |                                |    15 | 23880 |    31   (0)| 00:00:01 |
                |  39 |    TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |    15 |  1350 |    16   (0)| 00:00:01 |
                |* 40 |     INDEX RANGE SCAN DESCENDING| TABLENAME_SC_SUB_IX            |    15 |       |     3   (0)| 00:00:01 |
                |  41 |    TABLE ACCESS BY USER ROWID  | TABLENAME                      |     1 |  1502 |     1   (0)| 00:00:01 |
                -----------------------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                   2 - filter(SYS_XMLI_LOC_ISNODE("SYS_P1"."LOCATOR")=1)
                   3 - access("SYS_P1"."RID"=:B1 AND "SYS_P1"."PATHID"=HEXTORAW('509D') )
                   5 - filter(SYS_XMLI_LOC_ISNODE("SYS_P3"."LOCATOR")=1)
                   6 - access("SYS_P3"."RID"=:B1 AND "SYS_P3"."PATHID"=HEXTORAW('4FDE') )
                   8 - filter(SYS_XMLI_LOC_ISNODE("SYS_P5"."LOCATOR")=1)
                   9 - access("SYS_P5"."RID"=:B1 AND "SYS_P5"."PATHID"=HEXTORAW('7129') )
                  11 - filter(SYS_XMLI_LOC_ISNODE("SYS_P7"."LOCATOR")=1)
                  12 - access("SYS_P7"."RID"=:B1 AND "SYS_P7"."PATHID"=HEXTORAW('73C0') )
                  14 - filter(SYS_XMLI_LOC_ISNODE("SYS_P9"."LOCATOR")=1)
                  15 - access("SYS_P9"."RID"=:B1 AND "SYS_P9"."PATHID"=HEXTORAW('3092') )
                  17 - filter(SYS_XMLI_LOC_ISNODE("SYS_P11"."LOCATOR")=1)
                  18 - access("SYS_P11"."RID"=:B1 AND "SYS_P11"."PATHID"=HEXTORAW('30AA') )
                  20 - filter(SYS_XMLI_LOC_ISNODE("SYS_P13"."LOCATOR")=1)
                  21 - access("SYS_P13"."RID"=:B1 AND "SYS_P13"."PATHID"=HEXTORAW('3415') )
                  23 - filter(SYS_XMLI_LOC_ISNODE("SYS_P15"."LOCATOR")=1)
                  24 - access("SYS_P15"."RID"=:B1 AND "SYS_P15"."PATHID"=HEXTORAW('4972') )
                  26 - filter(SYS_XMLI_LOC_ISNODE("SYS_P17"."LOCATOR")=1)
                  27 - access("SYS_P17"."RID"=:B1 AND "SYS_P17"."PATHID"=HEXTORAW('745F') )
                  29 - filter(SYS_XMLI_LOC_ISNODE("SYS_P19"."LOCATOR")=1)
                  30 - access("SYS_P19"."RID"=:B1 AND "SYS_P19"."PATHID"=HEXTORAW('6BA9') )
                  32 - filter(SYS_XMLI_LOC_ISNODE("SYS_P21"."LOCATOR")=1)
                  33 - access("SYS_P21"."RID"=:B1 AND "SYS_P21"."PATHID"=HEXTORAW('62DB') )
                  35 - filter(SYS_XMLI_LOC_ISNODE("SYS_P23"."LOCATOR")=1)
                  36 - access("SYS_P23"."RID"=:B1 AND "SYS_P23"."PATHID"=HEXTORAW('1FD9') )
                  37 - filter(SYSDATE@!-500<=SYSDATE@!-50)
                  40 - access("SYS_SXI_0"."ORGANIZATION_ID"=6 AND "SYS_SXI_0"."IS_SENDER"=1 AND 
                              "SYS_SXI_0"."CREATION_TIME">=SYSDATE@!-500 AND "SYS_SXI_0"."CREATION_TIME"<=SYSDATE@!-50)
                 
                Note
                -----
                   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)
                Removing all XmlQueries from the SELECT and adding 1 of them to the XmlTable in the FROM (full table scan, takes 25 seconds to complete):
                ---------------------------------------------------------------------------------------------------------------
                | Id  | Operation                    | Name                           | Rows  | Bytes | Cost (%CPU)| Time     |
                ---------------------------------------------------------------------------------------------------------------
                |   0 | SELECT STATEMENT             |                                |  1522 |  2232K| 15515   (3)| 00:03:07 |
                |   1 |  TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |     1 |    21 |     2   (0)| 00:00:01 |
                |*  2 |   INDEX RANGE SCAN           | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |   3 |  TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |     1 |    32 |     2   (0)| 00:00:01 |
                |*  4 |   INDEX RANGE SCAN           | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |   5 |  TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |     1 |    23 |     2   (0)| 00:00:01 |
                |*  6 |   INDEX RANGE SCAN           | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |   7 |  TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |     1 |    27 |     2   (0)| 00:00:01 |
                |*  8 |   INDEX RANGE SCAN           | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |   9 |  TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |     1 |    18 |     2   (0)| 00:00:01 |
                |* 10 |   INDEX RANGE SCAN           | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |  11 |  TABLE ACCESS BY INDEX ROWID | TABLENAME_SC                   |     1 |    13 |     2   (0)| 00:00:01 |
                |* 12 |   INDEX RANGE SCAN           | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |* 13 |  TABLE ACCESS BY INDEX ROWID | SYS63339_TABL_XML_I_PATH_TABLE |     1 |    37 |     4   (0)| 00:00:01 |
                |* 14 |   INDEX RANGE SCAN           | SYS63339_TABL_XML_I_PIKEY_IX   |    17 |       |     3   (0)| 00:00:01 |
                |* 15 |  FILTER                      |                                |       |       |            |          |
                |* 16 |   TABLE ACCESS FULL          | TABLENAME                      |  1522 |  2232K|  9423   (4)| 00:01:54 |
                |  17 |   TABLE ACCESS BY INDEX ROWID| TABLENAME_SC                   |     1 |    13 |     2   (0)| 00:00:01 |
                |* 18 |    INDEX RANGE SCAN          | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |  19 |   TABLE ACCESS BY INDEX ROWID| TABLENAME_SC                   |     1 |    13 |     2   (0)| 00:00:01 |
                |* 20 |    INDEX RANGE SCAN          | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |  21 |   TABLE ACCESS BY INDEX ROWID| TABLENAME_SC                   |     1 |    21 |     2   (0)| 00:00:01 |
                |* 22 |    INDEX RANGE SCAN          | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                |  23 |   TABLE ACCESS BY INDEX ROWID| TABLENAME_SC                   |     1 |    21 |     2   (0)| 00:00:01 |
                |* 24 |    INDEX RANGE SCAN          | SYS63339_63348_RID_IDX         |     1 |       |     1   (0)| 00:00:01 |
                ---------------------------------------------------------------------------------------------------------------
                 
                Predicate Information (identified by operation id):
                ---------------------------------------------------
                 
                   2 - access("SYS_SXI_7"."RID"=:B1)
                   4 - access("SYS_SXI_6"."RID"=:B1)
                   6 - access("SYS_SXI_5"."RID"=:B1)
                   8 - access("SYS_SXI_4"."RID"=:B1)
                  10 - access("SYS_SXI_3"."RID"=:B1)
                  12 - access("SYS_SXI_2"."RID"=:B1)
                  13 - filter(SYS_XMLI_LOC_ISTEXT("SYS_P0"."LOCATOR","SYS_P0"."PATHID")=1)
                  14 - access("SYS_P0"."RID"=:B1 AND "SYS_P0"."PATHID"=HEXTORAW('509D') )
                  15 - filter(CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT 
                              SYS_XQ_PKSQL2XML("SYS_SXI_11"."ORGANIZATION_ID",2,4,2) FROM "USERN"."TABLENAME_SC" "SYS_SXI_11" WHERE 
                              "SYS_SXI_11"."RID"=:B1),0,0,54525952,0),50,1,2)) AS integer )=6 AND 
                              CAST(TO_NUMBER(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT SYS_XQ_PKSQL2XML("SYS_SXI_10"."IS_SENDER",2,4,2) 
                              FROM "USERN"."TABLENAME_SC" "SYS_SXI_10" WHERE "SYS_SXI_10"."RID"=:B2),0,0,54525952,0),50,1,2)) AS 
                              number(1) )=1 AND CAST(TO_TIMESTAMP(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT 
                              SYS_XQ_PKSQL2XML("SYS_SXI_9"."CREATION_TIME",180,8,2) FROM "USERN"."TABLENAME_SC" "SYS_SXI_9" WHERE 
                              "SYS_SXI_9"."RID"=:B3),0,0,20971520,0),50,1,2),'SYYYY-MM-DD"T"HH24:MI:SSXFF') AS timestamp 
                              )>=SYSDATE@!-300 AND CAST(TO_TIMESTAMP(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL( (SELECT 
                              SYS_XQ_PKSQL2XML("SYS_SXI_8"."CREATION_TIME",180,8,2) FROM "USERN"."TABLENAME_SC" "SYS_SXI_8" WHERE 
                              "SYS_SXI_8"."RID"=:B4),0,0,20971520,0),50,1,2),'SYYYY-MM-DD"T"HH24:MI:SSXFF') AS timestamp 
                              )<=SYSDATE@!-200)
                  16 - filter(EXISTSNODE(SYS_MAKEXML(0,"SYS_ALIAS_11"."SYS_NC00003$"),'/oraxq_defpfx:element1',' 
                              xmlns:oraxq_defpfx="http://www.example.com/myproject/schema/namespace2"')=1)
                  18 - access("SYS_SXI_11"."RID"=:B1)
                  20 - access("SYS_SXI_10"."RID"=:B1)
                  22 - access("SYS_SXI_9"."RID"=:B1)
                  24 - access("SYS_SXI_8"."RID"=:B1)
                Index creation script:
                CREATE INDEX tabl_xml_ix
                  ON tablename(xml)
                  INDEXTYPE IS XDB.XMLIndex
                  PARAMETERS('
                    PATHS (INCLUDE (/element1/common:header/common:kind
                /element1/common:element1Header/common:referenceNumber
                /element1/common:element1Header/common:reference
                /element1/common:element1Header/common:organization/common:organizationId
                /element1/common:element1Header/common:status/common:cStatus
                /element1/common:element1Header/common:status/common:isOpen
                /element1/common:element1Header/common:status/common:processStep
                /element1/common:element1Header/common:status/common:lrrm
                /element1/common:element1Header/common:status/common:sent
                /element1/common:element1Header/common:status/common:success
                /element1/common:element1Header/common:creationTime)
                                   NAMESPACE MAPPING (xmlns="http://www.example.com/myproject/schema/namespace2"
                                        xmlns:common="http://www.example.com/myproject/schema/common"))');
                
                ALTER INDEX tabl_xml_ix
                PARAMETERS ('PATHS (INCLUDE ADD (/element1/e/referenceCode
                /element1/e/header/sNumber
                /element1/element2/name
                /element1/element3/name
                /element1/element4/id
                /element1/submission/deferred
                /element1/common:element1Header/common:organization/common:pNumber
                /element1/e/iNumber
                /element1/header/status/aseod
                /element1/common:element1Header/common:isSender
                /element1/ror/gcor
                /element1/header/status/eventReportReceived
                /element1/pod/eNumber)
                                   NAMESPACE MAPPING (xmlns="http://www.example.com/myproject/schema/namespace2"
                                        xmlns:common="http://www.example.com/myproject/schema/common"))');
                
                BEGIN
                  DBMS_XMLINDEX.registerParameter(
                    'tablename_add_sc','ADD_GROUP GROUP tablename_group
                XMLTABLE tablename_sc
                XMLNAMESPACES(
                DEFAULT ''http://www.example.com/myproject/schema/namespace2'',
                ''http://www.example.com/myproject/schema/common'' AS "common"
                ),
                ''/element1''
                COLUMNS
                is_sender NUMBER(1) path ''common:element1Header/common:isSender/text()'',
                creation_time TIMESTAMP PATH ''common:element1Header/common:creationTime/text()'',
                rn VARCHAR2(22 CHAR) PATH ''common:element1Header/common:referenceNumber/text()'',
                rc VARCHAR2(21) PATH ''e/referenceCode/text()'',
                reference VARCHAR2(35 CHAR) PATH ''common:element1Header/common:reference/text()'',
                i_nr VARCHAR2(35 CHAR) PATH ''e/iNumber/text()'',
                p_nr VARCHAR2(32) PATH ''common:element1Header/common:organization/common:pNumber/text()'',
                pod_e_nr VARCHAR2(13) PATH ''pod/eNumber/text()'',
                d_id VARCHAR2(16) PATH ''element4/id/text()'',
                organization_id INTEGER PATH ''common:element1Header/common:organization/common:organizationId/text()'',
                sequence_number INTEGER PATH ''e/eHeader/sequenceNumber/text()''
                ');
                END;
                
                ALTER INDEX tabl_xml_ix PARAMETERS('PARAM tablename_add_sc');
                
                create index tablename_sc_rn_ix on tablename_sc(rn);
                create index tablename_sc_time_ix on tablename_sc(creation_time);
                create index tablename_sc_sub_ix on tablename_sc (organization_id, is_sender, creation_time);
                Edited by: Michiel Weggen on Apr 11, 2012 2:02 AM Reason: Forgot secondary indexes.

                Edited by: Michiel Weggen on Apr 11, 2012 5:51 AM: changed path of d_id to element4/id in the structured component
                • 5. Re: Bad performance with many xmlqueries in select and big resultset
                  odie_63
                  Thanks.

                  Any sample document you can show us too? It'll help for testing some alternatives.

                  I'll second Marco's comment here, I'd first try again without any index and a single XMLTable, just to establish a baseline.
                  Then proceed with only a structured XMLIndex, with relevant secondary indexes to deal with filter predicates.
                  • 6. Re: Bad performance with many xmlqueries in select and big resultset
                    Michiel Weggen
                    <?xml version = '1.0' encoding = 'UTF-8' standalone = 'yes'?><ns2:element1 xsi:schemaLocation="http://www.example.com/myproject/schema/namespace2" xmlns:common="http://www.example.com/myproject/schema/common" xmlns:ns2="http://www.example.com/myproject/schema/namespace2" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
                       <common:element1Header>
                          <common:identifier>30116</common:identifier>
                          <common:kind>TEST.NL</common:kind>
                          <common:referenceNumber>11/091</common:referenceNumber>
                          <common:creationTime>2011-03-31T08:05:46</common:creationTime>
                          <common:lastValidationTime>2011-03-31T14:05:56</common:lastValidationTime>
                          <common:organization>
                             <common:organizationId>26</common:organizationId>
                             <common:pNumber>XX06600600450</common:pNumber>
                          </common:organization>
                          <common:isSender>0</common:isSender>
                          <common:status>
                             <common:cStatus>2</common:cStatus>
                             <common:isOpen>0</common:isOpen>
                             <common:processStep>0</common:processStep>
                             <common:sent>0</common:sent>
                             <common:success>0</common:success>
                             <common:lrrm>0</common:lrrm>
                          </common:status>
                       </common:element1Header>
                       <ns2:element2>
                          <ns2:language>nl</ns2:language>
                          <ns2:eNumber>BE1A000005099</ns2:eNumber>
                          <ns2:name>D C B MX</ns2:name>
                          <ns2:address>
                             <common:streetName>STEENBOK HAAI</common:streetName>
                             <common:streetNumber>36C</common:streetNumber>
                             <common:postcode>3142</common:postcode>
                             <common:city>TESTCITY</common:city>
                          </ns2:address>
                       </ns2:element2>
                       <ns2:element3>
                          <ns2:language>nl</ns2:language>
                          <ns2:tId>YY06600600450</ns2:tId>
                          <ns2:name>This is a test name</ns2:name>
                          <ns2:address>
                             <common:streetName>SOME STREET</common:streetName>
                             <common:streetNumber>11</common:streetNumber>
                             <common:postcode>3064PQ</common:postcode>
                             <common:city>BUGTOWN</common:city>
                          </ns2:address>
                       </ns2:element3>
                       <ns2:pod>
                          <ns2:language>nl</ns2:language>
                          <ns2:eNumber>AA1A000005000</ns2:eNumber>
                          <ns2:traderName>D C B NV</ns2:traderName>
                          <ns2:address>
                             <common:streetName>Steenbok Haai</common:streetName>
                             <common:streetNumber>36c</common:streetNumber>
                             <common:postcode>3142</common:postcode>
                             <common:city>TESTCITY</common:city>
                          </ns2:address>
                       </ns2:pod>
                       <ns2:element4>
                          <ns2:language>nl</ns2:language>
                          <ns2:id>TTW0000104960</ns2:id>
                          <ns2:name>This is another test name</ns2:name>
                          <ns2:address>
                             <common:streetName>G WDV S C NR3582,3583</common:streetName>
                             <common:streetNumber>53</common:streetNumber>
                             <common:postcode>1635RC</common:postcode>
                             <common:city>TESTTOWN</common:city>
                          </ns2:address>
                       </ns2:element4>
                       <ns2:e>
                          <ns2:referenceCode>10GRGLXEGAP7002GC5BD1</ns2:referenceCode>
                          <ns2:iNumber>49566311</ns2:iNumber>
                          <ns2:iDate>2011-03-31</ns2:iDate>
                          <ns2:originTypeCode>1</ns2:originTypeCode>
                          <ns2:dateOfDispatch>2011-03-31</ns2:dateOfDispatch>
                          <ns2:timeOfDispatch>09:30:00</ns2:timeOfDispatch>
                          <ns2:header>
                             <ns2:sequenceNumber>1</ns2:sequenceNumber>
                             <ns2:dTypeCode>1</ns2:dTypeCode>
                             <ns2:journeyTime>D05</ns2:journeyTime>
                             <ns2:tArrangement>3</ns2:tArrangement>
                          </ns2:header>
                       </ns2:e>
                       <ns2:ror>
                          <ns2:doa>2011-03-31</ns2:doa>
                          <ns2:gcor>1</ns2:gcor>
                          <ns2:destOff>XX000563</ns2:destOff>
                       </ns2:ror>
                    </ns2:element1>
                    • 7. Re: Bad performance with many xmlqueries in select and big resultset
                      odie_63
                      Here's my test case :
                      CREATE TABLE tmp_xml OF XMLTYPE;
                      Then I inserted multiples instances based on the sample :

                      - 15000 for organization_id = 5
                      - 20000 for organization_id = 6
                      - 10000 for organization_id = 26

                      with various creationTime spread around sysdate.

                      Structured XMLIndex :
                      CREATE INDEX tmp_xml_sxi ON tmp_xml (object_value)
                      INDEXTYPE IS XDB.XMLIndex
                      PARAMETERS(q'#xmltable tmp_xml_xt
                      xmlnamespaces(default 'http://www.example.com/myproject/schema/namespace2',
                      'http://www.example.com/myproject/schema/common' as "c"), 
                      '/element1'
                      columns 
                      creation_time timestamp path 'c:element1Header/c:creationTime',
                      organization_id integer path 'c:element1Header/c:organization/c:organizationId',
                      is_sender number(1) path 'c:element1Header/c:isSender',
                      rn varchar2(22) path 'c:element1Header/c:referenceNumber',
                      element2Name varchar2(182) path 'element2/name',
                      element3Name varchar2(182) path 'element3/name',
                      gcor varchar2(5) path 'ror/gcor',
                      cStatus integer path 'c:element1Header/c:status/c:cStatus',
                      lrrm integer path 'c:element1Header/c:status/c:lrrm',
                      sent integer path 'c:element1Header/c:status/c:sent',
                      success integer path 'c:element1Header/c:status/c:success',
                      processStep integer path 'c:element1Header/c:status/c:processStep',
                      isOpen number(1) path 'c:element1Header/c:status/c:isOpen',
                      hasNotes number(1) path 'c:element1Header/c:hasNotes'
                      #');
                      Secondary index :
                      CREATE INDEX tmp_xml_xt_ix1 ON tmp_xml_xt (organization_id, creation_time);
                      Stats :
                      CALL dbms_stats.gather_table_stats(user, 'TMP_XML');
                      Query :
                      SQL> set autotrace traceonly
                      SQL> set lines 120
                      SQL> set timing on
                      SQL> SELECT creation_time,
                        2         organization_id,
                        3         is_sender,
                        4         element2Name,
                        5         element3Name,
                        6         gcor,
                        7         cStatus,
                        8         lrrm,
                        9         sent,
                       10         success,
                       11         processStep,
                       12         isOpen
                       13  FROM tmp_xml
                       14     , XMLTable(
                       15         XMLNamespaces(
                       16           default 'http://www.example.com/myproject/schema/namespace2'
                       17         ,'http://www.example.com/myproject/schema/common' as "c"
                       18         )
                       19       , '/element1'
                       20         passing object_value
                       21         columns
                       22           creation_time      timestamp     path 'c:element1Header/c:creationTime',
                       23           organization_id    integer       path 'c:element1Header/c:organization/c:organizationId',
                       24           is_sender          number(1)     path 'c:element1Header/c:isSender',
                       25           rn                 varchar2(22)  path 'c:element1Header/c:referenceNumber',
                       26           element2Name       varchar2(182) path 'element2/name',
                       27           element3Name       varchar2(182) path 'element3/name',
                       28           gcor               varchar2(5)   path 'ror/gcor',
                       29           cStatus            integer       path 'c:element1Header/c:status/c:cStatus',
                       30           lrrm               integer       path 'c:element1Header/c:status/c:lrrm',
                       31           sent               integer       path 'c:element1Header/c:status/c:sent',
                       32           success            integer       path 'c:element1Header/c:status/c:success',
                       33           processStep        integer       path 'c:element1Header/c:status/c:processStep',
                       34           isOpen             number(1)     path 'c:element1Header/c:status/c:isOpen',
                       35           hasNotes           number(1)     path 'c:element1Header/c:hasNotes'
                       36       )
                       37  WHERE creation_time BETWEEN sysdate-20 AND sysdate-1
                       38  AND organization_id = 6
                       39  AND is_sender = 0
                       40  ORDER BY creation_time desc
                       41  ;
                      
                      456 rows selected.
                      
                      Elapsed: 00:00:00.01
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 1254495932
                      
                      -------------------------------------------------------------------------------------------------
                      | Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
                      -------------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT               |                |   152 | 14440 |    25   (0)| 00:00:01 |
                      |*  1 |  FILTER                        |                |       |       |            |          |
                      |   2 |   NESTED LOOPS                 |                |   152 | 14440 |    25   (0)| 00:00:01 |
                      |*  3 |    TABLE ACCESS BY INDEX ROWID | TMP_XML_XT     |   152 | 11856 |    25   (0)| 00:00:01 |
                      |*  4 |     INDEX RANGE SCAN DESCENDING| TMP_XML_XT_IX1 |   350 |       |     3   (0)| 00:00:01 |
                      |*  5 |    INDEX UNIQUE SCAN           | SYS_C006992    |     1 |    17 |     0   (0)| 00:00:01 |
                      -------------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         1 - filter(SYSDATE@!-20<=SYSDATE@!-1)
                         3 - filter("SYS_SXI_0"."IS_SENDER"=0)
                         4 - access("SYS_SXI_0"."ORGANIZATION_ID"=6 AND
                                    "SYS_SXI_0"."CREATION_TIME">=SYSDATE@!-20 AND "SYS_SXI_0"."CREATION_TIME"<=SYSDATE@!-1)
                         5 - access("TMP_XML"."SYS_NC_OID$"="SYS_SXI_0"."OID")
                      
                      
                      Statistics
                      ----------------------------------------------------------
                                0  recursive calls
                                0  db block gets
                              582  consistent gets
                                0  physical reads
                                0  redo size
                            11424  bytes sent via SQL*Net to client
                              749  bytes received via SQL*Net from client
                               32  SQL*Net roundtrips to/from client
                                0  sorts (memory)
                                0  sorts (disk)
                              456  rows processed
                      SQL> SELECT creation_time,
                        2         organization_id,
                        3         is_sender,
                        4         element2Name,
                        5         element3Name,
                        6         gcor,
                        7         cStatus,
                        8         lrrm,
                        9         sent,
                       10         success,
                       11         processStep,
                       12         isOpen
                       13  FROM tmp_xml
                       14     , XMLTable(
                       15         XMLNamespaces(
                       16           default 'http://www.example.com/myproject/schema/namespace2'
                       17         ,'http://www.example.com/myproject/schema/common' as "c"
                       18         )
                       19       , '/element1'
                       20         passing object_value
                       21         columns
                       22           creation_time      timestamp     path 'c:element1Header/c:creationTime',
                       23           organization_id    integer       path 'c:element1Header/c:organization/c:organizationId',
                       24           is_sender          number(1)     path 'c:element1Header/c:isSender',
                       25           rn                 varchar2(22)  path 'c:element1Header/c:referenceNumber',
                       26           element2Name       varchar2(182) path 'element2/name',
                       27           element3Name       varchar2(182) path 'element3/name',
                       28           gcor               varchar2(5)   path 'ror/gcor',
                       29           cStatus            integer       path 'c:element1Header/c:status/c:cStatus',
                       30           lrrm               integer       path 'c:element1Header/c:status/c:lrrm',
                       31           sent               integer       path 'c:element1Header/c:status/c:sent',
                       32           success            integer       path 'c:element1Header/c:status/c:success',
                       33           processStep        integer       path 'c:element1Header/c:status/c:processStep',
                       34           isOpen             number(1)     path 'c:element1Header/c:status/c:isOpen',
                       35           hasNotes           number(1)     path 'c:element1Header/c:hasNotes'
                       36       )
                       37  WHERE creation_time BETWEEN sysdate-1000 AND sysdate-50
                       38  ORDER BY organization_id, creation_time desc
                       39  ;
                      
                      11400 rows selected.
                      
                      Elapsed: 00:00:00.14
                      
                      Execution Plan
                      ----------------------------------------------------------
                      Plan hash value: 3695884163
                      
                      --------------------------------------------------------------------------------------------
                      | Id  | Operation            | Name        | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
                      --------------------------------------------------------------------------------------------
                      |   0 | SELECT STATEMENT     |             |  7458 |   691K|       |   338   (2)| 00:00:05 |
                      |   1 |  SORT ORDER BY       |             |  7458 |   691K|   776K|   338   (2)| 00:00:05 |
                      |*  2 |   FILTER             |             |       |       |       |            |          |
                      |   3 |    NESTED LOOPS      |             |  7458 |   691K|       |   173   (2)| 00:00:03 |
                      |*  4 |     TABLE ACCESS FULL| TMP_XML_XT  |  7458 |   568K|       |   172   (2)| 00:00:03 |
                      |*  5 |     INDEX UNIQUE SCAN| SYS_C006992 |     1 |    17 |       |     0   (0)| 00:00:01 |
                      --------------------------------------------------------------------------------------------
                      
                      Predicate Information (identified by operation id):
                      ---------------------------------------------------
                      
                         2 - filter(SYSDATE@!-1000<=SYSDATE@!-50)
                         4 - filter("SYS_SXI_0"."CREATION_TIME"<=SYSDATE@!-50 AND
                                    "SYS_SXI_0"."CREATION_TIME">=SYSDATE@!-1000)
                         5 - access("TMP_XML"."SYS_NC_OID$"="SYS_SXI_0"."OID")
                      Edited by: odie_63 on 11 avr. 2012 16:20
                      • 8. Re: Bad performance with many xmlqueries in select and big resultset
                        Marco Gralike
                        Original query with ORDER BY on 2 structured component columns, 1 descending and 1 ascending (NOTE: While the explain plan says the time is 00:00:01, the query takes 763 seconds to complete)
                        Do NOT always trust explain plan. Its not always right. DBMS_XPLAN is way better to achieve the same and more.
                        If you don't believe me, read the whitepaper released by Maria Colgan of the Optimizer group. It can be downloaded here: https://blogs.oracle.com/optimizer/entry/explain_the_explain_plan_white

                        Quoting the whitepaper:
                        Under certain conditions the plan shown when using EXPLAIN PLAN can be different from the plan
                        shown using V$SQL_PLAN. For example, when the SQL statement contains bind variables the plan
                        shown from using EXPLAIN PLAN ignores the bind variable values while the plan shown in
                        V$SQL_PLAN takes the bind variable values into account in the plan generation process.
                        Edited by: Marco Gralike on Apr 11, 2012 10:56 PM
                        • 9. Re: Bad performance with many xmlqueries in select and big resultset
                          Marco Gralike
                          CREATE TABLE tmp_xml OF XMLTYPE;
                          Oracle 11.2.0.2.0 ? - Aka Binary XML or CLOB XMLType ?

                          ;-)
                          • 11. Re: Bad performance with many xmlqueries in select and big resultset
                            Michiel Weggen
                            When trying to move all XmlQueries to the XmlTable I seem to have run into another problem:
                            create table structured_index_test (
                              id number(19) not null,
                              xml xmltype,
                              primary key (id)
                              ) xmltype column "XML" store as securefile binary XML;
                            
                            table STRUCTURED_INDEX_TEST created.
                            
                            insert into structured_index_test (id,xml) values (0,'<xml><a>a</a><b>b</b><c>c</c><d>d</d><e>e</e><f>f</f><g>g</g><h>h</h><i>i</i><j>j</j><k>k</k><l>l</l><m>m</m><n>n</n><o>o</o><p>p</p><q>q</q><r>r</r><s>s</s><t>t</t><u>u</u><v>v</v><w>w</w><x>x</x><y>y</y><z>z</z></xml>');
                            insert into structured_index_test (id,xml) values (1,'<xml><a>a</a><b>b</b><c>c</c><d>d</d><e>e</e><f>f</f><g>g</g><h>h</h><i>i</i><j>j</j><k>k</k><l>l</l><m>m</m><n>n</n><o>o</o><p>p</p><q>q</q><r>r</r><s>s</s><t>t</t><u>u</u><v>v</v><w>w</w><x>x</x><y>y</y><z>z</z></xml>');
                            insert into structured_index_test (id,xml) values (2,'<xml><a>a</a><b>b</b><c>c</c><d>d</d><e>e</e><f>f</f><g>g</g><h>h</h><i>i</i><j>j</j><k>k</k><l>l</l><m>m</m><n>n</n><o>o</o><p>p</p><q>q</q><r>r</r><s>s</s><t>t</t><u>u</u><v>v</v><w>w</w><x>x</x><y>y</y><z>z</z></xml>');
                            insert into structured_index_test (id,xml) values (3,'<xml><a>a</a><b>b</b><c>c</c><d>d</d><e>e</e><f>f</f><g>g</g><h>h</h><i>i</i><j>j</j><k>k</k><l>l</l><m>m</m><n>n</n><o>o</o><p>p</p><q>q</q><r>r</r><s>s</s><t>t</t><u>u</u><v>v</v><w>w</w><x>x</x><y>y</y><z>z</z></xml>');
                            insert into structured_index_test (id,xml) values (4,'<xml><a>a</a><b>b</b><c>c</c><d>d</d><e>e</e><f>f</f><g>g</g><h>h</h><i>i</i><j>j</j><k>k</k><l>l</l><m>m</m><n>n</n><o>o</o><p>p</p><q>q</q><r>r</r><s>s</s><t>t</t><u>u</u><v>v</v><w>w</w><x>x</x><y>y</y><z>z</z></xml>');
                            
                            1 rows inserted.
                            1 rows inserted.
                            1 rows inserted.
                            1 rows inserted.
                            1 rows inserted.
                            
                            select id,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
                            from structured_index_test,
                            xmltable('/xml' passing xml columns 
                            a varchar2(1) path 'a/text()',
                            b varchar2(1) path 'b/text()',
                            c varchar2(1) path 'c/text()',
                            d varchar2(1) path 'd/text()',
                            e varchar2(1) path 'e/text()',
                            f varchar2(1) path 'f/text()',
                            g varchar2(1) path 'g/text()',
                            h varchar2(1) path 'h/text()',
                            i varchar2(1) path 'i/text()',
                            j varchar2(1) path 'j/text()',
                            k varchar2(1) path 'k/text()',
                            l varchar2(1) path 'l/text()',
                            m varchar2(1) path 'm/text()',
                            n varchar2(1) path 'n/text()',
                            o varchar2(1) path 'o/text()',
                            p varchar2(1) path 'p/text()',
                            q varchar2(1) path 'q/text()',
                            r varchar2(1) path 'r/text()',
                            s varchar2(1) path 's/text()',
                            t varchar2(1) path 't/text()',
                            u varchar2(1) path 'u/text()',
                            v varchar2(1) path 'v/text()',
                            w varchar2(1) path 'w/text()',
                            x varchar2(1) path 'x/text()',
                            y varchar2(1) path 'y/text()',
                            z varchar2(1) path 'z/text()');
                            
                            ID                     A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 
                            ---------------------- - - - - - - - - - - - - - - - - - - - - - - - - - - 
                            0                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            1                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            2                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            3                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            4                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            
                            Plan hash value: 3381419358
                             
                            --------------------------------------------------------------------------------------------
                            | Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                            --------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT   |                       | 40840 |    80M|   141   (1)| 00:00:02 |
                            |   1 |  NESTED LOOPS      |                       | 40840 |    80M|   141   (1)| 00:00:02 |
                            |   2 |   TABLE ACCESS FULL| STRUCTURED_INDEX_TEST |     5 | 10075 |     3   (0)| 00:00:01 |
                            |   3 |   XPATH EVALUATION |                       |       |       |            |          |
                            --------------------------------------------------------------------------------------------
                            
                            select id,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o
                            from structured_index_test,
                            xmltable('/xml' passing xml columns 
                            a varchar2(1) path 'a/text()',
                            b varchar2(1) path 'b/text()',
                            c varchar2(1) path 'c/text()',
                            d varchar2(1) path 'd/text()',
                            e varchar2(1) path 'e/text()',
                            f varchar2(1) path 'f/text()',
                            g varchar2(1) path 'g/text()',
                            h varchar2(1) path 'h/text()',
                            i varchar2(1) path 'i/text()',
                            j varchar2(1) path 'j/text()',
                            k varchar2(1) path 'k/text()',
                            l varchar2(1) path 'l/text()',
                            m varchar2(1) path 'm/text()',
                            n varchar2(1) path 'n/text()',
                            o varchar2(1) path 'o/text()');
                            
                            ID                     A B C D E F G H I J K L M N O 
                            ---------------------- - - - - - - - - - - - - - - - 
                            0                      a b c d e f g h i j k l m n o 
                            1                      a b c d e f g h i j k l m n o 
                            2                      a b c d e f g h i j k l m n o 
                            3                      a b c d e f g h i j k l m n o 
                            4                      a b c d e f g h i j k l m n o 
                            
                            Plan hash value: 3381419358
                             
                            --------------------------------------------------------------------------------------------
                            | Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                            --------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT   |                       | 40840 |    79M|   141   (1)| 00:00:02 |
                            |   1 |  NESTED LOOPS      |                       | 40840 |    79M|   141   (1)| 00:00:02 |
                            |   2 |   TABLE ACCESS FULL| STRUCTURED_INDEX_TEST |     5 | 10075 |     3   (0)| 00:00:01 |
                            |   3 |   XPATH EVALUATION |                       |       |       |            |          |
                            --------------------------------------------------------------------------------------------
                            
                            select id,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p
                            from structured_index_test,
                            xmltable('/xml' passing xml columns 
                            a varchar2(1) path 'a/text()',
                            b varchar2(1) path 'b/text()',
                            c varchar2(1) path 'c/text()',
                            d varchar2(1) path 'd/text()',
                            e varchar2(1) path 'e/text()',
                            f varchar2(1) path 'f/text()',
                            g varchar2(1) path 'g/text()',
                            h varchar2(1) path 'h/text()',
                            i varchar2(1) path 'i/text()',
                            j varchar2(1) path 'j/text()',
                            k varchar2(1) path 'k/text()',
                            l varchar2(1) path 'l/text()',
                            m varchar2(1) path 'm/text()',
                            n varchar2(1) path 'n/text()',
                            o varchar2(1) path 'o/text()',
                            p varchar2(1) path 'p/text()');
                            
                            ID                     A B C D E F G H I J K L M N O P 
                            ---------------------- - - - - - - - - - - - - - - - - 
                            0                        b c d e f g h i j k l m n o p 
                            1                        b c d e f g h i j k l m n o p 
                            2                        b c d e f g h i j k l m n o p 
                            3                        b c d e f g h i j k l m n o p 
                            4                        b c d e f g h i j k l m n o p 
                            
                            Plan hash value: 3381419358
                             
                            --------------------------------------------------------------------------------------------
                            | Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                            --------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT   |                       | 40840 |    79M|   141   (1)| 00:00:02 |
                            |   1 |  NESTED LOOPS      |                       | 40840 |    79M|   141   (1)| 00:00:02 |
                            |   2 |   TABLE ACCESS FULL| STRUCTURED_INDEX_TEST |     5 | 10075 |     3   (0)| 00:00:01 |
                            |   3 |   XPATH EVALUATION |                       |       |       |            |          |
                            --------------------------------------------------------------------------------------------
                            
                            select id,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q
                            from structured_index_test,
                            xmltable('/xml' passing xml columns 
                            a varchar2(1) path 'a/text()',
                            b varchar2(1) path 'b/text()',
                            c varchar2(1) path 'c/text()',
                            d varchar2(1) path 'd/text()',
                            e varchar2(1) path 'e/text()',
                            f varchar2(1) path 'f/text()',
                            g varchar2(1) path 'g/text()',
                            h varchar2(1) path 'h/text()',
                            i varchar2(1) path 'i/text()',
                            j varchar2(1) path 'j/text()',
                            k varchar2(1) path 'k/text()',
                            l varchar2(1) path 'l/text()',
                            m varchar2(1) path 'm/text()',
                            n varchar2(1) path 'n/text()',
                            o varchar2(1) path 'o/text()',
                            p varchar2(1) path 'p/text()',
                            q varchar2(1) path 'q/text()');
                            
                            ID                     A B C D E F G H I J K L M N O P Q 
                            ---------------------- - - - - - - - - - - - - - - - - - 
                            0                      a   c d e f g h i j k l m n o p q 
                            1                      a   c d e f g h i j k l m n o p q 
                            2                      a   c d e f g h i j k l m n o p q 
                            3                      a   c d e f g h i j k l m n o p q 
                            4                      a   c d e f g h i j k l m n o p q 
                            
                            Plan hash value: 3381419358
                             
                            --------------------------------------------------------------------------------------------
                            | Id  | Operation          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
                            --------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT   |                       | 40840 |    79M|   141   (1)| 00:00:02 |
                            |   1 |  NESTED LOOPS      |                       | 40840 |    79M|   141   (1)| 00:00:02 |
                            |   2 |   TABLE ACCESS FULL| STRUCTURED_INDEX_TEST |     5 | 10075 |     3   (0)| 00:00:01 |
                            |   3 |   XPATH EVALUATION |                       |       |       |            |          |
                            --------------------------------------------------------------------------------------------
                            
                            create index tmp_structured_index_test_ix on structured_index_test (xml)
                            indextype is xdb.xmlindex
                            parameters ('xmltable tmp_structured_index_sc ''/xml''
                            columns
                            a varchar2(1) path ''a/text()'',
                            b varchar2(1) path ''b/text()'',
                            c varchar2(1) path ''c/text()'',
                            d varchar2(1) path ''d/text()'',
                            e varchar2(1) path ''e/text()'',
                            f varchar2(1) path ''f/text()'',
                            g varchar2(1) path ''g/text()'',
                            h varchar2(1) path ''h/text()'',
                            i varchar2(1) path ''i/text()'',
                            j varchar2(1) path ''j/text()'',
                            k varchar2(1) path ''k/text()'',
                            l varchar2(1) path ''l/text()'',
                            m varchar2(1) path ''m/text()'',
                            n varchar2(1) path ''n/text()'',
                            o varchar2(1) path ''o/text()'',
                            p varchar2(1) path ''p/text()'',
                            q varchar2(1) path ''q/text()'',
                            r varchar2(1) path ''r/text()'',
                            s varchar2(1) path ''s/text()'',
                            t varchar2(1) path ''t/text()'',
                            u varchar2(1) path ''u/text()'',
                            v varchar2(1) path ''v/text()'',
                            w varchar2(1) path ''w/text()'',
                            x varchar2(1) path ''x/text()'',
                            y varchar2(1) path ''y/text()'',
                            z varchar2(1) path ''z/text()''
                            ');
                            
                            index TMP_STRUCTURED_INDEX_TEST_IX created.
                            
                            select id,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
                            from structured_index_test,
                            xmltable('/xml' passing xml columns 
                            a varchar2(1) path 'a/text()',
                            b varchar2(1) path 'b/text()',
                            c varchar2(1) path 'c/text()',
                            d varchar2(1) path 'd/text()',
                            e varchar2(1) path 'e/text()',
                            f varchar2(1) path 'f/text()',
                            g varchar2(1) path 'g/text()',
                            h varchar2(1) path 'h/text()',
                            i varchar2(1) path 'i/text()',
                            j varchar2(1) path 'j/text()',
                            k varchar2(1) path 'k/text()',
                            l varchar2(1) path 'l/text()',
                            m varchar2(1) path 'm/text()',
                            n varchar2(1) path 'n/text()',
                            o varchar2(1) path 'o/text()',
                            p varchar2(1) path 'p/text()',
                            q varchar2(1) path 'q/text()',
                            r varchar2(1) path 'r/text()',
                            s varchar2(1) path 's/text()',
                            t varchar2(1) path 't/text()',
                            u varchar2(1) path 'u/text()',
                            v varchar2(1) path 'v/text()',
                            w varchar2(1) path 'w/text()',
                            x varchar2(1) path 'x/text()',
                            y varchar2(1) path 'y/text()',
                            z varchar2(1) path 'z/text()');
                            
                            ID                     A B C D E F G H I J K L M N O P Q R S T U V W X Y Z 
                            ---------------------- - - - - - - - - - - - - - - - - - - - - - - - - - - 
                            0                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            1                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            2                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            3                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            4                      a b c d e f g h i j   l m n o p q r s t u v w x y z 
                            
                            Plan hash value: 1159232082
                             
                            ---------------------------------------------------------------------------------------------------------
                            | Id  | Operation                    | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
                            ---------------------------------------------------------------------------------------------------------
                            |   0 | SELECT STATEMENT             |                          |     5 |   445 |     2   (0)| 00:00:01 |
                            |   1 |  NESTED LOOPS                |                          |       |       |            |          |
                            |   2 |   NESTED LOOPS               |                          |     5 |   445 |     2   (0)| 00:00:01 |
                            |   3 |    INDEX FAST FULL SCAN      | SYS_C0041514             |     5 |   125 |     2   (0)| 00:00:01 |
                            |*  4 |    INDEX RANGE SCAN          | SYS133843_133844_RID_IDX |     1 |       |     0   (0)| 00:00:01 |
                            |   5 |   TABLE ACCESS BY INDEX ROWID| TMP_STRUCTURED_INDEX_SC  |     1 |    64 |     0   (0)| 00:00:01 |
                            ---------------------------------------------------------------------------------------------------------
                            
                            Predicate Information (identified by operation id):
                            ---------------------------------------------------
                             
                               4 - access("STRUCTURED_INDEX_TEST".ROWID="SYS_ALIAS_0"."RID")
                             
                            Note
                            -----
                               - dynamic sampling used for this statement (level=2)
                            Edited by: Michiel Weggen on Apr 12, 2012 12:38 AM
                            • 12. Re: Bad performance with many xmlqueries in select and big resultset
                              odie_63
                              That one's is both funny and frustrating, again ;)

                              Probably a regression in 11.2.0.3 as it works in the prior version :
                              SQL> select * from v$version;
                               
                              BANNER
                              --------------------------------------------------------------------------------
                              Oracle Database 11g Express Edition Release 11.2.0.2.0 - Production
                              PL/SQL Release 11.2.0.2.0 - Production
                              CORE     11.2.0.2.0     Production
                              TNS for 32-bit Windows: Version 11.2.0.2.0 - Production
                              NLSRTL Version 11.2.0.2.0 - Production
                               
                              SQL> 
                              SQL> select id,a,b,c,d,e,f,g,h,i,j,k,l,m,n,o,p,q,r,s,t,u,v,w,x,y,z
                                2  from structured_index_test,
                                3  xmltable('/xml' passing xml columns
                                4  a varchar2(1) path 'a/text()',
                                5  b varchar2(1) path 'b/text()',
                                6  c varchar2(1) path 'c/text()',
                                7  d varchar2(1) path 'd/text()',
                                8  e varchar2(1) path 'e/text()',
                                9  f varchar2(1) path 'f/text()',
                               10  g varchar2(1) path 'g/text()',
                               11  h varchar2(1) path 'h/text()',
                               12  i varchar2(1) path 'i/text()',
                               13  j varchar2(1) path 'j/text()',
                               14  k varchar2(1) path 'k/text()',
                               15  l varchar2(1) path 'l/text()',
                               16  m varchar2(1) path 'm/text()',
                               17  n varchar2(1) path 'n/text()',
                               18  o varchar2(1) path 'o/text()',
                               19  p varchar2(1) path 'p/text()',
                               20  q varchar2(1) path 'q/text()',
                               21  r varchar2(1) path 'r/text()',
                               22  s varchar2(1) path 's/text()',
                               23  t varchar2(1) path 't/text()',
                               24  u varchar2(1) path 'u/text()',
                               25  v varchar2(1) path 'v/text()',
                               26  w varchar2(1) path 'w/text()',
                               27  x varchar2(1) path 'x/text()',
                               28  y varchar2(1) path 'y/text()',
                               29  z varchar2(1) path 'z/text()');
                               
                                                ID A B C D E F G H I J K L M N O P Q R S T U V W X Y Z
                              -------------------- - - - - - - - - - - - - - - - - - - - - - - - - - -
                                                 0 a b c d e f g h i j k l m n o p q r s t u v w x y z
                                                 1 a b c d e f g h i j k l m n o p q r s t u v w x y z
                                                 2 a b c d e f g h i j k l m n o p q r s t u v w x y z
                                                 3 a b c d e f g h i j k l m n o p q r s t u v w x y z
                                                 4 a b c d e f g h i j k l m n o p q r s t u v w x y z
                               
                              You're gonna have to submit a SR to Oracle Support.
                              • 13. Re: Bad performance with many xmlqueries in select and big resultset
                                Michiel Weggen
                                Just noticed we're on 11.2.0.1.0 on the development DB instead of 11.2.0.3.0, gonna check if we can update to a newer patch version.

                                Edited by: Michiel Weggen on Apr 12, 2012 1:34 AM