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??")
- From: Greg Rahn <greg@xxxxxxxxxxxxxxxxxx>
- To: "Taylor, Chris David" <ChrisDavid.Taylor@xxxxxxxxxxxxxxx>
- Date: Fri, 12 Aug 2011 16:37:31 -0700
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('<sql_id>', 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
<ChrisDavid.Taylor@xxxxxxxxxxxxxxx> 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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
