explain plan issues

From: dba cjb <chris.brown_at_providentinsurance.co.uk>
Date: Tue, 8 Nov 2011 07:56:44 -0800 (PST)
Message-ID: <d00894e5-7f40-43fe-9b9c-fb19ffecd42d_at_u28g2000yqb.googlegroups.com>



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' Received on Tue Nov 08 2011 - 09:56:44 CST

Original text of this message