Re: explain plan issues

From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Wed, 9 Nov 2011 03:59:29 -0800 (PST)
Message-ID: <0f419b55-c81f-4691-8231-d4c7f9f9b8a7_at_cu3g2000vbb.googlegroups.com>



On Nov 8, 10:56 am, dba cjb <chris.br..._at_providentinsurance.co.uk> wrote:
> oracle 10.2.0.4 enterprise windows 2003
>
> We have been running a query that will only run satisfactorily with a
> cardinality hint
>
> We don't understand how a nested loop with 2 estimated feeds of 4
> ( id 4 ) & 14 ( id 9) can result in an estimated output of 3496K|
> ( id 3)
>
> I would welcome any ideas on wether this is a bug with the optimiser /
> the stats seem to be ok when tested with other queries
>
> regards
> Chris B
>
> Id  | Operation                       | Name                    |
> Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |
> 1Mem | Used-Mem
> |
>
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---
>
> |*  1 |  HASH JOIN                      |
> |      1 |   3490K|      1 |00:03:24.43 |     858K|    807K|   688K|
> 688K| 8718K
> (0)|
>
> |*  2 |   TABLE ACCESS BY INDEX ROWID   | mushroom
> |      1 |    902K|      1 |00:00:00.01 |     224 |      0 |
> |       |
> |
>
> |   3 |    NESTED LOOPS                 |
> |      1 |   3496K|    436 |00:00:00.03 |      27 |      0 |
> |       |
> |
>
> |   4 |     NESTED LOOPS                |
> |      1 |      4 |      1 |00:00:00.01 |      24 |      0 |
> |       |
> |
>
> |   5 |      TABLE ACCESS BY INDEX ROWID| chicken
> |      1 |     42 |      3 |00:00:00.01 |      10 |      0 |
> |       |
> |
>
> |*  6 |       INDEX SKIP SCAN           | chicken_MOOCODEREF5
> |      1 |     42 |      3 |00:00:00.01 |       9 |      0 |
> |       |
> |
>
> |*  7 |      TABLE ACCESS BY INDEX ROWID| mushroom
> |      3 |      1 |      1 |00:00:00.01 |      14 |      0 |
> |       |
> |
>
> |*  8 |       INDEX UNIQUE SCAN         | mushroom_INDEX1
> |      3 |      1 |      3 |00:00:00.01 |      11 |      0 |
> |       |
> |
>
> |*  9 |     INDEX RANGE SCAN            | mushroom_OINK_IND
> |      1 |     14 |    434 |00:00:00.01 |       3 |      0 |
> |       |
> |
>
> |  10 |   TABLE ACCESS FULL             | chicken
> |      1 |     21M|     21M|00:04:15.32 |     857K|    807K|
> |       |
> |
>
> ---------------------------------------------------------------------------­---------------------------------------------------------------------------­---
>
> select /*+gather_plan_statistics*/ * from blahblah.chicken l  inner
> join blahblah.mushroom pl on l.MOOcode = pl.MOOcode
> and
>
> l.BAAcode = pl.BAAcode and l.BAAnum = pl.BAAnum and l.BAAlinenum =
> pl.BAAlinenum  inner join blahblah.mushroom cpl on pl.OINKref
> =
>
> cpl.OINKref and pl.OINKref > 0 and pl.BAAcode != cpl.BAAcode  inner
> join blahblah.chicken cl  on cl.MOOcode = cpl.MOOcode
> and
>
> cl.BAAcode = cpl.BAAcode  and cl.BAAnum = cpl.BAAnum  and
> cl.BAAlinenum = cpl.BAAlinenum  where l.ref5 =
> '34234234'

You are interpretting the nested join in the execution plan partially incorrect. The optimizer is predicting that the outer row source will be 4 rows which, if accurate, would be indicated in the "Starts" column for the inner row source when the query executes. For EACH of those 4 predicted rows in the outer row source the optimizer is predicting that 14 rows will be returned from the inner row source - so the optimizer should actually be predicting that 4*14 rows (or less if there was an access predicate on operation 4) will be returned by the nested loops operation on ID 4.

I am not sure if you noticed, but there is also a problem with the actual rows returned by the nested loops join of operations 4 and 9 - a nested loops join of 1 actual row and 434 actual rows should not produce 436 rows. There are a number of ANSI related bugs listed in Metalink (MOS) - and you might be hitting one or more of those bugs that affect 10.2.0.4. See the following blog articles for some of the bug numbers:
http://hoopercharles.wordpress.com/2010/12/26/feeling-ansi-about-oracle-join-syntax/ http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-ready-or-not/

If we slightly reformat your SQL statement, it would look like this: select /*+gather_plan_statistics*/
  *
from
  blahblah.chicken l
inner join
  blahblah.mushroom pl
on
  l.MOOcode = pl.MOOcode

  and l.BAAcode = pl.BAAcode
  and l.BAAnum = pl.BAAnum
  and l.BAAlinenum = pl.BAAlinenum

inner join
  blahblah.mushroom cpl
on
  pl.OINKref = cpl.OINKref
  and pl.OINKref > 0
  and pl.BAAcode != cpl.BAAcode
inner join
  blahblah.chicken cl
on
  cl.MOOcode = cpl.MOOcode
  and cl.BAAcode = cpl.BAAcode
  and cl.BAAnum = cpl.BAAnum
  and cl.BAAlinenum = cpl.BAAlinenum

where
  l.ref5 = '34234234'

Do you actually need ALL columns from ALL 4 tables? Do you see different results if you just specify in the SELECT clause the columns that are necessary to the program that will consume the SQL statement's resultset?

Do you see different results if you avoid ANSI style joins? For example (once again, replace the * in the SELECT clause with just the columns that are needed):
select /*+gather_plan_statistics*/
  *
from

  blahblah.chicken l,
  blahblah.mushroom pl
  blahblah.mushroom cpl
  blahblah.chicken cl

where
  l.ref5 = '34234234'
  AND l.MOOcode = pl.MOOcode
  and l.BAAcode = pl.BAAcode
  and l.BAAnum = pl.BAAnum
  and l.BAAlinenum = pl.BAAlinenum
  AND pl.OINKref = cpl.OINKref
  and pl.OINKref > 0
  and pl.BAAcode != cpl.BAAcode
  AND cl.MOOcode = cpl.MOOcode
  and cl.BAAcode = cpl.BAAcode

  and cl.BAAnum = cpl.BAAnum
  and cl.BAAlinenum = cpl.BAAlinenum

Seeing the actual SQL statement, and actual execution plan with the Predicate Information section would be helpful.

Charles Hooper
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Wed Nov 09 2011 - 05:59:29 CST

Original text of this message