4 Replies Latest reply: Mar 4, 2014 11:43 PM by eduardoEloy RSS

    Incorrect execution plan for xml extract / xmltable in relational view

    eduardoEloy

      The problem: Oracle views that join database tables with data obtained from XML using Extract and ExtractValue use an invalid execution plan in Oracle 11.2g. Does anyone know if this is a known problem with a simple solution?

       

      My workplace has recently upgraded from Oracle 10.1 to Oracle 11.2 database. Some of our important legacy code uses views to expose and extract XML data. Testing reveals that these views no longer work consistently in 11.2g. The execution plan seems inconsistent and wrong in that it tries to join extracted XML data to relational table data before extracting the XML data, hence returning nothing when there is clearly data. We realise that Extract and ExtractValue are deprecated in 11.2g, but execution plans seem to have the same problem when we use XMLTable.

       

      Strangely, after running the following queries without hints, then with the hints and getting consistent results (no data without the hints, data with the hints), retrying a few days later gives different results and execution plans so the queries work even without the hints. This has occurred in two different Oracle instances. Does the optimiser “learn”? It still doesn’t give us faith that it will work consistently over time.

       

      Examples: Without going into the meaning of the XML data or why the original views are defined as they are – the original developers are no longer here to explain – here are some examples of original view definition queries and their execution plans, followed by modified versions and their plans. Note that the originals sometimes work, sometimes don’t, so we’re not confident.

       

      1. Original view’s query:  In 10.1g this returned row(s) when expected. In 11.2g, due to the evaluation order, no rows are ever returned when the execution plan is as shown. Note that sometimes the exec plan changes and it works.

      {code}

         SELECT

                xml.history_id,

                xml.game_name,

                xml.game_version_number,

                xml.gamble_type_xref,

                gt.code_id gamble_type_id

           FROM (SELECT history_id,

                        UPPER (EXTRACTVALUE (VALUE (software_game), '//Game_Name'))

                           game_name,

                        EXTRACTVALUE (VALUE (software_game),

                                      '//Game_Version_Number')

                           game_version_number,

                        EXTRACTVALUE (VALUE (game_gamble), '//Game_Gamble')

                           gamble_type_xref

                   FROM ogr_test_xml,

                        TABLE (

                           XMLSEQUENCE (

                              EXTRACT (

                                 xml_file,

      '/submission/Recommended_Product/Software/Software_Game'))) software_game,

                        TABLE (

                           XMLSEQUENCE (

                              EXTRACT (VALUE (software_game),

                                       'Software_Game/Game_Gamble'))) game_gamble)

                xml,

                adm_code_ms gt

          WHERE     gt.external_ref = xml.gamble_type_xref

          AND gt.type_code = (SELECT code_id

                              FROM adm_code_ms

                              WHERE internal_ref = 'TSV_GAMBLE_METHOD')

          AND xml.history_id = 110 -----added to get specific data

      {code}

       

      ---------------------------------------------------------------------------------------------

      | Id  | Operation                       | Name                      | Rows  | Bytes | Cost  |

      ---------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT                |                           |     1 |  2035 |    66 |

      |   1 |  NESTED LOOPS                   |                           |     1 |  2035 |    64 |

      |   2 |   NESTED LOOPS                  |                           |     1 |  2033 |    34 |

      |   3 |    MERGE JOIN CARTESIAN         |                           |     1 |  2027 |     5 |

      |   4 |     TABLE ACCESS FULL           | OGR_TEST_XML              |     1 |  2015 |     2 |

      |   5 |     BUFFER SORT                 |                           |     8 |    96 |     3 |

      |   6 |      TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS               |     8 |    96 |     3 |

      |   7 |       INDEX RANGE SCAN          | IDX_ADM_CODE_TYPE         |     8 |       |     1 |

      |   8 |        INDEX RANGE SCAN         | IDX_ADM_CODE_INT_REF_CODE |     1 |    15 |     2 |

      |   9 |    XPATH EVALUATION             |                           |       |       |       |

      |  10 |   XPATH EVALUATION              |                           |       |       |       |

      ---------------------------------------------------------------------------------------------

       

      2.  Add a hint to force evaluation order. The execution plan changes and it now returns row(s) as expected.

       

      SELECT /*+ordered*/   -------- This is only change from (1)

                xml.history_id,

                xml.game_name,

                xml.game_version_number,

                xml.gamble_type_xref,

                gt.code_id gamble_type_id

           FROM (SELECT history_id, ...

       

      ------------------------------------------------------------------------------------------

      | Id  | Operation                    | Name                      | Rows  | Bytes | Cost  |

      ------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT             |                           |     1 |  2035 |    66 |

      |   1 |  NESTED LOOPS                |                           |     1 |  2035 |    64 |

      |   2 |   NESTED LOOPS               |                           |     1 |  2023 |    61 |

      |   3 |    NESTED LOOPS              |                           |     1 |  2021 |    31 |

      |   4 |     TABLE ACCESS FULL        | OGR_TEST_XML              |     1 |  2015 |     2 |

      |   5 |     XPATH EVALUATION         |                           |       |       |       |

      |   6 |    XPATH EVALUATION          |                           |       |       |       |

      |   7 |   TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS               |     1 |    12 |     3 |

      |   8 |    INDEX RANGE SCAN          | IDX_ADM_CODE_TYPE         |     8 |       |     1 |

      |   9 |     INDEX RANGE SCAN         | IDX_ADM_CODE_INT_REF_CODE |     1 |    15 |     2 |

      ------------------------------------------------------------------------------------------

       

      3.  Rewrite the SQL using XMLTable. This failed to return row(s) initially, due to its execution plan.

         SELECT xml.history_id

               ,xml.game_name

               ,xml.game_version_number

               ,xml.gamble_type_xref

               ,gt.code_id gamble_type_id

           FROM (

                 SELECT history_id

                      , upper(software_game."game_name")      game_name

                      , software_game."game_version_number"   game_version_number

                      , software_game."gamble_type_xref"      gamble_type_xref

                   FROM ogr_test_xml,

      xmltable('/submission/Recommended_Product/Software/Software_Game' passing xml_file

                columns

                  "game_name" varchar2(20) path '//Game_Name/text()',

                  "game_version_number" varchar2(20) path

                                   '//Game_Version_Number/text()',

                  "gamble_type_xref"  varchar2(20) path  '//Game_Gamble/text()'

                                )software_game

                 ) xml,

                adm_code_ms gt

          WHERE     gt.external_ref = xml.gamble_type_xref

          AND       gt.type_code = (SELECT code_id

                                      FROM adm_code_ms

                                     WHERE internal_ref = 'TSV_GAMBLE_METHOD')

          AND xml.history_id = 110 -----added to get specific data

      --------------------------------------------------------------------------------------------

      | Id  | Operation                      | Name                      | Rows  | Bytes | Cost  |

      --------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT               |                           |     1 |  2033 |    37 |

      |   1 |  NESTED LOOPS                  |                           |     1 |  2033 |    35 |

      |   2 |   MERGE JOIN CARTESIAN         |                           |     1 |  2027 |     5 |

      |   3 |    TABLE ACCESS FULL           | OGR_TEST_XML              |     1 |  2015 |     2 |

      |   4 |    BUFFER SORT                 |                           |     8 |    96 |     3 |

      |   5 |     TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS               |     8 |    96 |     3 |

      |   6 |      INDEX RANGE SCAN          | IDX_ADM_CODE_TYPE         |     8 |       |     1 |

      |   7 |       INDEX RANGE SCAN         | IDX_ADM_CODE_INT_REF_CODE |     1 |    15 |     2 |

      |   8 |   XPATH EVALUATION             |                           |       |       |       |

      --------------------------------------------------------------------------------------------

      4.  Add hint to XMLTable version: This returned row(s) immediately.

         SELECT /*+ordered*/ -----this is the only change from (3)

                xml.history_id

               ,xml.game_name

               ,xml.game_version_number

               ,xml.gamble_type_xref

               ,gt.code_id gamble_type_id

           FROM (...

      ------------------------------------------------------------------------------------------

      | Id  | Operation                    | Name                      | Rows  | Bytes | Cost  |

      ------------------------------------------------------------------------------------------

      |   0 | SELECT STATEMENT             |                           |     1 |  2033 |    37 |

      |   1 |  NESTED LOOPS                |                           |     1 |  2033 |    35 |

      |   2 |   NESTED LOOPS               |                           |     1 |  2021 |    32 |

      |   3 |    TABLE ACCESS FULL         | OGR_TEST_XML              |     1 |  2015 |     2 |

      |   4 |    XPATH EVALUATION          |                           |       |       |       |

      |   5 |   TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS               |     1 |    12 |     3 |

      |   6 |    INDEX RANGE SCAN          | IDX_ADM_CODE_TYPE         |     8 |       |     1 |

      |   7 |     INDEX RANGE SCAN         | IDX_ADM_CODE_INT_REF_CODE |     1 |    15 |     2 |

      ------------------------------------------------------------------------------------------

      Any suggestions?

        • 1. Re: Incorrect execution plan for xml extract / xmltable in relational view
          odie_63

          Difficult to say without a test case to reproduce the issue.

           

           

          What's the exact DB version?

           

          Could you post the DDL for the OGR_TEST_XML table? (use DBMS_METADATA.GET_DDL)

          Is it possible to give at least a sample XML doc, with dummy data if necessary?

           

          What about the cardinalities in the explain plan? Do you find them consistent with the reality?

          How much volume of XML data are we talking about here? (number of rows, average row size?)

           

          Does the optimiser “learn”?

          I'm thinking "stats gathering" and "bind peeking" to explain why the plan changes from time to time.

          Are the stats always up to date?

           

          Could you give full explain plans with predicate information?

          • 2. Re: Incorrect execution plan for xml extract / xmltable in relational view
            eduardoEloy

            Thanks. I didn't post much detail in the hope there was a well known, obvious answer. I've put data examples and DDL below.

            We're using Oracle 11.2.0.3.0 Enterprise Edition.

            OGR_TEST_XML table basically has the XML document stored in an xmltype column, a foreign key to a table with details of the original xml file and a surrogate key. I'll include its full DDL at the end. ADM_CODE_MS is a subtypes reference codes table that uses the internal_ref  column to identify a row and an external_ref to join with the incoming xml. The query above basically extracts info from the xml doc, along with the ref code id where the value of the xml's gamle_type_xref matches the external ref of one of our TSV_GAMBLE_METHOD codes. The xml data we are testing here has only a few rows. See the sample xml doc below.

             

            The changed execution plan happened when rerunning the same queries a day or two after running the original queries and the ones with the ordered hint. That is, we cannot reproduce the original problem. I've now run out of test db instances!...but I don't actually trust that everything's fixed forever. The cardinalities look OK.

             

             

            ADM_CODE_MS -  subset of relevant data used above - There are 5 more but this gives an idea:

            code_id      type_code      description      internal_ref                               external_ref

            9164           9163           High Stake                                                        HGTK

            9165           9163           No                     TSV_GAMBLE_METHOD_NO     NO

            9171           9163           Yes                                                                    YES

            9172           9163           Beat the Dealer x2                                               BEAT

             

            9163           649           Gamble Method      TSV_GAMBLE_METHOD  ----------- parent / subtype code obtained in subquery

             

            Incoming XML document from xml_file (xmltype) column of ogr_test_xml table where history_id=110 in this case. This is a realistice amount of data.

            <?xml version="1.0" encoding="WINDOWS-1252"?>
            <submission xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
              <Manufacturer>
                <Manufacturer_Name>TESTMFR</Manufacturer_Name>
                <Manufacturer_Identifier>01</Manufacturer_Identifier>
                <Manufacturer_Contact>
                  <Contact_Name>J J Tester </Contact_Name>
                  <Contact_Email>JJtester@test.com</Contact_Email>
                  <Contact_Phone>02 1111 1101</Contact_Phone>
                  <Contact_Address>9-99 Test St, ASuburb, Sydney 2018</Contact_Address>
                </Manufacturer_Contact>
                <Manufacturer_Ref>XXX</Manufacturer_Ref>
                <Submission_Date>2014-08-07</Submission_Date>
              </Manufacturer>
              <Recommending_Authority>
                <ATF>57</ATF>
                <ATF_File_No>IXX.565.4532h</ATF_File_No>
                <Recommendation_Date>2013-09-25</Recommendation_Date>
                <Test_Engineer>
                  <Contact_Name>J J Test</Contact_Name>
                </Test_Engineer>
                <Managing_Engineer>
                  <Contact_Name>J J Test</Contact_Name>
                </Managing_Engineer>
                <NATA_Approved_Signatory>
                  <Contact_Name>J J Test</Contact_Name>
                </NATA_Approved_Signatory>
              </Recommending_Authority>
              <Jurisdiction>Queensland</Jurisdiction>
              <Recommended_Product>
                <Requirements_Tested>
                  <Title>NXX10</Title>
                  <Version>10.3</Version>
                </Requirements_Tested>
                <Artwork_Details>
                  <Artwork_Part_Number>AATEST, Version 01</Artwork_Part_Number>
                  <Artwork_Type>TOP</Artwork_Type>
                  <Art_Market>CLUB</Art_Market>
                  <Art_Market>QXXX</Art_Market>
                  <Artwork_File_Name>Test Game 111.jpg</Artwork_File_Name>
                </Artwork_Details>
              </Recommended_Product>
              <Recommended_Product>
                <Requirements_Tested>
                  <Title>XXX10</Title>
                  <Version>10.3</Version>
                </Requirements_Tested>
                <Software>
                  <Software_Game>
                    <Game_Software_Type>SGAM</Game_Software_Type>
                    <Game_Name>Test Game 111</Game_Name>
                    <Game_ID>X8TESTA</Game_ID>
                    <Variations>
                      <Var_Num>03</Var_Num>
                      <Min_RTP>89.111</Min_RTP>
                      <Max_RTP>89.111</Max_RTP>
                      <Optimal_RTP>89.111</Optimal_RTP>
                      <Standard_Deviation>9.99</Standard_Deviation>
                      <Max_Win>999000</Max_Win>
                      <Max_Bet>500</Max_Bet>
                      <Max_Bet_Per_Line>25</Max_Bet_Per_Line>
                      <Max_Lines>30</Max_Lines>
                      <Variation_Market>CLUB</Variation_Market>
                      <Variation_Market>QXXX</Variation_Market>
                    </Variations>
                    <Game_Version_Number>11XO</Game_Version_Number>
                    <Game_Description>This is a test this is a test this is a test</Game_Description>
                    <Game_Play_Type>LINE</Game_Play_Type>
                    <Game_Display_Type>V</Game_Display_Type>
                    <Game_MD>
                      <MD_Market>CLUB</MD_Market>
                      <MD_Denomination>1</MD_Denomination>
                      <MD_Denomination>2</MD_Denomination>
                      <MD_Denomination>5</MD_Denomination>
                    </Game_MD>
                    <Game_MD>
                      <MD_Market>QXXX</MD_Market>
                      <MD_Denomination>1</MD_Denomination>
                      <MD_Denomination>2</MD_Denomination>
                      <MD_Denomination>5</MD_Denomination>
                      <MD_Denomination>10</MD_Denomination>
                      <MD_Denomination>20</MD_Denomination>
                      <MD_Denomination>50</MD_Denomination>
                    </Game_MD>
                    <Game_Token>100</Game_Token>
                    <Game_Protocol>X4Y4X</Game_Protocol>
                    <Game_Peripherals>
                      <Progressive_Controller>N</Progressive_Controller>
                      <Touch_Screen>Y</Touch_Screen>
                      <Ticket_Printer>Y</Ticket_Printer>
                      <Coin_Acceptor>Y</Coin_Acceptor>
                      <Note_Acceptor>Y</Note_Acceptor>
                      <Hopper>Y</Hopper>
                      <Progressive_Display>N</Progressive_Display>
                    </Game_Peripherals>
                    <Game_Gamble>YES</Game_Gamble>
                  </Software_Game>
                  <PSD>
                    <PSD_Name>Test Princess</PSD_Name>
                    <PSD_Type>HDD</PSD_Type>
                    <PSD_Label>X81000XX.1</PSD_Label>
                    <Position>1</Position>
                    <PSD_Hash>
                      <HMAC_SHA-1>
                        <HMACSHA1_Byte_Order>XCX</HMACSHA1_Byte_Order>
                        <HMACSHA1_Key>0000000000000000000000000000000</HMACSHA1_Key>
                        <HMACSHA1_Digest>C1FDGFDGSDSGDS865B1F</HMACSHA1_Digest>
                      </HMAC_SHA-1>
                    </PSD_Hash>
                  </PSD>
                  <PSD>
                    <PSD_Name>Test Princess</PSD_Name>
                    <PSD_Type>HDD</PSD_Type>
                    <PSD_Label>X81000RA.1</PSD_Label>
                    <Position>1</Position>
                    <PSD_Hash>
                      <HMAC_SHA-1>
                        <HMACSHA1_Byte_Order>BLL</HMACSHA1_Byte_Order>
                        <HMACSHA1_Key>00000000000000000000000000</HMACSHA1_Key>
                        <HMACSHA1_Digest>D9C1FDSGDGFD5087BFD43BDA4</HMACSHA1_Digest>
                      </HMAC_SHA-1>
                    </PSD_Hash>
                  </PSD>
                  <PSD>
                    <PSD_Name>Test Princess</PSD_Name>
                    <PSD_Type>HDD</PSD_Type>
                    <PSD_Label>game.manifest</PSD_Label>
                    <Position>1</Position>
                    <PSD_Hash>
                      <HMAC_SHA-1>
                        <HMACSHA1_Byte_Order>BLL</HMACSHA1_Byte_Order>
                        <HMACSHA1_Key>000000000000000000000000000000</HMACSHA1_Key>
                        <HMACSHA1_Digest>F1FDGFDG6575FGFGHFDSE580BADCE</HMACSHA1_Digest>
                      </HMAC_SHA-1>
                    </PSD_Hash>
                  </PSD>
                  <PSD>
                    <PSD_Name>Test Princess</PSD_Name>
                    <PSD_Type>HDD</PSD_Type>
                    <PSD_Label>game.manifest.sig</PSD_Label>
                    <Position>1</Position>
                    <PSD_Hash>
                      <HMAC_SHA-1>
                        <HMACSHA1_Byte_Order>BLL</HMACSHA1_Byte_Order>
                        <HMACSHA1_Key>0000000000000000000000000000000</HMACSHA1_Key>
                        <HMACSHA1_Digest>DAFDGDFGFDSD546546FD80C8B054436ACEB2</HMACSHA1_Digest>
                      </HMAC_SHA-1>
                    </PSD_Hash>
                  </PSD>
                </Software>
                <Overall_Hash>
                  <HMAC_SHA-1>
                    <HMACSHA1_Byte_Order>BLL</HMACSHA1_Byte_Order>
                    <HMACSHA1_Key>0000000000000000000000000000</HMACSHA1_Key>
                    <HMACSHA1_Digest>5E9D36GHFGHHF4D83BC0C976CBA</HMACSHA1_Digest>
                  </HMAC_SHA-1>
                </Overall_Hash>
              </Recommended_Product>
              <General_Comments_Notes>
                <Note>None</Note>
              </General_Comments_Notes>
              <Recommendation_Attachments>
                <Attachment>None</Attachment>
                <Attachment>None</Attachment>
              </Recommendation_Attachments>
              <Submission_Reference>9</Submission_Reference>
            </submission>

             

            OGR_TEST_XML DDL


            CREATE TABLE OGRGEN.OGR_TEST_XML
            (
              TEST_XML_ID  NUMBER(10)                       NOT NULL,  -- surrogate key
              HISTORY_ID   NUMBER(10)                       NOT NULL,  -- fk to table with details of the incoming xml file
              XML_FILE     SYS.XMLTYPE
            )
            XMLTYPE XML_FILE STORE AS SECUREFILE BINARY XML (
              TABLESPACE  USERS
              ENABLE      STORAGE IN ROW
              CHUNK       8192
              NOCACHE
              LOGGING
                  STORAGE    (
                              INITIAL          104K
                              NEXT             1M
                              MINEXTENTS       1
                              MAXEXTENTS       UNLIMITED
                              PCTINCREASE      0
                              BUFFER_POOL      DEFAULT
                              FLASH_CACHE      DEFAULT
                              CELL_FLASH_CACHE DEFAULT
                             ))
            ALLOW NONSCHEMA
            DISALLOW ANYSCHEMA
            TABLESPACE USERS
            RESULT_CACHE (MODE DEFAULT)
            PCTUSED    0
            PCTFREE    10
            INITRANS   1
            MAXTRANS   255
            STORAGE    (
                        INITIAL          64K
                        NEXT             1M
                        MAXSIZE          UNLIMITED
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        BUFFER_POOL      DEFAULT
                        FLASH_CACHE      DEFAULT
                        CELL_FLASH_CACHE DEFAULT
                       )
            LOGGING
            NOCOMPRESS
            NOCACHE
            NOPARALLEL
            MONITORING;


            CREATE UNIQUE INDEX OGRGEN.PK_OGR_TEST_XML ON OGRGEN.OGR_TEST_XML
            (TEST_XML_ID)
            LOGGING
            TABLESPACE IDX_USERS
            PCTFREE    10
            INITRANS   2
            MAXTRANS   255
            STORAGE    (
                        INITIAL          128K
                        NEXT             128K
                        MAXSIZE          UNLIMITED
                        MINEXTENTS       1
                        MAXEXTENTS       UNLIMITED
                        PCTINCREASE      0
                        BUFFER_POOL      DEFAULT
                        FLASH_CACHE      DEFAULT
                        CELL_FLASH_CACHE DEFAULT
                       )
            NOPARALLEL;


            CREATE OR REPLACE PUBLIC SYNONYM OGR_TEST_XML FOR OGRGEN.OGR_TEST_XML;


            ALTER TABLE OGRGEN.OGR_TEST_XML ADD (
              CONSTRAINT PK_OGR_TEST_XML
              PRIMARY KEY
              (TEST_XML_ID)
              USING INDEX OGRGEN.PK_OGR_TEST_XML
              ENABLE VALIDATE);

            ALTER TABLE OGRGEN.OGR_TEST_XML ADD (
              CONSTRAINT FK_OGR_TEST_XML_FILE_HISTORY
              FOREIGN KEY (HISTORY_ID)
              REFERENCES OGRGEN.OGR_FILE_HISTORY (HISTORY_ID)
              ON DELETE CASCADE
              ENABLE NOVALIDATE);

            • 3. Re: Incorrect execution plan for xml extract / xmltable in relational view
              odie_63

              Thanks for the extended test case.

               

              I don't see any issue on my side.

              I've inserted 100 copies of the sample XML and got this result :

              Connected to:

              Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

              With the Partitioning, Real Application Clusters, Automatic Storage Management, OLAP,

              Data Mining and Real Application Testing options

               

              SQL> set lines 200

              SQL> set pages 100

              SQL> set autotrace on explain

              SQL>

              SQL> SELECT xml.history_id

                2        ,xml.game_name

                3        ,xml.game_version_number

                4        ,xml.gamble_type_xref

                5        ,gt.code_id gamble_type_id

                6    FROM (

                7          SELECT history_id

                8               , upper(software_game."game_name")      game_name

                9               , software_game."game_version_number"   game_version_number

              10               , software_game."gamble_type_xref"      gamble_type_xref

              11            FROM ogr_test_xml,

              12            xmltable('/submission/Recommended_Product/Software/Software_Game' passing xml_file

              13         columns

              14           "game_name" varchar2(20) path 'Game_Name',

              15           "game_version_number" varchar2(20) path

              16                            'Game_Version_Number',

              17           "gamble_type_xref"  varchar2(20) path  'Game_Gamble'

              18                         )software_game

              19          ) xml,

              20         adm_code_ms gt

              21   WHERE     gt.external_ref = xml.gamble_type_xref

              22   AND       gt.type_code = (SELECT code_id

              23                               FROM adm_code_ms

              24                              WHERE internal_ref = 'TSV_GAMBLE_METHOD')

              25   AND xml.history_id = 110 ;

               

              HISTORY_ID GAME_NAME            GAME_VERSION_NUMBER  GAMBLE_TYPE_XREF     GAMBLE_TYPE_ID

              ---------- -------------------- -------------------- -------------------- --------------

                     110 TEST GAME 111        11XO                 YES                            9171

               

               

              Execution Plan

              ----------------------------------------------------------

              Plan hash value: 1960365211

               

              ------------------------------------------------------------------------------------------------------------

              | Id  | Operation                      | Name                      | Rows  | Bytes | Cost (%CPU)| Time     |

              ------------------------------------------------------------------------------------------------------------

              |   0 | SELECT STATEMENT               |                           |  5105 |  7248K|    41   (3)| 00:00:01 |

              |*  1 |  HASH JOIN                     |                           |  5105 |  7248K|    39   (3)| 00:00:01 |

              |   2 |   TABLE ACCESS BY INDEX ROWID  | ADM_CODE_MS               |     3 |    36 |     2   (0)| 00:00:01 |

              |*  3 |    INDEX RANGE SCAN            | IDX_ADM_CODE_TYPE         |     3 |       |     1   (0)| 00:00:01 |

              |   4 |     TABLE ACCESS BY INDEX ROWID| ADM_CODE_MS               |     1 |    13 |     2   (0)| 00:00:01 |

              |*  5 |      INDEX RANGE SCAN          | IDX_ADM_CODE_INT_REF_CODE |     1 |       |     1   (0)| 00:00:01 |

              |   6 |   NESTED LOOPS                 |                           |  8168 |    11M|    36   (0)| 00:00:01 |

              |*  7 |    TABLE ACCESS STORAGE FULL   | OGR_TEST_XML              |     1 |  1436 |     7   (0)| 00:00:01 |

              |   8 |    XPATH EVALUATION            |                           |       |       |            |       |

              ------------------------------------------------------------------------------------------------------------

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

               

                 1 - access("GT"."EXTERNAL_REF"=CAST("P"."C_01$" AS varchar2(20) ))

                 3 - access("GT"."TYPE_CODE"= (SELECT "CODE_ID" FROM "ADM_CODE_MS" "ADM_CODE_MS" WHERE

                            "INTERNAL_REF"='TSV_GAMBLE_METHOD'))

                 5 - access("INTERNAL_REF"='TSV_GAMBLE_METHOD')

                 7 - storage("HISTORY_ID"=110)

                     filter("HISTORY_ID"=110)

               

               

              • 4. Re: Incorrect execution plan for xml extract / xmltable in relational view
                eduardoEloy

                Thanks again. Our problem is that we now get the correct row(s) returned in our non-production dev/test environments for the above example, but this was not the case initially. We have around 30 views that use SQL similar to but more or less complex than the above example. Some seem to work immediately. Others, like the above example, did not work when first run in any of our test systems. After adding the /*+ordered*/ hint the above example returned the correct rows. When we logged back in a day or two later, it returned the correct rows even without the hint. This was repeated in 3 non-prod test environments. We are unsure whether the optimiser will revert to the earlier execution plan at some stage and start missing production data.

                 

                Has anyone else reported anything similar? Is this whole approach plain wrong or crazy? My personal preference, whatever method is used, is to separate the steps of 9i) extraction of data from an incoming file and (ii) interpreting/filtering/transforming using other data. However, this is legacy code and we're stuck with it unless there is a very good reason to rewrite it. I hope the above is not a very good reason!...