Re: explain plan issues

From: Randolf Geist <mahrah_at_web.de>
Date: Wed, 9 Nov 2011 10:45:56 -0800 (PST)
Message-ID: <0d7aa24a-ca90-40fc-b4c9-658c4c47ef7a_at_d5g2000yqg.googlegroups.com>



On Nov 8, 4:56 pm, 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

Unfortunately the plan output is really mangled and you haven't provided the "Predicate Information" section.

But it looks like you've misinterpreted the plan output, because it is a "table prefetch" plan and there is a parent row source (operation ID 2) to the NESTED LOOP that looks like this:

|* 2 | TABLE ACCESS BY INDEX ROWID | mushroom | 1 | 902K| 1 |00:00:00.01 | 224 | 0 |

If this wasn't a "table prefetch" plan this would actually be operation ID 9 and operation ID 9 would become a child operation to this - this would then basically mean, for each loop iteration the index access is estimated to return 14 rows (based on index stats) whereas for the table access 902K rows are estimated (based on table column stats) - hence the total number of rows of the NESTED LOOP is derived from the table estimate that is 4 times 902K (allowing for some rounding issues).

So it looks like your cardinality problem could be caused by some column statistics on the table mushroom, in particular the join or filter columns for this step - check the "Predicate Information" section for operation ID 2.

Hope this helps,
Randolf Received on Wed Nov 09 2011 - 12:45:56 CST

Original text of this message