Re: Unbelievable Outer Join

From: joel garry <joel-garry_at_home.com>
Date: Wed, 12 Aug 2009 09:32:12 -0700 (PDT)
Message-ID: <49ba5be7-2f66-42a1-afa1-bdb668a3237e_at_l5g2000pra.googlegroups.com>

On Aug 12, 7: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

Looks like a job for 10053 trace, see http://www.centrexcc.com/papers.html

Before you get into that, check for a few things:

How fresh are the stats?
Are there histograms? (the default changed to yes going from 9 to 10, that trips up some people)
Have you tried ansi syntax? (I can't remember the details offhand, but there was some situation where it worked better than the old syntax)
Might be worth a try to set optimizer_features_enable to 9.2 and see if that makes a difference.

This post has become a classic:
http://oracle-randolf.blogspot.com/2009/02/basic-sql-statement-performance.html

jg

--
_at_home.com is bogus.
“I think really, truly there are no experts in social media.”
http://www3.signonsandiego.com/stories/2009/aug/12/social-network-put-work/?uniontrib

Received on Wed Aug 12 2009 - 11:32:12 CDT