Re:An explain plan question

 Actually, that is not correct. The first step executed is the "top most" step without any children. In this case, that would actually be step 3.

I know what the Oracle documentation says and what I have been taught...but it is wrong. In fact, the Oracle documentation contradicts itself! If you read Chapter 13 of the Performance Tuning Guide for 10g (13.4.2.1 in 10gr2 to be exact) it says "The execution order in EXPLAIN PLAN output begins with the line that is the furthest indented to the right. The next step is the parent of that line. If two lines are indented equally, then the top line is normally executed first. "

A few paragraphs down, this documentation shows an example (13-2) very similar to yours.

| Id  | Operation                     |  Name        | Rows  | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |     3 |   189 |    10  (10)|
|   1 |  NESTED LOOPS                 |              |     3 |   189 |    10  (10)|
|   2 |   NESTED LOOPS                |              |     3 |   141 |     7  (15)|
|*  3 |    TABLE ACCESS FULL          | EMPLOYEES    |     3 |    60 |     4  (25)|
|   4 |    TABLE ACCESS BY INDEX ROWID| JOBS         |    19 |   513 |     2  (50)|
|*  5 |     INDEX UNIQUE SCAN         | JOB_ID_PK    |     1 |       |            |
|   6 |   TABLE ACCESS BY INDEX ROWID | DEPARTMENTS  |    27 |   432 |     2  (50)|
|*  7 |    INDEX UNIQUE SCAN          | DEPT_ID_PK   |     1 |       |            |
-----------------------------------------------------------------------------------

If the documetation is correct, then Step 5 should be the first step executed. In section 13.4.2.2, it directly contradicts the previous statement.

"The following steps in Example 13-2 physically retrieve data from an object in the database:

    *

      Step 3 reads all rows of the employees table.     *

      Step 5 looks up each job_id in JOB_ID_PK index and finds the rowids of the associated rows in the jobs table.     *

      Step 4 retrieves the rows with rowids that were returned by Step 5 from the jobs table."

It has been a few years since I did this, but my tests of actual execution steps indicated that 13.4.2.2 is the *basically* the correct order. I also found that the steps are not performed to completion in strict sequential order. In the case above, Steps 3/5/4 may be performed sufficiently to generate a result set that is passed on to the next step. After that step (and perhaps others up the line) are completed, the query may return to the steps to generate more result sets.

Regards,
Daniel Fink

Pabba, Chandra wrote:
> Hi,
>
> The rightmost or the most indented and the uppermost operation is the first operation that is executed. In your case, step 5.
>
> HTH

>

>


>     From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of amonte

>     Sent: Tuesday, January 30, 2007 8:44 AM

>     To: oracle-l_at_freelists.org

>     Subject: a explain plan question

>


>     Hi

>


>     I have this query

>


>     SELECT

>        TUH_NVPAGINA.ID_SECCION AS ID_SECCION,

>        TUD_FEDIA.ID_TIPO_DIA AS ID_TIPO_DIA,

>        TUD_FEDIA.ID_MES AS ID_MES,

>        count(distinct TUH_NVPAGINA.TX_COOKIE_SESION) CNT

>     FROM

>        TUH_NVPAGINA,

>        (SELECT FE_DIA, ID_MES, ID_TIPO_DIA

>           FROM TUD_FEDIA

>          WHERE ID_MES = :p_f_inicio) TUD_FEDIA

>     WHERE TUH_NVPAGINA.FE_DIA = TUD_FEDIA.FE_DIA

>     GROUP BY

>        TUH_NVPAGINA.ID_SECCION,

>        TUD_FEDIA.ID_TIPO_DIA,

>        TUD_FEDIA.ID_MES

>


>     and this plan

>


>     Execution Plan

>     ----------------------------------------------------------

>        0      SELECT STATEMENT Optimizer=CHOOSE (Cost=1022392 Card=934 Bytes=49502)

>        1    0   SORT (GROUP BY) (Cost=1022392 Card=934 Bytes=49502)

>        2    1     NESTED LOOPS (Cost=814767 Card=182275095 Bytes=9660580035)

>        3    2       TABLE ACCESS (FULL) OF 'TUD_FEDIA' (Cost=3 Card=30 Bytes=480)

>        4    2       PARTITION RANGE (ITERATOR)

>        5    4         TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159 Card=5992606 Bytes=221726422)

>


>     --------------------------------------------------------------------------------------------

>     | Id  | Operation                  |  Name         | Rows  | Bytes | Cost  | Pstart| Pstop |

>     --------------------------------------------------------------------------------------------

>     |   0 | SELECT STATEMENT           |               |   934 | 49502 |  1022K|       |       |

>     |   1 |  SORT GROUP BY             |               |   934 | 49502 |  1022K|       |       |

>     |   2 |   NESTED LOOPS             |               |   182M|  9213M|   814K|       |       |

>     |*  3 |    TABLE ACCESS FULL       | TUD_FEDIA     |    30 |   480 |     3 |       |       |

>     |   4 |    PARTITION RANGE ITERATOR|               |       |       |       |   KEY |   KEY |

>     |*  5 |     TABLE ACCESS FULL      | TUH_NVPAGINA  |  5992K|   211M| 27159 |   KEY |   KEY |

>     --------------------------------------------------------------------------------------------

>


>     Predicate Information (identified by operation id):

>     ---------------------------------------------------

>


>        3 - filter("TUD_FEDIA"."ID_MES"=TO_NUMBER(:Z))

>        5 - filter("TUH_NVPAGINA"."FE_DIA"="TUD_FEDIA"."FE_DIA")

>


>     I was wondering how to read this plan, the order of steps. From old set autotrace trace exp it seems to me that step 5 is the first step?

>


>        5    4         TABLE ACCESS (FULL) OF 'TUH_NVPAGINA' (Cost=27159 Card=5992606 Bytes=221726422)

>


>     Thanks

>


>     Alex

>

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