Re: Unbelievable Outer Join
From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 13 Aug 2009 05:43:12 -0700 (PDT)
Message-ID: <65827114-449d-4881-a490-a12d8a869ddc_at_k6g2000yqn.googlegroups.com>
On Aug 12, 12:19 pm, Ruud de Koter <nob..._at_internet.org> wrote:
> Hi Jan,
>
>
>
>
>
> > 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
>
> Did it occur to you that the join syntax you are using is not the ANSI
> syntax recommended by Oracle? I wouldn't be surprised at all if the CBO
> were to do this correctly when you 'd be using this newer syntax.
>
> I 'd say: try rewriting the statement using the ANSI syntax and see what
> that brings.
>
> Good luck,
>
> Ruud de Koter- Hide quoted text -
>
> - Show quoted text -
Date: Thu, 13 Aug 2009 05:43:12 -0700 (PDT)
Message-ID: <65827114-449d-4881-a490-a12d8a869ddc_at_k6g2000yqn.googlegroups.com>
On Aug 12, 12:19 pm, Ruud de Koter <nob..._at_internet.org> wrote:
> Hi Jan,
>
>
>
>
>
> > 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
>
> Did it occur to you that the join syntax you are using is not the ANSI
> syntax recommended by Oracle? I wouldn't be surprised at all if the CBO
> were to do this correctly when you 'd be using this newer syntax.
>
> I 'd say: try rewriting the statement using the ANSI syntax and see what
> that brings.
>
> Good luck,
>
> Ruud de Koter- Hide quoted text -
>
> - Show quoted text -
Where does Oracle recommend using the newer syntax over the traditional syntax? Both are fully valid under the ANSI standard.
Over the last couple of releases the CBO has generally had more issues with statements written using the newer syntax verse the older syntax based on forum posts.
HTH -- Mark D Powell -- Received on Thu Aug 13 2009 - 07:43:12 CDT