explain plan issues

#1

Quote:

---------------------------------------------------------------------------------------------------------------------------------------------------------

Quote:

* 1 | HASH JOIN |
1 | 3490K| 1 |00:03:24.43 | 858K| 807K| 688K|
688K| 8718K

(0)|

Quote:

* 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'

#2

On Nov 8, 10:56*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:

Quote:

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 --

#3

On Nov 8, 4:40*pm, Mark D Powell <Mark.Powe... (AT) hp (DOT) com> wrote:

Quote:

On Nov 8, 10:56*am, dba cjb <chris.br... (AT) providentinsurance (DOT) 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

1) use hint
2) raise a bug with oracle

cheers
Chris B

Quote:

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 -

#4

On Nov 8, 10:56*am, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:

Quote:

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/2...e-join-syntax/
http://hoopercharles.wordpress.com/2...-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.

#5

On Nov 8, 4:56*pm, dba cjb <chris.br... (AT) providentinsurance (DOT) co.uk>
wrote:

Quote:

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:

Quote:

* 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

#6

On Nov 9, 6:45*pm, Randolf Geist <mah... (AT) web (DOT) de> wrote:

Quote:

On Nov 8, 4:56*pm, dba cjb <chris.br... (AT) providentinsurance (DOT) 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

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

explain plan issues

comp.databases.oracle.server comp.databases.oracle.server

explain plan issues - 11-08-2011 , 09:56 AM

Re: explain plan issues - 11-08-2011 , 10:40 AM

Re: explain plan issues - 11-09-2011 , 04:14 AM

Re: explain plan issues - 11-09-2011 , 05:59 AM

Re: explain plan issues - 11-09-2011 , 12:45 PM

Re: explain plan issues - 11-10-2011 , 09:35 AM