FAQ
First, disregard the part about the large TEMP tablespace usage. I think that was coming from the query _before_ I rewrote it to use WITH statements. I'm not seeing the large TEMP usage now.

For each number below, I've included the dbms_xplan information, the row source operations, and the timings.

#1 The original query using WITH statements and UNION
http://pastebin.com/embed_iframe.php?i=0Ht0V4T3
Time: 18 minutes, 38 secs (89,200 rows)

#2 The original query CHANGED from UNION to UNION ALL (only change):
http://pastebin.com/embed_iframe.php?i=JybML3y8
Time: 1 minute, 03 secs (90,227 rows)

#3 The query from #2 CHANGED to include the ROW_NUMBER function to give us the same results as #1:
http://pastebin.com/embed_iframe.php?i=75QJ2ShD
Time: 50 secs (89,200 rows)

Chris Taylor
Sr. Oracle DBA
Ingram Barge Company
Nashville, TN 37205
Office: 615-517-3355
Cell: 615-663-1673
Email: chris.taylor_at_ingrambarge.com

CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

Search Discussions

  • Greg Rahn at Aug 12, 2011 at 7:18 pm
    Let's use this as a training exercise...

    Why is #1 so horrible in performance?

    Hint: look at this: http://pastebin.com/frUSVFKA

    On Fri, Aug 12, 2011 at 11:32 AM, Taylor, Chris David <
    ChrisDavid.Taylor_at_ingrambarge.com> wrote:
    First, disregard the part about the large TEMP tablespace usage. I think
    that was coming from the query _*before*_ I rewrote it to use WITH
    statements. I�m not seeing the large TEMP usage now.****

    ** **

    For each number below, I�ve included the dbms_xplan information, the row
    source operations, and the timings.****

    ** **

    #1 The original query using WITH statements and UNION****

    http://pastebin.com/embed_iframe.php?i=0Ht0V4T3****

    Time: 18 minutes, 38 secs (89,200 rows)****

    ** **

    #2 The original query CHANGED from UNION to UNION ALL (only change):****

    http://pastebin.com/embed_iframe.php?i=JybML3y8****

    Time: 1 minute, 03 secs (90,227 rows)****

    ** **

    #3 The query from #2 CHANGED to include the ROW_NUMBER function to give us
    the same results as #1:****

    http://pastebin.com/embed_iframe.php?i=75QJ2ShD****

    Time: 50 secs (89,200 rows)****


    **
    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Aug 12, 2011 at 7:32 pm
    Part 2:

    Now can you tell why #3 is much faster?

    Two hints:
    1) it has nothing directly to do with the UNION vs window function, etc.
    2) see http://pastebin.com/AUicQgiX
    On Fri, Aug 12, 2011 at 12:18 PM, Greg Rahn wrote:

    Let's use this as a training exercise...

    Why is #1 so horrible in performance?

    Hint: look at this: http://pastebin.com/frUSVFKA


    On Fri, Aug 12, 2011 at 11:32 AM, Taylor, Chris David <
    ChrisDavid.Taylor_at_ingrambarge.com> wrote:
    First, disregard the part about the large TEMP tablespace usage. I think
    that was coming from the query _*before*_ I rewrote it to use WITH
    statements. I�m not seeing the large TEMP usage now.****

    ** **

    For each number below, I�ve included the dbms_xplan information, the row
    source operations, and the timings.****

    ** **

    #1 The original query using WITH statements and UNION****

    http://pastebin.com/embed_iframe.php?i=0Ht0V4T3****

    Time: 18 minutes, 38 secs (89,200 rows)****

    ** **

    #2 The original query CHANGED from UNION to UNION ALL (only change):****

    http://pastebin.com/embed_iframe.php?i=JybML3y8****

    Time: 1 minute, 03 secs (90,227 rows)****

    ** **

    #3 The query from #2 CHANGED to include the ROW_NUMBER function to give us
    the same results as #1:****

    http://pastebin.com/embed_iframe.php?i=75QJ2ShD****

    Time: 50 secs (89,200 rows)****


    **
    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Jeremy Schneider at Aug 12, 2011 at 7:49 pm
    Aaaah, it's clear now. This has nothing to do with an optimization between
    UNION vs ROW_NUMBER -- it's that your plan completely changed when you
    switched the syntax -- most importantly the table join order. If you use
    the same join order with the UNION then you should see similar performance.

    It seems that there are two table join orders with nearly identical cost.
    Something in the costing of the UNION statement causes the optimizer to pick
    the unlucky order, whereas with the UNION ALL it picks the more lucky one.

    -Jeremy

    On Fri, Aug 12, 2011 at 1:32 PM, Taylor, Chris David <
    ChrisDavid.Taylor_at_ingrambarge.com> wrote:
    First, disregard the part about the large TEMP tablespace usage. I think
    that was coming from the query _*before*_ I rewrote it to use WITH
    statements. I�m not seeing the large TEMP usage now.****

    ** **

    For each number below, I�ve included the dbms_xplan information, the row
    source operations, and the timings.****

    ** **

    #1 The original query using WITH statements and UNION****

    http://pastebin.com/embed_iframe.php?i=0Ht0V4T3****

    Time: 18 minutes, 38 secs (89,200 rows)****

    ** **

    #2 The original query CHANGED from UNION to UNION ALL (only change):****

    http://pastebin.com/embed_iframe.php?i=JybML3y8****

    Time: 1 minute, 03 secs (90,227 rows)****

    ** **

    #3 The query from #2 CHANGED to include the ROW_NUMBER function to give us
    the same results as #1:****

    http://pastebin.com/embed_iframe.php?i=75QJ2ShD****

    Time: 50 secs (89,200 rows)****

    ** **

    ** **

    ** **

    *Chris Taylor*****

    *Sr. Oracle DBA*****

    Ingram Barge Company****

    Nashville, TN 37205****

    Office: 615-517-3355****

    Cell: 615-663-1673****

    Email: chris.taylor_at_ingrambarge.com****

    ****

    *CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
    and may also be privileged. If you are not the named recipient, please
    notify the sender immediately and delete the contents of this message
    without disclosing the contents to anyone, using them for any purpose, or
    storing or copying the information on any medium.*****

    ** **
    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago

    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Aug 12, 2011 at 8:21 pm
    So it is interesting to me then that Oracle would pick a better plan for UNION-ALL vs UNION in that case with the same information available to the optimizer at the time each query is executed.

    In that regard then, UNION would seem to be a more expensive operation REGARDLESS of the additional SORT operation that must occur.

    (Theory) Following that, IF UNION-ALL is always less expensive than UNION (before the additional SORT operation) then the WINDOW function applied (depending on overhead) will OFTEN come out cheaper than a UNION.

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-663-1673
    Email: chris.taylor_at_ingrambarge.com

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

    From: Jeremy Schneider
    Sent: Friday, August 12, 2011 2:49 PM
    To: Taylor, Chris David
    Cc: Greg Rahn; Wolfgang Breitling (breitliw_at_centrexcc.com); mwf_at_rsiz.com; oracle-l_at_freelists.org
    Subject: Re: UNION ALL with ROW_NUMBER vs UNION (WAS: "Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??")

    Aaaah, it's clear now. This has nothing to do with an optimization between UNION vs ROW_NUMBER -- it's that your plan completely changed when you switched the syntax -- most importantly the table join order. If you use the same join order with the UNION then you should see similar performance.

    It seems that there are two table join orders with nearly identical cost. Something in the costing of the UNION statement causes the optimizer to pick the unlucky order, whereas with the UNION ALL it picks the more lucky one.

    -Jeremy

    On Fri, Aug 12, 2011 at 1:32 PM, Taylor, Chris David > wrote:
    First, disregard the part about the large TEMP tablespace usage. I think that was coming from the query _before_ I rewrote it to use WITH statements. I'm not seeing the large TEMP usage now.

    For each number below, I've included the dbms_xplan information, the row source operations, and the timings.

    #1 The original query using WITH statements and UNION
    http://pastebin.com/embed_iframe.php?i=0Ht0V4T3
    Time: 18 minutes, 38 secs (89,200 rows)

    #2 The original query CHANGED from UNION to UNION ALL (only change):
    http://pastebin.com/embed_iframe.php?i=JybML3y8
    Time: 1 minute, 03 secs (90,227 rows)

    #3 The query from #2 CHANGED to include the ROW_NUMBER function to give us the same results as #1:
    http://pastebin.com/embed_iframe.php?i=75QJ2ShD
    Time: 50 secs (89,200 rows)

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-663-1673
    Email: chris.taylor_at_ingrambarge.com

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.
  • Jeremy Schneider at Aug 12, 2011 at 8:38 pm
    I don't know exactly how oracle would cost a UNION versus how oracle would
    cost a UNION-ALL plus a WINDOW. But it probably depends a bit on some
    environmental factors including row-source cardinality, work area size,
    estimated cardinality of sort columns, etc... But it seems immaterial which
    comes out cheaper from the CBO - you just have to decide which way to code
    it then help the optimizer get the best plan with your code. Personally I'd
    go with the union and fix the optimizing of that plan, which is the simpler
    and more readable to do the same thing. Then, a little digging to see where
    Oracle went wrong in it's guessing. Calling
    display_plan(null,null,'ALLSTATS LAST') right after an execution can help
    with this since it'll give you a side-by-side comparison of estimated to
    actual rowcounts. Ideally the optimizer should be getting the same plan for
    both ways of writing the query.

    -J

    On Fri, Aug 12, 2011 at 3:21 PM, Taylor, Chris David <
    ChrisDavid.Taylor_at_ingrambarge.com> wrote:
    So it is interesting to me then that Oracle would pick a better plan for
    UNION-ALL vs UNION in that case with the same information available to the
    optimizer at the time each query is executed.****

    ** **

    In that regard then, UNION would seem to be a more expensive operation
    REGARDLESS of the additional SORT operation that must occur.****

    ** **

    (Theory) Following that, IF UNION-ALL is always less expensive than UNION
    (before the additional SORT operation) then the WINDOW function applied
    (depending on overhead) will OFTEN come out cheaper than a UNION. ****

    ** **

    ** **

    *Chris Taylor*****

    *Sr. Oracle DBA*****

    Ingram Barge Company****

    Nashville, TN 37205****

    Office: 615-517-3355****

    Cell: 615-663-1673****

    Email: chris.taylor_at_ingrambarge.com****

    ****

    *CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
    and may also be privileged. If you are not the named recipient, please
    notify the sender immediately and delete the contents of this message
    without disclosing the contents to anyone, using them for any purpose, or
    storing or copying the information on any medium.*****

    ** **

    *From:* Jeremy Schneider
    *Sent:* Friday, August 12, 2011 2:49 PM
    *To:* Taylor, Chris David
    *Cc:* Greg Rahn; Wolfgang Breitling (breitliw_at_centrexcc.com); mwf_at_rsiz.com;
    oracle-l@freelists.org
    *Subject:* Re: UNION ALL with ROW_NUMBER vs UNION (WAS: "Wow - has anyone
    used ROW_NUMBER() to get around using a UNION statement when UNION ALL
    doesn't work??")****

    ** **

    Aaaah, it's clear now. This has nothing to do with an optimization between
    UNION vs ROW_NUMBER -- it's that your plan completely changed when you
    switched the syntax -- most importantly the table join order. If you use
    the same join order with the UNION then you should see similar performance.

    It seems that there are two table join orders with nearly identical cost.
    Something in the costing of the UNION statement causes the optimizer to pick
    the unlucky order, whereas with the UNION ALL it picks the more lucky one.

    -Jeremy

    ****

    On Fri, Aug 12, 2011 at 1:32 PM, Taylor, Chris David <
    ChrisDavid.Taylor_at_ingrambarge.com> wrote:****

    First, disregard the part about the large TEMP tablespace usage. I think
    that was coming from the query _*before*_ I rewrote it to use WITH
    statements. I�m not seeing the large TEMP usage now.****

    ****

    For each number below, I�ve included the dbms_xplan information, the row
    source operations, and the timings.****

    ****

    #1 The original query using WITH statements and UNION****

    http://pastebin.com/embed_iframe.php?i=0Ht0V4T3****

    Time: 18 minutes, 38 secs (89,200 rows)****

    ****

    #2 The original query CHANGED from UNION to UNION ALL (only change):****

    http://pastebin.com/embed_iframe.php?i=JybML3y8****

    Time: 1 minute, 03 secs (90,227 rows)****

    ****

    #3 The query from #2 CHANGED to include the ROW_NUMBER function to give us
    the same results as #1:****

    http://pastebin.com/embed_iframe.php?i=75QJ2ShD****

    Time: 50 secs (89,200 rows)****

    ****

    ****

    ****

    *Chris Taylor*****

    *Sr. Oracle DBA*****

    Ingram Barge Company****

    Nashville, TN 37205****

    Office: 615-517-3355****

    Cell: 615-663-1673****

    Email: chris.taylor_at_ingrambarge.com****

    ****

    *CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential
    and may also be privileged. If you are not the named recipient, please
    notify the sender immediately and delete the contents of this message
    without disclosing the contents to anyone, using them for any purpose, or
    storing or copying the information on any medium.*****

    ****




    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago****
    --
    http://www.ardentperf.com
    +1 312-725-9249

    Jeremy Schneider
    Chicago

    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Aug 12, 2011 at 9:15 pm
    So, let's start from the top...

    At a high level the difference here is a case of "less bad or less
    unlucky" - the cardinality estimates are horrid here which makes be
    believe there are probably non-representative stats. This raises the
    question: What is the stats strategy and dbms_stats command being used
    here?

    The main problem with #1 is that deep in the plan cardinality is
    grossly underestimated and that cascaded in to a series of NLJs where
    56,540,199 rows show up and then have a FILTER applied to sort it down
    to 89,200 (so much for FILTER early). Compare that to #3 (and #2)
    where there is just less than 3 orders of magnitude less rows flowing
    through the NLJ and 89,276 sort down to 89,200 (because of a plan
    difference where the FILTER is applied in an un-merged view (lines
    22-26) which also shows up in the UNION ALL #2 plan). This is a
    chronic case of CBO optimizing for a small number of rows and getting
    orders of magnitude more than that. When that happens all bets are
    off...

    One can not make any generalizations about UNION vs UNION ALL vs
    WINDOW SORT because the input into those row sources for these plans
    are grossly different here (the latter 2 have the un-merged view with
    the filter which is why they are close in execution times). This is
    why I asked early on to see the plans and why it is so important to
    include that information - both the plan and execution stats/actual
    row counts/etc.

    Assuming this is not 11g, I'd be interested to see what plan you would
    get if you gathered stats with a 100% sample size - offering the CBO
    the best possible chance at stats (in 11g, the default of
    auto_sample_size should be used).

    Main takeaway here is to fix the input (stats) before attempting to
    fix the output (the execution plan). The CBO generally follows the
    cliche "garbage (stats) in, garbage (plans) out".

    Hope that helps...

    On Fri, Aug 12, 2011 at 1:38 PM, Jeremy Schneider
    wrote:
    I don't know exactly how oracle would cost a UNION versus how oracle would
    cost a UNION-ALL plus a WINDOW.  But it probably depends a bit on some
    environmental factors including row-source cardinality, work area size,
    estimated cardinality of sort columns, etc...  But it seems immaterial which
    comes out cheaper from the CBO - you just have to decide which way to code
    it then help the optimizer get the best plan with your code.  Personally I'd
    go with the union and fix the optimizing of that plan, which is the simpler
    and more readable to do the same thing.  Then, a little digging to see where
    Oracle went wrong in it's guessing.  Calling
    display_plan(null,null,'ALLSTATS LAST') right after an execution can help
    with this since it'll give you a side-by-side comparison of estimated to
    actual rowcounts.  Ideally the optimizer should be getting the same plan for
    both ways of writing the query.

    On Fri, Aug 12, 2011 at 3:21 PM, Taylor, Chris David
    wrote:
    So it is interesting to me then that Oracle would pick a better plan for
    UNION-ALL vs UNION in that case with the same information available to the
    optimizer at the time each query is executed.
    In that regard then, UNION would seem to be a more expensive operation
    REGARDLESS of the additional SORT operation that must occur.

    (Theory) Following that, IF UNION-ALL is always less expensive than UNION
    (before the additional SORT operation) then the WINDOW function applied
    (depending on overhead) will OFTEN come out cheaper than a UNION.

    From: Jeremy Schneider
    Sent: Friday, August 12, 2011 2:49 PM
    To: Taylor, Chris David
    Cc: Greg Rahn; Wolfgang Breitling (breitliw_at_centrexcc.com); mwf_at_rsiz.com;
    oracle-l@freelists.org
    Subject: Re: UNION ALL with ROW_NUMBER vs UNION (WAS: "Wow - has anyone
    used ROW_NUMBER() to get around using a UNION statement when UNION ALL
    doesn't work??")

    Aaaah, it's clear now.  This has nothing to do with an optimization
    between UNION vs ROW_NUMBER -- it's that your plan completely changed when
    you switched the syntax -- most importantly the table join order.  If you
    use the same join order with the UNION then you should see similar
    performance.

    It seems that there are two table join orders with nearly identical cost.
    Something in the costing of the UNION statement causes the optimizer to pick
    the unlucky order, whereas with the UNION ALL it picks the more lucky one.
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Aug 12, 2011 at 11:31 pm
    Grumble....

    Ok a few items I've found.

    PeopleSoft is reanalyzing some of the objects in question:
    http://pastebin.com/embed_iframe.php?i=1iS2y6TK

    SYSADM PS_PO_LINE_DISTRIB TABLE 834268 1 50549 08/12/2011 12:18:16 AM
    SYSADM PS_PO_LINE_DISTRIB INDEX 866303 1 151379 08/12/2011 12:18:18 AM

    (These are NIGHTLY9 jobs - though I have to verify I don't have a rogue stats program kicking off somewhere)

    2. I will rerun the test scenarios with updated 100% stats on all tables/indexes in the few days and report back.

    3. It is still interesting that Oracle behaved differently with a UNION vs UNION ALL when operating off the same statistics information (scenario #1 vs scenario #2 in my original email).

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-663-1673
    Email: chris.taylor_at_ingrambarge.com

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

    -----Original Message-----
    From: Taylor, Chris David
    Sent: Friday, August 12, 2011 4:58 PM
    To: greg_at_structureddata.org
    Cc: Jeremy Schneider; oracle-l@freelists.org
    Subject: Re: UNION ALL with ROW_NUMBER vs UNION (WAS: "Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??")

    10.2.0.4

    I collect stats every weekend using 100% sample size BUT given that this a Peoplesoft system, it is likely there are other stat commands being executed as part of App Engine programs.

    I will get the stats command this evening.

    I disagree with you on the generalization of UNION vs UNION ALL because BOTH operations are operating with the SAME information available to the optimizer (comparing #1 and #2).

    Sure, the row sources are different at different points precisely because Oracle did something different with the UNION prior to the final SORT UNIQUE.

    I believe I have a very solid stats strategy, though of course that may be my own pride :)

    Peoplesoft processing that includes stat gathering operations seldom cause problems but sometimes it does.



    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Aug 12, 2011 at 11:37 pm
    Regardless of what is or is not happening with stats, the plans looks
    like they are getting poor cardinality estimates so that warrants
    further investigation.

    WRT the UNION vs UNION ALL, sure, the stats, etc. are a constant input
    to the CBO but what is happening is the CBO in the UNION ALL is
    unnesting both uncorrelated subqueries in both braches (VW_NSO_1 &
    VW_NSO_2), but the UNION only in the second branch (VW_NSO_1) and not
    the first. Why that is happening is unknown -- maybe a bug, maybe
    costing, maybe edge condition. My point is here is that
    generalization of UNION vs UNION ALL assumes all the rest of the plan
    is the same -- and in this case it is not - for reasons not yet
    determined. At a high level, you are right, but the devil is in the
    details...

    As a matter of debug & triage, you can get the full outline directive
    for both by using dbms_xplan.display_cursor('', null,
    'outline') and find the hints that do the unnesting and try and force
    it in the UNION version. That won't give you the answer of why it's
    happening, but it's a good exercise in debugging plans. Ultimately
    the 10053 traces are probably needed to determine. If you want, grab
    the 10053 trace from both load them into a visual diff tool and see if
    you can track down a difference.

    (took a break and thought more about this)

    After looking at both branches there is an every so slight difference
    in those subqueries: the first branch has a DISTINCT which may be
    causing the issue (bug). There is no need to to have a DISTINCT in
    the first as IN either matches or not - it doesn't match multiple
    times (1 row on the left for 1 or more rows on the right). So try
    this - remove the DISTINCT and see if the UNION plan unnests both
    subqueries.

    PL.ACTIVITY_ID IN (SELECT DISTINCT ING_VESSEL_ID...

    versus
    DL2.ACTIVITY_ID IN (SELECT ING_VESSEL_ID...

    On Fri, Aug 12, 2011 at 2:57 PM, Taylor, Chris David
    wrote:
    10.2.0.4

    I collect stats every weekend using 100% sample size BUT given that this a Peoplesoft system, it is likely there are other stat commands being executed as part of App Engine programs.

    I will get the stats command this evening.

    I disagree with you on the generalization of UNION vs UNION ALL because BOTH operations are operating with the SAME information available to the optimizer (comparing #1 and #2).

    Sure, the row sources are different at different points precisely because Oracle did something different with the UNION prior to the final SORT UNIQUE.

    I believe I have a very solid stats strategy, though of course that may be my own pride :)

    Peoplesoft processing that includes stat gathering operations seldom cause problems but sometimes it does.
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l
  • Taylor, Chris David at Aug 13, 2011 at 12:33 am
    Greg,

    Thank you, that is some good information - something you say puzzles me however:
    "My point is here is that generalization of UNION vs UNION ALL assumes all the rest of the plan is the same -- and in this case it is not..."
    Am I mistaken in thinking that the PLAN is dependent upon the SQL? That is (if I can word this the way I want), isn't the plan created by the optimizer based on what's in the SQL? So that the SQL contains a UNION the optimizer generates a PLAN, and when the SQL contains a UNION ALL in this case, it generates a different plan? (scenarios #1 & #2 only)
    " As a matter of debug & triage, you can get the full outline directive for both by using dbms_xplan.display_cursor('', null,
    'outline') and find the hints that do the unnesting and try and force it in the UNION version. That won't give you the answer of why it's happening, but it's a good > exercise in debugging plans. Ultimately the 10053 traces are probably needed to determine. If you want, grab the 10053 trace from both load them into a visual
    diff tool and see if you can track down a difference."
    Great input - I have never considered using a visual diff tool on 10053 traces. Very interesting.
    " After looking at both branches there is an every so slight difference in those subqueries: the first branch has a DISTINCT which may be causing the issue (bug).
    There is no need to to have a DISTINCT in the first as IN either matches or not - it doesn't match multiple
    times (1 row on the left for 1 or more rows on the right). So try
    this - remove the DISTINCT and see if the UNION plan unnests both subqueries.
    PL.ACTIVITY_ID IN (SELECT DISTINCT ING_VESSEL_ID...
    versus
    DL2.ACTIVITY_ID IN (SELECT ING_VESSEL_ID..."
    I didn't catch that DISTINCT difference. That portion of the query is screwed up besides being different. (I can honestly say I didn't any of the original query). I'll play around with that portion just to see what the optimizer does as well. (I've changed that portion of the SQL in the production version of the code since the original email last week)

    Thanks again for your input on this (in case I come across as unappreciative).

    Chris Taylor
    Sr. Oracle DBA
    Ingram Barge Company
    Nashville, TN 37205
    Office: 615-517-3355
    Cell: 615-663-1673
    Email: chris.taylor_at_ingrambarge.com

    CONFIDENTIALITY NOTICE: This e-mail and any attachments are confidential and may also be privileged. If you are not the named recipient, please notify the sender immediately and delete the contents of this message without disclosing the contents to anyone, using them for any purpose, or storing or copying the information on any medium.

    -----Original Message-----
    From: Greg Rahn
    Sent: Friday, August 12, 2011 6:38 PM
    To: Taylor, Chris David
    Cc: Jeremy Schneider; oracle-l@freelists.org
    Subject: Re: UNION ALL with ROW_NUMBER vs UNION (WAS: "Wow - has anyone used ROW_NUMBER() to get around using a UNION statement when UNION ALL doesn't work??")

    Regardless of what is or is not happening with stats, the plans looks like they are getting poor cardinality estimates so that warrants further investigation.

    WRT the UNION vs UNION ALL, sure, the stats, etc. are a constant input to the CBO but what is happening is the CBO in the UNION ALL is unnesting both uncorrelated subqueries in both braches (VW_NSO_1 & VW_NSO_2), but the UNION only in the second branch (VW_NSO_1) and not the first. Why that is happening is unknown -- maybe a bug, maybe costing, maybe edge condition. My point is here is that generalization of UNION vs UNION ALL assumes all the rest of the plan is the same -- and in this case it is not - for reasons not yet determined. At a high level, you are right, but the devil is in the details...

    As a matter of debug & triage, you can get the full outline directive for both by using dbms_xplan.display_cursor('', null,
    'outline') and find the hints that do the unnesting and try and force it in the UNION version. That won't give you the answer of why it's happening, but it's a good exercise in debugging plans. Ultimately the 10053 traces are probably needed to determine. If you want, grab the 10053 trace from both load them into a visual diff tool and see if you can track down a difference.

    (took a break and thought more about this)

    After looking at both branches there is an every so slight difference in those subqueries: the first branch has a DISTINCT which may be causing the issue (bug). There is no need to to have a DISTINCT in the first as IN either matches or not - it doesn't match multiple
    times (1 row on the left for 1 or more rows on the right). So try
    this - remove the DISTINCT and see if the UNION plan unnests both subqueries.

    PL.ACTIVITY_ID IN (SELECT DISTINCT ING_VESSEL_ID...

    versus
    DL2.ACTIVITY_ID IN (SELECT ING_VESSEL_ID...

    --
    Regards,
    Greg Rahn
    http://structureddata.org

    --
    http://www.freelists.org/webpage/oracle-l
  • Greg Rahn at Aug 13, 2011 at 12:56 am
    Let me try to convey this a slightly different way:

    To say the only difference in the SQL is UNION vs UNION ALL is not the
    same as to say the UNION execution plan is identical UNION ALL
    execution plan other than the sort unique required (e.g. the input
    into the UNION or UNION ALL row source).

    On Fri, Aug 12, 2011 at 5:33 PM, Taylor, Chris David
    wrote:
    Thank you, that is some good information - something you say puzzles me however:
    "My point is here is that generalization of UNION vs UNION ALL assumes all the rest of the plan is the same -- and in this case it is not..."
    Am I mistaken in thinking that the PLAN is dependent upon the SQL?   That is (if I can word this the way I want), isn't the plan created by the optimizer based on what's in the SQL?  So that the SQL contains a UNION the optimizer generates a PLAN, and when the SQL contains a UNION ALL in this case, it generates a different plan? (scenarios #1 & #2 only)
    --
    Regards,
    Greg Rahn
    http://structureddata.org
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 12, '11 at 6:32p
activeAug 13, '11 at 12:56a
posts11
users3
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase