Re: Unbelievable Outer Join
Date: Wed, 12 Aug 2009 09:10:49 -0700 (PDT)
Message-ID: <92e78ac7-2155-414a-8d99-ea68a60fdb77_at_c14g2000yqm.googlegroups.com>
On 12 srp, 16:55, Mark D Powell <Mark.Pow..._at_eds.com> wrote:
> On Aug 12, 10:25 am, Jan <noreply...._at_gmail.com> wrote:
>
>
>
>
>
> > On 12 srp, 16:16, Jan <noreply...._at_gmail.com> wrote:
>
> > > Hi,
> > > our 10.2.0.4.0 - 64bit EE running on Windows Server 2003 executes one
> > > kind of insert from select whose explain plan I can't comprehend.
>
> > > This is the explain plan of the statement:
> > > -----------------------------------------------------------------------------------------------------------------------------------
> > > | Id | Operation |
> > > Name | Rows | Bytes | Cost (%CPU)| Time |
> > > Pstart| Pstop
> > > |
> > > -----------------------------------------------------------------------------------------------------------------------------------
> > > | 0 | INSERT STATEMENT
> > > | | | | 512 (100)|
> > > | |
> > > |
> > > | 1 | HASH UNIQUE
> > > | | 1 | 167 | 512 (1)| 00:00:07
> > > | |
> > > |
> > > |* 2 | TABLE ACCESS BY INDEX ROWID |
> > > TI238_185727 | 1 | 26 | 2 (0)| 00:00:01
> > > | |
> > > |
> > > | 3 | NESTED LOOPS
> > > | | 1 | 167 | 511 (1)| 00:00:07
> > > | |
> > > |
> > > | 4 | NESTED LOOPS
> > > | | 1 | 141 | 509 (1)| 00:00:07
> > > | |
> > > |
> > > | 5 | NESTED LOOPS
> > > | | 1 | 91 | 100 (2)| 00:00:02
> > > | |
> > > |
> > > |* 6 | FILTER
> > > | | | | |
> > > | |
> > > |
> > > | 7 | NESTED LOOPS OUTER
> > > | | 1 | 76 | 99 (3)| 00:00:02
> > > | |
> > > |
> > > |* 8 | TABLE ACCESS FULL |
> > > TT203_10100000001372631 | 6798 | 331K| 99 (3)| 00:00:02
> > > | |
> > > |
> > > |* 9 | TABLE ACCESS BY INDEX ROWID |
> > > TI238_14935572 | 1 | 26 | 0 (0)|
> > > | |
> > > |
> > > |* 10 | INDEX RANGE SCAN |
> > > I_T201_14935572 | 1 | | 0 (0)|
> > > | |
> > > |
> > > |* 11 | INDEX RANGE SCAN |
> > > IDX_T510_PARENT_TO_CHILD | 111 | 1665 | 1 (0)| 00:00:01
> > > | |
> > > |
> > > | 12 | PARTITION RANGE ITERATOR
> > > | | 1 | 50 | 409 (1)| 00:00:05 |
> > > KEY | 23
> > > |
> > > |* 13 | TABLE ACCESS BY LOCAL INDEX ROWID|
> > > TT203_3401330 | 1 | 50 | 409 (1)| 00:00:05 |
> > > KEY | 23
> > > |
> > > |* 14 | INDEX RANGE SCAN |
> > > UQ_3401330_BD | 48 | | 360 (1)| 00:00:05 |
> > > KEY | 23
> > > |
> > > |* 15 | INDEX RANGE SCAN |
> > > I_VAL_185727 | 1 | | 1 (0)| 00:00:01
> > > | |
> > > |
> > > -----------------------------------------------------------------------------------------------------------------------------------
>
> > > (I hope the formatting will show up correctly)
>
> > > See the NESTED LOOPS OUTER between TT203_10100000001372631 and
> > > TI238_14935572?
> > > The join is written this way:
> > > TT203_10100000001372631.OID = TI238_14935572.T203VALUE_OID (+)
>
> > > Inner table TT203_10100000001372631 has really 6798 rows and outer
> > > table TI238_14935572 has one. Why would optimizer think this will
> > > result in 1 row????
>
> > > The plan is that completely wrong because of this. The select ran 50
> > > minutes instead of a under a minute which it would normally....
>
> > > Do you have any experience with this behavior?
>
> > I apologize for the formatting of the explain plan, very unhelpful.
> > This is the part of plan in question:
> > Operation | Rows
> > -----------------------------------
> > NESTED LOOPS OUTER | 1
> > TABLE ACCESS FULL | 6798
> > TABLE ACCESS BY INDEX ROWID | 1
>
> > Best regards,
> > Jan- Hide quoted text -
>
> > - Show quoted text -
>
> Jan, it does appear to be a bug in determining the cardinality on the
> outer join. It looks like the CBO only expects to find one matching
> key value and does not consider the fact the query wants an outer
> join.
> I am not an expert on the optimizer but there have been bugs in the
> CBO since it came out back with version 7.0. Any opinion I could make
> as to why would be pure speculation. If I had to guess I would hazard
> a guess that the bug results from a side effect of the presence of
> partioning which would force the CBO to consider partition pruning.
>
> The above is unless there is an unshown filter condition that the
> predicate portion of the explain plan shows is applied in this step so
> that all 6798 rows would not be returned.
>
> Have you verified that the statistics are current for all objects in
> this query>
>
> HTH -- Mark D Powell --– Skrýt citovaný text –
>
> – Zobrazit citovaný text –
Thanks for opinion Mark!
Neither of the two tables is partitioned. The statistics are I believe accurate.
The only way I can explain it is that it is a bug. But this is such a basic functionality that I can't believe it would not be fixed yet. It just seems too silly to be a bug.
TT203_10100000001372631 is filtered during TABLE ACCESS FULL operation, but the way I undestand the plans is that 6798 rows will be left (moved into the next part which is the nested loop) after filter is applied. No filtering is applied during NESTED LOOPS OUTER operation, it's really just "6798 rows " left outer join "1 row" the way I understand it, which should result in "6798 rows " not "1 row" as estimated.
These are the predicate information for the select which are of concern to the strange part of the plan:
8 - filter(("D"."TO_TIME">TIMESTAMP' 2009-06-13 14:08:08' AND "D"."TO_TIME"<=TIMESTAMP' 2009-09-25 22:00:00' AND "D"."FROM_TIME"<TIMESTAMP' 2009-09-25 22:00:00' AND "D"."T406VALUE_STATE"<>'L'))
9 - filter("D"."OID"="IQ_SYNC_OUT"."T203VALUE_OID") 10 - access("IQ_SYNC_OUT"."T201TIMESERIES"=10100000001372631)
Jan Received on Wed Aug 12 2009 - 11:10:49 CDT