Re: explain plan issues

From: dba cjb <chris.brown_at_providentinsurance.co.uk>
Date: Wed, 9 Nov 2011 02:14:53 -0800 (PST)
Message-ID: <f563f1ca-13ee-468f-9193-b2e908f36ae8_at_p16g2000yqd.googlegroups.com>



On Nov 8, 4:40 pm, Mark D Powell <Mark.Powe..._at_hp.com> wrote:
> On Nov 8, 10:56 am, dba cjb <chris.br..._at_providentinsurance.co.uk>
> wrote:
>
> We have applied patch 45

I don't have any problems with code supply...although will probably do via metalink

I suppose I was just wanting some confirmation that given 2 small feeds into a nested loop producing 3 million + est output ...that shouldn't happen / I guess I was looking for general advice on how to manage

at the moment I've got

  1. use hint
  2. raise a bug with oracle

cheers
Chris B

>
>
>
> > 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 --- Hide quoted text -
>
> - Show quoted text -
Received on Wed Nov 09 2011 - 04:14:53 CST

Original text of this message