Re:An explain plan question

From: daniel.fink@xxxxxxxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx
Date: Tue, 30 Jan 2007 15:30:32 +0000

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


Thanks
Chandra Pabba

    From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 
On Behalf Of amonte
    Sent: Tuesday, January 30, 2007 8:44 AM
    To: oracle-l@xxxxxxxxxxxxx
    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


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

Re:An explain plan question

Other related posts: