1 2 Previous Next 17 Replies Latest reply: Jul 19, 2013 8:07 AM by odie_63 RSS

    XMLTable Query Causes VM Stack Overflow

    840690
      Background Info
      >
      SQL*Plus: Release 11.2.0.1.0 Production on Wed Jul 18 06:21:00 2012

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      With the Partitioning, Automatic Storage Management, OLAP, Data Mining
      and Real Application Testing options

      Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
      PL/SQL Release 11.2.0.1.0 - Production
      CORE 11.2.0.1.0 Production
      TNS for Linux: Version 11.2.0.1.0 - Production
      NLSRTL Version 11.2.0.1.0 - Production
      >

      Problem
      I have run into a problem with a query that uses XMLTable to aggregate data from XML instances.

      My scenario: I have a table with a XMLType (binary) column that contains an XML document. For the sake diagnosing the issue, I have replicated this table (CTAS) and filled it with a subset (~130,000 records) of my larger table's data. The query itself is basic in that it uses an XMLTable to create counts of items within the XML document, the query then aggregates the counts by performing a SUM. The following SQL is an example of the query; however, I've removed all but one of my XPath query items:
      SELECT 
              COUNT(*) as TOTAL_RECORDS,
              SUM(T7.item) AS "ITEM"
       FROM NADS_TEMP_C n
              ,XMLTable(XMLNAMESPACES('http://mynamespace/goes/here' as "p1"),
                     '/*' PASSING n.DATA_INTERNAL
                     COLUMNS
                          item NUMBER PATH 'count(/Arrest)'
                ) T7;
      When I run my query I get the following error:
      ERROR at line 1:
      ORA-19112: error raised during evaluation:
      XVM-00651: VM Stack overflow.
      If I restrict the query to 11,109 rows the query runs without an issue and returns the expected results.

      I typically use the /*+ no_xml_query_rewrite */ hint when running this query as it significantly improves performance; however, on the above case it throws the following error:
      ERROR at line 1:
      ORA-19112: error raised during evaluation:
      XVM-00004: internal error "invalid address"
      It appears that the XVM is running out of memory; however, I cannot find anything that specifies where to set/adjust the XVM memory for XPath evaluations.

      I've also tried:
      * modified the XMLType column from Binary SecureFile to BasicFile and to CLOB
      * trimmed the * of columns in the Query down from 65 XMLTable columns to 1, 5, 10, etc. to alleviate invalid XPaths and/or to reduce memory footprint

      At this point, I'm stuck as to what the issue could be and a possible solution/work around. Any ideas or tips would be greatly appreciated.

      Edited by: drad on Jul 19, 2012 7:48 AM - added background info

      Edited by: drad on Jul 19, 2012 7:52 AM
        • 1. Re: XMLTable Query Causes VM Stack Overflow
          odie_63
          Hi,

          You should report that to Oracle Support.
          They've already released a patch in the past solving a similar issue with the XVM stack. Maybe it can be applied here, or at least they can advise on something else.

          Does the explain plan show XPATH EVALUATION or COLLECTION ITERATOR operations?
          I typically use the /*+ no_xml_query_rewrite */ hint when running this query as it significantly improves performance
          Really?
          I would have thought quite the opposite given your actual storage method.
          I definitely want to see the explain plan then.
          • 2. Re: XMLTable Query Causes VM Stack Overflow
            Marco Gralike
            Please dont use the hint if not actually needed. Although due to the /* I can imagine that in the end you force a not optimal solution in memory anyway. You could try to, as a quick and dirty workaround, set the java pool size and PGA by hand. This probably will work until you deplete your memory again forcing every possible solution to be handled in memory. XVM environments in 11.2.0.1 and 11.2.0.2 are different regarding used solutions.
            • 3. Re: XMLTable Query Causes VM Stack Overflow
              840690
              Thanks Marco.
              I'm actually not using /* in my final query (the above query was a small example). I've included my full query below which shows the qualified path and xpaths I am using. If you see any room for improvement please let me know.
              SELECT /*+ no_xml_query_rewrite */
                      COUNT(*) as TOTAL_RECORDS,
                      SUM(T7.x903s1) AS OFCRFirstName,
                      SUM(T7.x884s2) AS OFCRMiddleName,
                      SUM(T7.x847s3) AS OFCRLastName,
                      SUM(T7.x916s4) AS OFCRFullName,
                      SUM(T7.x871s5) AS WTNSFirstName,
                      SUM(T7.x860s6) AS WTNSMiddleName,
                      SUM(T7.x859s7) AS WTNSLastName,
                      SUM(T7.x944s8) AS WTNSGender,
                      SUM(T7.x844s9) AS WTNSTelephone,
                      SUM(T7.x921s10) AS VCTMPersonFirstName,
                      SUM(T7.x940s11) AS VCTMPersonMiddleName,
                      SUM(T7.x881s12) AS VCTMPersonLastName,
                      SUM(T7.x899s13) AS VCTMPersonFullName,
                      SUM(T7.x932s14) AS VCTMPersonGender,
                      SUM(T7.x843s15) AS VCTMPersonTelephone,
                      SUM(T7.x912s16) AS VCTMOrgName,
                      SUM(T7.x913s17) AS VCTMOrgTelephoneNumber,
                      SUM(T7.x839s18) AS SUBJPersonFirstName,
                      SUM(T7.x850s19) AS SUBJPersonMiddleName,
                      SUM(T7.x938s20) AS SUBJPersonLastName,
                      SUM(T7.x928s21) AS SUBJPersonFullName,
                      SUM(T7.x851s22) AS SUBJPersonGender,
                      SUM(T7.x863s23) AS SUBJPersonDOB,
                      SUM(T7.x837s24) AS SUBJPersonEthnicity,
                      SUM(T7.x920s25) AS SUBJPersonEyeColor,
                      SUM(T7.x888s26) AS SUBJPersonDLNumber,
                      SUM(T7.x874s27) AS SUBJPersonAdult,
                      SUM(T7.x907s28) AS SUBJPersonFBINumber,
                      SUM(T7.x909s29) AS SUBJPersonHairColor,
                      SUM(T7.x894s30) AS SUBJPersonHeight,
                      SUM(T7.x838s31) AS SUBJPersonPassportID,
                      SUM(T7.x856s32) AS SUBJPersonSID,
                      SUM(T7.x906s33) AS SUBJPersonSSN,
                      SUM(T7.x939s34) AS SUBJPersonWeight,
                      SUM(T7.x924s35) AS SUBJTelephone,
                      SUM(T7.x887s36) AS SUBJOrgName,
                      SUM(T7.x867s37) AS SUBJOrgTeleNumber,
                      SUM(T7.x866s38) AS SUBJMOActionCode,
                      SUM(T7.x900s39) AS INCDIncidentDate,
                      SUM(T7.x886s40) AS INCDIncidentStartDate,
                      SUM(T7.x858s41) AS INCDIncidentEndDate,
                      SUM(T7.x918s42) AS INCDReportingOfficer,
                      SUM(T7.x849s43) AS INCDNarrativeActDesc,
                      SUM(T7.x873s44) AS INCDLocSteetPreDirection,
                      SUM(T7.x848s45) AS INCDLocStreetNumber,
                      SUM(T7.x864s46) AS INCDLocStreetName,
                      SUM(T7.x908s47) AS INCDLocStreetPostDirection,
                      SUM(T7.x893s48) AS INCDLocFullStreet,
                      SUM(T7.x872s49) AS INCDLocFullAddress,
                      SUM(T7.x857s50) AS INCDLocCity,
                      SUM(T7.x880s51) AS INCDLocState,
                      SUM(T7.x865s52) AS INCDLocZip,
                      SUM(T7.x836s53) AS INCDLocCountry,
                      SUM(T7.x885s54) AS OFFNOffenseCode,
                      SUM(T7.x927s55) AS OFFNDescription,
                      SUM(T7.x904s56) AS OFFNCmpIndicator,
                      SUM(T7.x946s57) AS OFFNForceUsed,
                      SUM(T7.x945s58) AS OFFNDomesticViolence,
                      SUM(T7.x937s59) AS WRNTWarrantDate,
                      SUM(T7.x902s60) AS WRNTAgency,
                      SUM(T7.x846s61) AS WRNTIssuingAuthority,
                      SUM(T7.x862s62) AS WRNTDescription,
                      SUM(T7.x861s63) AS WRNTComplainantOrg,
                      SUM(T7.x852s64) AS WRNTCplPerson,
                      SUM(T7.x734s65) AS EmailAddress
               FROM NADS n
                      ,METAS met, META_TYPES mtt
                      ,XMLTable(XMLNAMESPACES('http://fbi.gov/cjis/N-DEx' as "p1"),
                      '/p1:DataItem' PASSING n.DATA_INTERNAL
                  COLUMNS
                        x903s1 NUMBER PATH 'count(Person[Role="Enforcement Official" and string-length(Name/FirstName)>0])',
                      x884s2 NUMBER PATH 'count(Person[Role="Enforcement Official" and string-length(Name/MiddleName)>0])',
                        x847s3 NUMBER PATH 'count(Person[Role="Enforcement Official" and string-length(Name/LastName)>0])',
                      x916s4 NUMBER PATH 'count(Person[Role="Enforcement Official" and string-length(Name/FullName)>0])',
                      x871s5 NUMBER PATH 'count(Person[Role="Witness" and string-length(Name/FirstName)>0])',
                      x860s6 NUMBER PATH 'count(Person[Role="Witness" and string-length(Name/MiddleName)>0])',
                      x859s7 NUMBER PATH 'count(Person[Role="Witness" and string-length(Name/LastName)>0])',
                      x944s8 NUMBER PATH 'count(Person[Role="Witness" and string-length(Sex)>0])',
                      x844s9 NUMBER PATH 'count(Person[Role="Witness" and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber/AssociationReference/AssociationGUID]/AssociationReference/AssociationGUID])',
                      x921s10 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/FirstName)>0])',
                      x940s11 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/MiddleName)>0])',
                      x881s12 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/LastName)>0])',
                      x899s13 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/FullName)>0])',
                      x932s14 NUMBER PATH 'count(Person[Role="Victim" and string-length(Sex)>0])',
                      x843s15 NUMBER PATH 'count(Person[Role="Victim" and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber/AssociationReference/AssociationGUID]/AssociationReference/AssociationGUID])',
                      x912s16 NUMBER PATH 'count(Organization[Role="Victim" and string-length(Name)>0])',
                      x913s17 NUMBER PATH 'count(Organization[Role="Victim" and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[string-length(FullNumber)>0 or string-length(InternationalNumber)>0 or string-length(NANPLine)>0]/AssociationReference[AssociationType="EntityTelephoneNumberAssociation"]/AssociationGUID])',
                      x839s18 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/FirstName)>0])',
                      x850s19 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/MiddleName)>0])',
                      x938s20 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/LastName)>0])',
                      x928s21 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/FullName)>0])',
                      x851s22 NUMBER PATH 'count(Person[Role="Subject" and string-length(Sex)>0])',
                      x863s23 NUMBER PATH 'count(Person[Role="Subject" and string-length(BirthDate)>0])',
                      x837s24 NUMBER PATH 'count(Person[Role="Subject" and string-length(Ethnicity)>0])',
                      x920s25 NUMBER PATH 'count(Person[Role="Subject" and string-length(EyeColor)>0])',
                      x888s26 NUMBER PATH 'count(Person[Role="Subject" and string-length(DriverLicenseID/ID)>0])',
                      x874s27 NUMBER PATH 'count(Person[Role="Subject" and string-length(TreatAsAdultIndicator)>0])',
                      x907s28 NUMBER PATH 'count(Person[Role="Subject" and string-length(FBINumber)>0])',
                      x909s29 NUMBER PATH 'count(Person[Role="Subject" and string-length(HairColor)>0])',
                      x894s30 NUMBER PATH 'count(Person[Role="Subject" and string-length(Height)>0])',
                      x838s31 NUMBER PATH 'count(Person[Role="Subject" and string-length(PassportID/ID)>0])',
                      x856s32 NUMBER PATH 'count(Person[Role="Subject" and string-length(StateFingerprintID/ID)>0])',
                      x906s33 NUMBER PATH 'count(Person[Role="Subject" and SSN])',
                      x939s34 NUMBER PATH 'count(Person[Role="Subject"]/Weight[PointValue or MaximumValue or MinimumValue]/..)',
                      x924s35 NUMBER PATH 'count(Person[(Role="Arrest Subject" or Role="Subject") and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber/AssociationReference/AssociationGUID]/AssociationReference/AssociationGUID])',
                      x887s36 NUMBER PATH 'count(Person[Role="Subject" and AssociationReference/AssociationType="PersonOrganizationAssociation" and AssociationReference/AssociationGUID=../Organization[string-length(Name)>0]/AssociationReference/AssociationGUID])',
                      x867s37 NUMBER PATH 'count(Person[Role="Subject" and AssociationReference/AssociationType="PersonOrganizationAssociation" and AssociationReference/AssociationGUID=../Organization[AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber/AssociationReference/AssociationGUID]/AssociationReference/AssociationGUID])',
                      x866s38 NUMBER PATH 'count(Person[Role="Subject" and string-length(SubjectMOAction)>0])',
                      x900s39 NUMBER PATH 'count(Incident[string-length(Date)>0])',
                      x886s40 NUMBER PATH 'count(Incident[string-length(StartDate)>0])',
                      x858s41 NUMBER PATH 'count(Incident[string-length(EndDate)>0])',
                      x918s42 NUMBER PATH 'count(Incident[AssociationReference/AssociationType="IncidentReportingOfficialAssociation" and AssociationReference/AssociationGUID=../Person/AssociationReference[AssociationType="IncidentReportingOfficialAssociation"]/AssociationGUID])',
                      x849s43 NUMBER PATH 'count(Incident/Narrative)',
                      x873s44 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/StreetPredirection)',
                      x848s45 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/StreetNumber)',
                      x864s46 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/StreetName)',
                      x908s47 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/StreetPostdirection)',
                      x893s48 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/FullStreetAddress)',
                      x872s49 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/FullAddress)',
                      x857s50 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/City)',
                      x880s51 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/State)',
                      x865s52 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/PostalCode)',
                      x836s53 NUMBER PATH 'count(Location[AssociationReference/AssociationType=../Incident/AssociationReference/AssociationType and AssociationReference/AssociationGUID=../Incident/AssociationReference/AssociationGUID]/Country)',
                      x885s54 NUMBER PATH 'count(Offense/Offense)',
                      x927s55 NUMBER PATH 'count(Offense/OffenseDescriptionText)',
                      x904s56 NUMBER PATH 'count(Offense/OtherContent[Info="offense was completed" or Info="offense was attempted"])',
                      x946s57 NUMBER PATH 'count(Offense/ForceCategory)',
                      x945s58 NUMBER PATH 'count(Offense/DomesticViolenceIndicator)',
                      x937s59 NUMBER PATH 'count(Warrant/WarrantDate)',
                      x902s60 NUMBER PATH 'count(Organization[AssociationReference/AssociationType="ActivityResponsibleOrganizationAssociation" and AssociationReference/AssociationGUID=../Activity[OtherContent/Info="Warrant"]/AssociationReference/AssociationGUID]/Name)',
                      x846s61 NUMBER PATH 'count(Organization[AssociationReference/AssociationType="ActivityInformationClearerOrganizationAssociation" and AssociationReference/AssociationGUID=../Activity[OtherContent/Info="Warrant"]/AssociationReference/AssociationGUID]/Name)',
                      x862s62 NUMBER PATH 'count(Warrant/Description)',
                      x861s63 NUMBER PATH 'count(Organization[AssociationReference/AssociationType="ActivityInvolvedOrganizationAssociation" and AssociationReference/AssociationGUID=../Activity[OtherContent/Info="Warrant"]/AssociationReference/AssociationGUID]/Name)',
                      x852s64 NUMBER PATH 'count(Person[AssociationReference/AssociationType="ActivityInvolvedPersonAssociation" and AssociationReference/AssociationGUID=../Activity[OtherContent/Info="Warrant"]/AssociationReference[AssociationType="ActivityInvolvedPersonAssociation"]/AssociationGUID])',
                      x734s65 NUMBER PATH 'count(EmailAddress[string-length(EmailAddress)>0])'
                        ) T7
               WHERE STATUS_ID=1
                 AND NAT_SOURCE_ID=0
                 AND OWNING_ORI='xxxxxxxxxx'
                 and n.id=met.nads_id and met.type_id=mtt.id
                 and mtt.name='ReportType' and met.val='Incident Report';
              The only reason I am using the hint is due to the performance gain. I should also explain that I have 120+ million records in the table which contains the XMLType column. As you can see from the sql above, my query filters on other various other columns (not xml columns) to get a set of data which I then gather aggregate data out of the xml instances from. My explain plan without the hint showed a lot of XPATH evaluations prior to filtering down to the subset of 120+ million records which obviously takes a lot longer (the subset in this case is ~130,000 records).

              I will try adjusting the java pool size, but I agree, it does not appear to be a fix.
              • 4. Re: XMLTable Query Causes VM Stack Overflow
                840690
                I have the explain plans but they are too long to post here (I keep getting message larger than allowable 30000 characters). Is there any way to post the explain plans here?
                • 5. Re: XMLTable Query Causes VM Stack Overflow
                  odie_63
                  Ah! I remember your post in the SQL forum now :)
                  My explain plan without the hint showed a lot of XPATH evaluations prior to filtering down to the subset of 120+ million records
                  If that's true, I think that would be a serious bug.
                  I have the explain plans but they are too long to post here
                  You can post the plan for a simplified query (only 3-4 XMLTable columns), that should be sufficient.

                  Edited by: odie_63 on 20 juil. 2012 14:17
                  • 6. Re: XMLTable Query Causes VM Stack Overflow
                    840690
                    Hi odie_63,
                    I've trimmed the xpath columns to the first 10 of the 65 columns. I dont believe it was me posting to the SQL forum as I've not posted this issue anywhere else. If you know of something similar please let me know and I'll check it out!

                    The explain plan for my query (trimmed to 10 xpaths) without the NO_XML_QUERY_REWRITE is as follows:
                    PLAN_TABLE_OUTPUT
                    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    Plan hash value: 1856330934
                    
                    ----------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                    ----------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                       |                         |     1 |   149 |   420K  (1)| 01:24:01 |       |       |
                    |   1 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |   2 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |*  3 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |*  4 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |   5 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |   6 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |*  7 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |*  8 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |   9 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  10 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 11 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 12 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  13 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  14 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 15 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 16 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  17 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  18 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 19 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 20 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  21 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  22 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 23 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 24 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  25 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  26 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 27 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 28 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  29 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  30 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 31 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 32 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  33 |  SORT AGGREGATE                        |                         |     1 |     2 |            |          |       |       |
                    |  34 |   COLLECTION ITERATOR PICKLER FETCH    | XQSEQUENCEFROMXMLTYPE   |  8168 | 16336 |    29   (0)| 00:00:01 |       |       |
                    |  35 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                    |  36 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                    |* 37 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |* 38 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    |  39 |  SORT AGGREGATE                        |                         |     1 |   149 |            |          |       |       |
                    |  40 |   NESTED LOOPS                         |                         |   115M|    16G|   420K  (1)| 01:24:01 |       |       |
                    |  41 |    NESTED LOOPS                        |                         | 14135 |  2029K| 35980   (1)| 00:07:12 |       |       |
                    |  42 |     NESTED LOOPS                       |                         | 14135 |  1697K|  7700   (1)| 00:01:33 |       |       |
                    |  43 |      TABLE ACCESS BY INDEX ROWID       | META_TYPES              |     1 |    14 |     1   (0)| 00:00:01 |       |       |
                    |* 44 |       INDEX UNIQUE SCAN                | MTT_NAME_UK             |     1 |       |     0   (0)| 00:00:01 |       |       |
                    |* 45 |      TABLE ACCESS BY GLOBAL INDEX ROWID| NADS                    | 14135 |  1504K|  7699   (1)| 00:01:33 | ROWID | ROWID |
                    |* 46 |       INDEX RANGE SCAN                 | NADS_OWNING_ORI_2_IDX   | 14137 |       |    52   (0)| 00:00:01 |       |       |
                    |* 47 |     TABLE ACCESS BY INDEX ROWID        | METAS                   |     1 |    24 |     2   (0)| 00:00:01 |       |       |
                    |* 48 |      INDEX UNIQUE SCAN                 | MET2_NADS_ID_TYPE_ID_UK |     1 |       |     1   (0)| 00:00:01 |       |       |
                    |  49 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                    ----------------------------------------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       3 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P17"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P17"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                       4 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P18"."C_01$",1,50),50,1,0)='Enforcement Official')
                       7 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P15"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P15"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                       8 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P16"."C_01$",1,50),50,1,0)='Enforcement Official')
                      11 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P13"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P13"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      12 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P14"."C_01$",1,50),50,1,0)='Enforcement Official')
                      15 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P11"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P11"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      16 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P12"."C_01$",1,50),50,1,0)='Enforcement Official')
                      19 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P9"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P9"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      20 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P10"."C_01$",1,50),50,1,0)='Witness')
                      23 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P7"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P7"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      24 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P8"."C_01$",1,50),50,1,0)='Witness')
                      27 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P5"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P5"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      28 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P6"."C_01$",1,50),50,1,0)='Witness')
                      31 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P3"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P3"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      32 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P4"."C_01$",1,50),50,1,0)='Witness')
                      37 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P1"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                  LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P1"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                      38 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P2"."C_01$",1,50),50,1,0)='Victim')
                      44 - access("MTT"."NAME"='ReportType')
                      45 - filter("STATUS_ID"=1 AND "NAT_SOURCE_ID"=0)
                      46 - access("OWNING_ORI"='VA0470000')
                      47 - filter("MET"."VAL"='Incident Report')
                      48 - access("N"."ID"="MET"."NADS_ID" AND "MET"."TYPE_ID"="MTT"."ID")
                    
                    92 rows selected.
                    The explain plan for my query (trimmed to 10 xpaths) with the NO_XML_QUERY_REWRITE is as follows:
                    PLAN_TABLE_OUTPUT
                    ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                    Plan hash value: 815899154
                    
                    ----------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation                              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                    ----------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT                       |                         |     1 |   149 |   420K  (1)| 01:24:01 |       |       |
                    |   1 |  SORT AGGREGATE                        |                         |     1 |   149 |            |          |       |       |
                    |   2 |   NESTED LOOPS                         |                         |   115M|    16G|   420K  (1)| 01:24:01 |       |       |
                    |   3 |    NESTED LOOPS                        |                         | 14135 |  2029K| 35980   (1)| 00:07:12 |       |       |
                    |   4 |     NESTED LOOPS                       |                         | 14135 |  1697K|  7700   (1)| 00:01:33 |       |       |
                    |   5 |      TABLE ACCESS BY INDEX ROWID       | META_TYPES              |     1 |    14 |     1   (0)| 00:00:01 |       |       |
                    |*  6 |       INDEX UNIQUE SCAN                | MTT_NAME_UK             |     1 |       |     0   (0)| 00:00:01 |       |       |
                    |*  7 |      TABLE ACCESS BY GLOBAL INDEX ROWID| NADS                    | 14135 |  1504K|  7699   (1)| 00:01:33 | ROWID | ROWID |
                    |*  8 |       INDEX RANGE SCAN                 | NADS_OWNING_ORI_2_IDX   | 14137 |       |    52   (0)| 00:00:01 |       |       |
                    |*  9 |     TABLE ACCESS BY INDEX ROWID        | METAS                   |     1 |    24 |     2   (0)| 00:00:01 |       |       |
                    |* 10 |      INDEX UNIQUE SCAN                 | MET2_NADS_ID_TYPE_ID_UK |     1 |       |     1   (0)| 00:00:01 |       |       |
                    |  11 |    COLLECTION ITERATOR PICKLER FETCH   | XQSEQUENCEFROMXMLTYPE   |  8168 | 16336 |    27   (0)| 00:00:01 |       |       |
                    ----------------------------------------------------------------------------------------------------------------------------------
                    
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    
                       6 - access("MTT"."NAME"='ReportType')
                       7 - filter("STATUS_ID"=1 AND "NAT_SOURCE_ID"=0)
                       8 - access("OWNING_ORI"='VA0470000')
                       9 - filter("MET"."VAL"='Incident Report')
                      10 - access("N"."ID"="MET"."NADS_ID" AND "MET"."TYPE_ID"="MTT"."ID")
                    
                    27 rows selected.
                    • 7. Re: XMLTable Query Causes VM Stack Overflow
                      odie_63
                      I dont believe it was me posting to the SQL forum as I've not posted this issue anywhere else.
                      Not about this specific issue, but about the same query : {thread:id=2386264}
                      • 8. Re: XMLTable Query Causes VM Stack Overflow
                        Marco Gralike
                        To get a fast, probably maybe even relational response, you will have to switch the underlying XMLType storage option

                        - preferably in XMLType object relational storage, if you have a fixed XML Schema
                        - XMLType Securefile storage WITH structured and unstructured XMLindexes in place.

                        If you would do it via XMLType OR than also annotate the Schema before registering if not only for support of all the dates mentioned in your query.
                        • 9. Re: XMLTable Query Causes VM Stack Overflow
                          odie_63
                          - XMLType Securefile storage WITH structured and unstructured XMLindexes in place.
                          Marco,

                          I'm not convinced XMLIndexes would be better in this situation.
                          It's worth a try of course, but looking at the explain plan we can see that basically the query is resolved like this :
                          SELECT sum(
                                   ( 
                                     select count( <column name> )
                                     from <xpathtable>
                                     where exists (
                                       select null
                                       from <xpathtable>
                                       where <correlation preds>
                                     )
                                   )
                                 )
                               , sum( ... )
                               , sum( ... )
                               , ...
                          FROM <base tables>
                          WHERE <join and filter preds>
                          If we use an unstructured XMLIndex, there will be a lot of accesses to the PATH TABLE instead (given the diversity of xpath predicates used), all of them being done for each row of the filtered result set, in subqueries. That could possibly be worst.

                          ... but still a wild guess right now, it has to be tested.


                          For the SXI, what structure would you suggest?
                          • 10. Re: XMLTable Query Causes VM Stack Overflow
                            Marco Gralike
                            We would need a test case.....
                            • 11. Re: XMLTable Query Causes VM Stack Overflow
                              840690
                              I have now tried bumping the Java pool up to 1G and get the same results (VM Stack Overflow).

                              There are no XMLIndexes on the column/table in question at the current time. I can look into moving to XMLType OR in the future but currently speed is not the issue, the Stack overflow is. This query has worked for me in the past for at least 6 months and I cannot pin the issue down to anything changing in the system (not to say that it didn't but if it did it was unknown to me).

                              I have a few follow on observations / questions. I have the following trimmed versions of the query in question:
                              * query with no xmltable
                              ** runs with expected response in: 00:01:16.35
                              * query with xmltable, only one column
                              ** runs with expected response in: 00:17:21.85
                              * query with xmltable, only one column and no_xml_query_rewrite
                              ** does not run, throws the following error:
                              SELECT /* no_xml_query_rewrite */+
                              *+
                              ERROR at line 1:
                              ORA-19112: error raised during evaluation:
                              XVM-00004: internal error "invalid address"

                              I've ran an explain for each of the above. Can anyone tell me why the addition of the xmltable is causing the 115M rows addition to the plan?

                              Query with No XMLTable
                              SQL> explain plan for
                                2  SELECT
                                      COUNT(*) as TOTAL_RECORDS
                                3    4   FROM NADS n,METAS met, META_TYPES mtt
                                5   WHERE STATUS_ID=1
                                6     AND NAT_SOURCE_ID=0
                                7     AND OWNING_ORI='VA0470000'
                                8     and n.id=met.nads_id and met.type_id=mtt.id
                                9     and mtt.name='ReportType' and met.val='Incident Report';
                              
                              Explained.
                              
                              Elapsed: 00:00:00.01
                              SQL> @expd
                              
                              PLAN_TABLE_OUTPUT
                              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                              Plan hash value: 3855160303
                              
                              -----------------------------------------------------------------------------------------------------------
                              | Id  | Operation                       | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
                              -----------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT                |                         |     1 |    60 | 28356   (1)| 00:05:41 |
                              |   1 |  SORT AGGREGATE                 |                         |     1 |    60 |            |          |
                              |   2 |   NESTED LOOPS                  |                         |       |       |            |          |
                              |   3 |    NESTED LOOPS                 |                         | 14135 |   828K| 28356   (1)| 00:05:41 |
                              |   4 |     NESTED LOOPS                |                         | 14135 |   496K|    75   (0)| 00:00:01 |
                              |   5 |      TABLE ACCESS BY INDEX ROWID| META_TYPES              |     1 |    14 |     1   (0)| 00:00:01 |
                              |*  6 |       INDEX UNIQUE SCAN         | MTT_NAME_UK             |     1 |       |     0   (0)| 00:00:01 |
                              |*  7 |      INDEX RANGE SCAN           | N_SNOI_IDX              | 14135 |   303K|    74   (0)| 00:00:01 |
                              |*  8 |     INDEX UNIQUE SCAN           | MET2_NADS_ID_TYPE_ID_UK |     1 |       |     1   (0)| 00:00:01 |
                              |*  9 |    TABLE ACCESS BY INDEX ROWID  | METAS                   |     1 |    24 |     2   (0)| 00:00:01 |
                              -----------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 6 - access("MTT"."NAME"='ReportType')
                                 7 - access("STATUS_ID"=1 AND "NAT_SOURCE_ID"=0 AND "OWNING_ORI"='VA0470000')
                                 8 - access("N"."ID"="MET"."NADS_ID" AND "MET"."TYPE_ID"="MTT"."ID")
                                 9 - filter("MET"."VAL"='Incident Report')
                              Query with XMLTable
                              SQL> explain plan for
                                2  SELECT
                                3          COUNT(*) as TOTAL_RECORDS,
                                4             SUM(T7.x903s1) AS OFCRFirstName
                                5   FROM NADS n,METAS met, META_TYPES mtt
                                6     ,XMLTable(XMLNAMESPACES('http://fbi.gov/cjis/N-DEx' as "p1"),
                                7          '/p1:DataItem' PASSING n.DATA_INTERNAL
                                8             COLUMNS
                                9                     x903s1 NUMBER PATH 'count(Person[Role="Enforcement Official" and string-length(Name/FirstName)>0])'
                               10             ) T7
                               11   WHERE STATUS_ID=1
                               12     AND NAT_SOURCE_ID=0
                               13     AND OWNING_ORI='VA0470000'
                               14     and n.id=met.nads_id and met.type_id=mtt.id
                               15     and mtt.name='ReportType' and met.val='Incident Report';
                              
                              Explained.
                              
                              Elapsed: 00:00:00.05
                              SQL> @expd
                              
                              PLAN_TABLE_OUTPUT
                              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                              Plan hash value: 2884540182
                              
                              ----------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                              ----------------------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT                       |                         |     1 |   149 |   420K  (1)| 01:24:01 |       |       |
                              |   1 |  SORT AGGREGATE                        |                         |     1 |     6 |            |          |       |       |
                              |   2 |   NESTED LOOPS SEMI                    |                         | 33358 |   195K| 11239   (2)| 00:02:15 |       |       |
                              |*  3 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                              |*  4 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                              |   5 |  SORT AGGREGATE                        |                         |     1 |   149 |            |          |       |       |
                              |   6 |   NESTED LOOPS                         |                         |   115M|    16G|   420K  (1)| 01:24:01 |       |       |
                              |   7 |    NESTED LOOPS                        |                         | 14135 |  2029K| 35980   (1)| 00:07:12 |       |       |
                              |   8 |     NESTED LOOPS                       |                         | 14135 |  1697K|  7700   (1)| 00:01:33 |       |       |
                              |   9 |      TABLE ACCESS BY INDEX ROWID       | META_TYPES              |     1 |    14 |     1   (0)| 00:00:01 |       |       |
                              |* 10 |       INDEX UNIQUE SCAN                | MTT_NAME_UK             |     1 |       |     0   (0)| 00:00:01 |       |       |
                              |* 11 |      TABLE ACCESS BY GLOBAL INDEX ROWID| NADS                    | 14135 |  1504K|  7699   (1)| 00:01:33 | ROWID | ROWID |
                              |* 12 |       INDEX RANGE SCAN                 | NADS_OWNING_ORI_2_IDX   | 14137 |       |    52   (0)| 00:00:01 |       |       |
                              |* 13 |     TABLE ACCESS BY INDEX ROWID        | METAS                   |     1 |    24 |     2   (0)| 00:00:01 |       |       |
                              |* 14 |      INDEX UNIQUE SCAN                 | MET2_NADS_ID_TYPE_ID_UK |     1 |       |     1   (0)| 00:00:01 |       |       |
                              |  15 |    XPATH EVALUATION                    |                         |       |       |            |          |       |       |
                              ----------------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 3 - filter(CASE  WHEN LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P1"."C_01$"),1,50),50,1,0)) IS NOT NULL THEN
                                            LENGTH(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL(SYS_XQEXVAL("P1"."C_01$"),1,50),50,1,0)) ELSE 0 END >0)
                                 4 - filter(SYS_XQ_UPKXML2SQL(SYS_XQEXVAL("P2"."C_01$",1,50),50,1,0)='Enforcement Official')
                                10 - access("MTT"."NAME"='ReportType')
                                11 - filter("STATUS_ID"=1 AND "NAT_SOURCE_ID"=0)
                                12 - access("OWNING_ORI"='VA0470000')
                                13 - filter("MET"."VAL"='Incident Report')
                                14 - access("N"."ID"="MET"."NADS_ID" AND "MET"."TYPE_ID"="MTT"."ID")
                              Query with XMLTable and no_xml_query_rewrite Hint
                              SQL> explain plan for
                                2  SELECT /*+ no_xml_query_rewrite */
                                3          COUNT(*) as TOTAL_RECORDS,
                                4             SUM(T7.x903s1) AS OFCRFirstName
                                5   FROM NADS n,METAS met, META_TYPES mtt
                                6     ,XMLTable(XMLNAMESPACES('http://fbi.gov/cjis/N-DEx' as "p1"),
                                7          '/p1:DataItem' PASSING n.DATA_INTERNAL
                                8             COLUMNS
                                9                     x903s1 NUMBER PATH 'count(Person[Role="Enforcement Official" and string-length(Name/FirstName)>0])'
                               10             ) T7
                               11   WHERE STATUS_ID=1
                               12     AND NAT_SOURCE_ID=0
                               13     AND OWNING_ORI='VA0470000'
                               14     and n.id=met.nads_id and met.type_id=mtt.id
                               15     and mtt.name='ReportType' and met.val='Incident Report';
                              
                              Explained.
                              
                              Elapsed: 00:00:00.02
                              SQL> @expd
                              
                              PLAN_TABLE_OUTPUT
                              ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
                              Plan hash value: 815899154
                              
                              ----------------------------------------------------------------------------------------------------------------------------------
                              | Id  | Operation                              | Name                    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
                              ----------------------------------------------------------------------------------------------------------------------------------
                              |   0 | SELECT STATEMENT                       |                         |     1 |   149 |   420K  (1)| 01:24:01 |       |       |
                              |   1 |  SORT AGGREGATE                        |                         |     1 |   149 |            |          |       |       |
                              |   2 |   NESTED LOOPS                         |                         |   115M|    16G|   420K  (1)| 01:24:01 |       |       |
                              |   3 |    NESTED LOOPS                        |                         | 14135 |  2029K| 35980   (1)| 00:07:12 |       |       |
                              |   4 |     NESTED LOOPS                       |                         | 14135 |  1697K|  7700   (1)| 00:01:33 |       |       |
                              |   5 |      TABLE ACCESS BY INDEX ROWID       | META_TYPES              |     1 |    14 |     1   (0)| 00:00:01 |       |       |
                              |*  6 |       INDEX UNIQUE SCAN                | MTT_NAME_UK             |     1 |       |     0   (0)| 00:00:01 |       |       |
                              |*  7 |      TABLE ACCESS BY GLOBAL INDEX ROWID| NADS                    | 14135 |  1504K|  7699   (1)| 00:01:33 | ROWID | ROWID |
                              |*  8 |       INDEX RANGE SCAN                 | NADS_OWNING_ORI_2_IDX   | 14137 |       |    52   (0)| 00:00:01 |       |       |
                              |*  9 |     TABLE ACCESS BY INDEX ROWID        | METAS                   |     1 |    24 |     2   (0)| 00:00:01 |       |       |
                              |* 10 |      INDEX UNIQUE SCAN                 | MET2_NADS_ID_TYPE_ID_UK |     1 |       |     1   (0)| 00:00:01 |       |       |
                              |  11 |    COLLECTION ITERATOR PICKLER FETCH   | XQSEQUENCEFROMXMLTYPE   |  8168 | 16336 |    27   (0)| 00:00:01 |       |       |
                              ----------------------------------------------------------------------------------------------------------------------------------
                              
                              Predicate Information (identified by operation id):
                              ---------------------------------------------------
                              
                                 6 - access("MTT"."NAME"='ReportType')
                                 7 - filter("STATUS_ID"=1 AND "NAT_SOURCE_ID"=0)
                                 8 - access("OWNING_ORI"='VA0470000')
                                 9 - filter("MET"."VAL"='Incident Report')
                                10 - access("N"."ID"="MET"."NADS_ID" AND "MET"."TYPE_ID"="MTT"."ID")
                              • 12. Re: XMLTable Query Causes VM Stack Overflow
                                odie_63
                                I can look into moving to XMLType OR in the future but currently speed is not the issue, the Stack overflow is.
                                OR is not just about speed, but also storage and query optimization for a data-centric approach.
                                XML queries are rewritten to use the underlying relational structure, there would be no XVM at all in the picture, just plain SQL and Oracle CBO in action.
                                Memory issues (if any) would therefore be much more easier to solve.

                                Can anyone tell me why the addition of the xmltable is causing the 115M rows addition to the plan?
                                That's because Oracle doesn't know the cardinality of the data set returned by XMLTable, so it uses a default one based on the db block size.
                                In your case, I guess it's 8k, and a default card. of 8168 :

                                14135 * 8168 = 115,454,680

                                (of course that doesn't mean 115M rows will be retrieved in reality)
                                • 13. Re: XMLTable Query Causes VM Stack Overflow
                                  Marco Gralike
                                  There are no XMLIndexes on the column/table in question at the current time. I can look into moving to XMLType OR in the future but currently speed is not the issue, the Stack overflow is. This query has worked for me in the past for at least 6 months and I cannot pin the issue down to anything changing in the system (not to say that it didn't but if it did it was unknown to me).
                                  OR is not only about speed NOR is the binary XML approach combined with XMLIndexes, its all about making live easier for the parser/CBO at hand by supplying the info needed the create the optimum plan based on as small as possible result sets.

                                  If you have the room, just build for fun a full blown xmindex (that is an unstructured one) and see what happens (for example on the current XMLTYPE column or CLOB column)

                                  DESPITE THAT GIVE US A TEST CASE

                                  Edited by: Marco Gralike on Jul 24, 2012 4:28 PM
                                  • 14. Re: XMLTable Query Causes VM Stack Overflow
                                    Marco Gralike
                                    Totally agree, I would need a proper test case to figure out how what and where, but you could break up most of the needed data from XML in smaller content table column based smaller pieces by driving the most optimum access path. I have no idea what is smarter to drive it first via a full scan across the XML data and then do the RELATIONAL counts,sum and relational bits (<> the XPATH counts, "strings are not empty checks" etc) or filter out most the relational bit first and then get the remaining XML data pieces and do the relational counts/sums...

                                    I mean if you see
                                            x871s5 NUMBER PATH 'count(Person[Role="Witness" and string-length(Name/FirstName)>0])',
                                            x860s6 NUMBER PATH 'count(Person[Role="Witness" and string-length(Name/MiddleName)>0])',
                                            x859s7 NUMBER PATH 'count(Person[Role="Witness" and string-length(Name/LastName)>0])',
                                            x944s8 NUMBER PATH 'count(Person[Role="Witness" and string-length(***)>0])',
                                            x844s9 NUMBER PATH 'count(Person[Role="Witness" and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber/AssociationReference/AssociationGUID]/AssociationReference/AssociationGUID])',
                                            x921s10 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/FirstName)>0])',
                                            x940s11 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/MiddleName)>0])',
                                            x881s12 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/LastName)>0])',
                                            x899s13 NUMBER PATH 'count(Person[Role="Victim" and string-length(Name/FullName)>0])',
                                            x932s14 NUMBER PATH 'count(Person[Role="Victim" and string-length(***)>0])',
                                            x843s15 NUMBER PATH 'count(Person[Role="Victim" and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber/AssociationReference/AssociationGUID]/AssociationReference/AssociationGUID])',
                                            x912s16 NUMBER PATH 'count(Organization[Role="Victim" and string-length(Name)>0])',
                                            x913s17 NUMBER PATH 'count(Organization[Role="Victim" and AssociationReference/AssociationType="EntityTelephoneNumberAssociation" and AssociationReference/AssociationGUID=../TelephoneNumber[string-length(FullNumber)>0 or string-length(InternationalNumber)>0 or string-length(NANPLine)>0]/AssociationReference[AssociationType="EntityTelephoneNumberAssociation"]/AssociationGUID])',
                                            x839s18 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/FirstName)>0])',
                                            x850s19 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/MiddleName)>0])',
                                            x938s20 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/LastName)>0])',
                                            x928s21 NUMBER PATH 'count(Person[Role="Subject" and string-length(Name/FullName)>0])',
                                            x851s22 NUMBER PATH 'count(Person[Role="Subject" and string-length(***)>0])',
                                            x863s23 NUMBER PATH 'count(Person[Role="Subject" and string-length(BirthDate)>0])',
                                            x837s24 NUMBER PATH 'count(Person[Role="Subject" and string-length(Ethnicity)>0])',
                                            x920s25 NUMBER PATH 'count(Person[Role="Subject" and string-length(EyeColor)>0])',
                                            x888s26 NUMBER PATH 'count(Person[Role="Subject" and string-length(DriverLicenseID/ID)>0])',
                                            x874s27 NUMBER PATH 'count(Person[Role="Subject" and string-length(TreatAsAdultIndicator)>0])',
                                            x907s28 NUMBER PATH 'count(Person[Role="Subject" and string-length(FBINumber)>0])',
                                            x909s29 NUMBER PATH 'count(Person[Role="Subject" and string-length(HairColor)>0])',
                                            x894s30 NUMBER PATH 'count(Person[Role="Subject" and string-length(Height)>0])',
                                            x838s31 NUMBER PATH 'count(Person[Role="Subject" and string-length(PassportID/ID)>0])',
                                            x856s32 NUMBER PATH 'count(Person[Role="Subject" and string-length(StateFingerprintID/ID)>0])'
                                    Then Role, Lastname, Fullname etc, etc, etc, are likely candidates for structured XMLIndex approach or...?

                                    also I don't like the "../" bit (not Oracle's strongest point - afaik - at least up to 11.2.0.1)
                                    ../TelephoneNumber[AssociationReferenc
                                    Also I am guessing that string comparison is way more efficiently done by the CBO than by the XQuery XML parser.

                                    Edited by: Marco Gralike on Jul 24, 2012 4:33 PM
                                    1 2 Previous Next