Re: Unbelievable Outer Join

From: Randolf Geist <mahrah_at_web.de>
Date: Thu, 13 Aug 2009 03:38:52 -0700 (PDT)
Message-ID: <86bb627b-bfd6-42ca-b68d-758bd7b26a69_at_k6g2000yqn.googlegroups.com>

On Aug 12, 4:16 pm, Jan <noreply...._at_gmail.com> wrote:
>  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.
>
> |*  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)|
> |       |
>
> 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)
>
> 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????

There are a couple of odd things in the plan you've posted, apart from the wrong cardinality estimate of the NESTED LOOP OUTER operation:

1. The cost of the INDEX RANGE SCAN on _T201_14935572 and the table access by ROWID on TI238_14935572 have a cost of 0, which looks in first place like the statistics are questionable. You say that dynamic sampling is used, but do you get a confirmation in the DBMS_XPLAN output that dynamic sampling has really been used? It shows up in the "Notes" section below the plan, however doesn't tell you on which table dynamic sampling has been performed. This can be checked by looking at the 10053 optimizer trace. If you're unlucky you have 0 rows / 0 blocks in the data dictionary and dynamic sampling doesn't get used or doesn't help in this case. How do you "enforce" dynamic sampling this particular table?
2. The index used for the inner table lookup is not the one expected for the nested loop join, i.e. it doesn't cover the join criteria but uses this predicate:

10 - access("IQ_SYNC_OUT"."T201TIMESERIES"=10100000001372631)

The join criteria is then applied in the TABLE ACCESS BY ROWID step for all rows that are returned by above index range scan. Does this make sense? How many rows satisfy above filter predicate when table is populated with millions of rows?

3. What is the FILTER operation performing above the NESTED LOOP OUTER operation? This is operation ID = 6.

I would suggest the following:

1. Post the complete DBMS_XPLAN output, ideally the one from the shared pool child cursor if it is still available using DBMS_XPLAN.DISPLAY_CURSOR, otherwise DBMS_XPLAN.DISPLAY

Make sure you include the complete "Predicate Information" section, that hasn't been done so far as I can see.

b) Can you post the corresponding SQL query?

c) If you can reproduce the issue, try to get hold of a 10053 optimizer trace. It might contain some information how the optimizer comes to the 1 row estimate of the outer join operation.

Of course, things seem to be more complicated since you don't seem to be able to reproduce the issue reliably in the live system, right?

Regards,
Randolf

Oracle related stuff blog:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Thu Aug 13 2009 - 05:38:52 CDT