Re: explain plan issues
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
- use hint
- 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