Message-ID: <027901c87412$9f308690$0300a8c0@Primary>
From: "Jonathan Lewis" <jonathan@jlcomp.demon.co.uk>
To: <makulev@gmx.net>,
 <oracle-l@freelists.org>
References: <001101c87177$daf62330$6401a8c0@trivadis.com>
Subject: Re: Reading an execution plan  puzzle 
Date: Wed, 20 Feb 2008 22:47:50 -0000
MIME-Version: 1.0
Content-Type: text/plain; format=flowed;	charset="iso-8859-1";	reply-type=original
Sender: oracle-l-bounce@freelists.org
Errors-to: oracle-l-bounce@freelists.org
Precedence: normal
Reply-to: jonathan@jlcomp.demon.co.uk


Notes inline

Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html


----- Original Message ----- 
From: "Milen Kulev" <makulev@gmx.net>
To: <oracle-l@freelists.org>
Sent: Sunday, February 17, 2008 3:14 PM
Subject: Reading an execution plan puzzle


> Hello Listers,
>
> Recently I have got puzzled when I tried to read the following execution plan 
> .
> The SQL was:
>
>
> My questions are :
> 1) Should I always trace (with event 10046 or 10053) the SQL to get the "full" 
> (with all the steps) execution plan?

It's a good idea.  It avoids errors caused by EXPLAIN PLAN
not knowing the type of your bind variables, and it supplies
actual (peeked) values for optimisation rather than using defaults
for selectivity that might otherwise be appropriate.

The presence of the table access to SUPP_SCHED_AGREEMENT_PART_TAB
in the explain plan may be due to bind confusion.

> 2) Is there any systematic "approach" (apart from trial & error) to reveal the 
> hidden (the this case FLTER) steps ?

The change in plan is a little worrying with with the introduction
of the aliasing - it suggests that your previous plan was suffering
from incorrect column capture - i.e. using a column of the same
name from the wrong table - rather than anything else.

The "missing filter" (from my perspective) is above line 4 - but its
absence is revealed by the filter predicate associated with line 4,
and when I see this pattern, I sometimes edit the output from
explain plan to make it easier to read - see below.

> 3) Why is "explain plan for" not able to ident properly parent/child steps ? 
> Perhaps there is a
> Bug related to LEVEL pseudo variable when using CONNECT BY ?

There is a bug with 10g's calculation of the DEPTH column in some cases -
in particular when there are multiple scalar subqueries in the where clause.
So this query might be exhibiting the bug.  It may not be documented, I haven't
checked, and it is partly fixed in 11g.

In your case, I think lines 6, 7, and 8 are all indented one space further
to the right than they should be.

Just run a query to 'select id, parent_id from plan_table' after doing the 
explain plan
to see if there's anything funny in your example.


Lines 3 - 8 would be more readable as follows; the FILTER is my re-insertion
of a filter operation that used to appear in similar 8i plans, but has generally 
been
squashed out of existence in 9i; line 4 (with its direct descendents if it had 
any) then
has to be pushed to the right;  and lines 6,7,8 are pushed to the left to cater 
for the
depth bug.

|*  3 |    TABLE ACCESS BY INDEX ROWID    | SUPPLIER_SCHEDULE_TAB         |
            FILTER
|*  4 |      INDEX FULL SCAN              | SUPPLIER_SCHEDULE_UK          |
|*  5 |      INDEX FAST FULL SCAN         | USER_ALLOWED_SITE_B_IX        |
|   6 |      SORT AGGREGATE               |                               |
|   7 |       FIRST ROW                   |                               |
|*  8 |        INDEX RANGE SCAN (MIN/MAX) | SUPPLIER_SCHEDULE_UK          |


>
> I am awaiting your comments impatiently ;)
>
> Best Regards. Milen
> P.S. There is no difference in the performance of both "versions" of this SQL
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
>
>
> -- 
> No virus found in this incoming message.
> Checked by AVG Free Edition.
> Version: 7.5.516 / Virus Database: 269.20.7/1283 - Release Date: 16/02/2008 
> 14:16
>
> 

--
http://www.freelists.org/webpage/oracle-l