This discussion is archived
1 2 Previous Next 17 Replies Latest reply: Jul 19, 2013 5:27 AM by Pollocks01 Go to original post RSS
  • 15. Re: XMLTable Query Causes VM Stack Overflow
    Pollocks01 Newbie
    Currently Being Moderated

    It's the Usual Suspects here again! Hi guys!

     

    I have a full blown XMLIndex and today I was troubleshooting a performance issue. I'd love to put together a test case for this....let's see.

     

    Anyways, my query was performing poorly. It was hitting a particular element which repeated many times in the xml. The explain plan was lots of hits to the path table and was taking ages to return. I threw in a NO_XMLINDEX_REWRITE hint and observed the XPATH EVALUATION (streaming binary XML) explain plan step. On executing the query with this hint the query returned the first 500 row page "instantaneously" within TOAD. When I paged through the records in TOAD (fetch size was 500 rows), I observed the session's memory growing upon each page. I then encountered:

     

    ORA-04030: out of process memory when trying to allocate 55600 bytes (kxs-heap-w,qmemNextBuf:Large Alloc)

     

    I have an SR open with Oracle right now for a similar issue - you already pitched in on that Marco on another post (thread 1124322).

     

    Without the hint, the Out of memory error didn't happen.

  • 16. Re: XMLTable Query Causes VM Stack Overflow
    Pollocks01 Newbie
    Currently Being Moderated

    My xpath in question had a wildcard:

     

    for $i in /PIPEDocument/PIPTransaction/HistoricalUsageAccept/ServiceInformation/custom:*

     

    When I changed this to be a specific node then not only did the XMLIndex based approach returned the resultset quickly but the XPATH EVALUATION approach also did not result in Out of Memory.

     

    So, the tip here is to NOT use wildcards in xpath expressions.

     

    Marco - it was one of your comments which prompted me to check this out - thanks!

     

    -P

  • 17. Re: XMLTable Query Causes VM Stack Overflow
    odie_63 Guru
    Currently Being Moderated

    So, the tip here is to NOT use wildcards in xpath expressions.

     

    Of course, we have to balance that statement with the usual "it depends".

     

    If the wildcard is used on purpose because one does want to access the sequence of child::*, there's no other choice.

    If the wildcard is used by pure laziness instead of a known, single, item name, one does it at his own risk

     

    (Thanks for the feedback BTW)

1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points