Re: explain plan issues

From: Mark D Powell <Mark.Powell2_at_hp.com>
Date: Tue, 8 Nov 2011 08:40:10 -0800 (PST)
Message-ID: <9783abfb-0d20-4315-96b8-f44cfe0658b0_at_t8g2000yql.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 should list the actual SQL. It is fine if you want to modify the actual table names to obscure the real names but what people post that they do and the full information contained in the actual code do not always match up completely. The missing portions are often very important.

To determine if you could be hitting a but the full Oracle version would be necessary since the CBO potentially changes with every patch set.

HTH -- Mark D Powell -- Received on Tue Nov 08 2011 - 10:40:10 CST

Original text of this message