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