Re: explain plan issues
Date: Thu, 10 Nov 2011 07:35:50 -0800 (PST)
Message-ID: <dfc18207-bebd-466e-a23b-edb19436c44a_at_u9g2000vbx.googlegroups.com>
On Nov 9, 6:45 pm, Randolf Geist <mah..._at_web.de> wrote:
> 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
thanks everyone again for your comprehensive replies
I will follow suggestions a.s.a.p when I return from a break
regards
Chris B
ps in the meantime ..i have included the code below with the hint we used to avoid the 3 million rows issue for step 3
select /*+ cardinality (pl 1)*/ cl.docnum, cl.doccode ,cl.el1, cl.el3, cl.el4, cl.el5, cl.el6, cl.valuehome, cl.statuser, cl.statrec, cl.statpay, cl.statpayint, cl.descr, nvl2(C.cmpcode, 'Void', 'Active') as Status
from chrisbrown.oas_docline l
inner join chrisbrown.oas_payline pl on l.cmpcode = pl.cmpcode and l.doccode = pl.doccode and l.docnum = pl.docnum and l.doclinenum = pl.doclinenum
inner join chrisbrown.oas_payline cpl on pl.matchref = cpl.matchref and pl.matchref > 0 and pl.doccode != cpl.doccode
inner join chrisbrown.oas_docline cl on cl.cmpcode = cpl.cmpcode and cl.doccode = cpl.doccode and cl.docnum = cpl.docnum and cl.doclinenum = cpl.doclinenum
left outer join chrisbrown.oas_cancel c on c.cmpcode = cl.cmpcode and c.canceldoccode = cl.doccode and c.canceldocnum = cl.docnum where l.ref5 = '45000344'
order by doccode, docnum, el1 Received on Thu Nov 10 2011 - 09:35:50 CST