Re: Reading an execution plan puzzle

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_at_gmx.net> To: <oracle-l_at_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