1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

The correct way of reading an execution (or query) plan in oracle

Discussion in 'SQL PL/SQL' started by Odyssey2001, Oct 8, 2018.

  1. Odyssey2001

    Odyssey2001 Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    France
    Hello,

    I would like to know whether the following algorithm is the correct way of reading an execution (or query) plan in oracle (kind of plan that can be obtained by invoking methods such as dbms_xplan.display_plan or dbms_xplan.display_cursor) :

    Code (SQL):
    WHILE (id <> 0) LOOP

        current_statement = The FIRST NOT yet evaluated statement
    (reading the plan FROM top TO down) which IS the
    right-most indented (that IS, it has no child so it IS a leaf IN
    the tree plan);

        Evaluate current_statement;

        Evaluate the parent statement OF current_statement;

    END LOOP;

    Is the above, the correct way to read an execution (or query) plan?

    Thanks in advance,
     
  2. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Let's look at a plan that won't work with your algorithm:

    Code (SQL):
    --------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                          |       |       |     4 (100)|          |
    |   1 |  MINUS                |                          |       |       |            |          |
    |   2 |   SORT UNIQUE NOSORT  |                          |     1 |  2015 |     1 (100)| 00:00:01 |
    |*  3 |    INDEX FULL SCAN    | LF_HOTEL_TEMP_PK         |     1 |  2015 |     0   (0)|          |
    |   4 |   INTERSECTION        |                          |       |       |            |          |
    |   5 |    INTERSECTION       |                          |       |       |            |          |
    |   6 |     SORT UNIQUE NOSORT|                          |     4 |    48 |     1 (100)| 00:00:01 |
    |*  7 |      INDEX FULL SCAN  | LF_TS_ROOMTYPE_PROP_IDX  |     4 |    48 |     0   (0)|          |
    |   8 |     SORT UNIQUE NOSORT|                          |     4 |    48 |     1 (100)| 00:00:01 |
    |*  9 |      INDEX FULL SCAN  | LF_GTA_ROOMTYPE_PROP_IDX |     4 |    48 |     0   (0)|          |
    |  10 |    SORT UNIQUE NOSORT |                          |     4 |    48 |     1 (100)| 00:00:01 |
    |* 11 |     INDEX FULL SCAN   | LF_HB_ROOMTYPE_PROP_IDX  |     4 |    48 |     0   (0)|          |
    --------------------------------------------------------------------------------------------------
    There are several areas where the indention is the 'farthest to the right' in this plan, meaning there are several steps that feed portions of the data to the result set. The INTERSECTION portion begins a series of three steps that feed data into the results of the topmost index full scan and resulting sort operation to be processed by the MINUS operator. Notice that all of the sorts are preceded by index scan operations that return data from the associated tables. You cannot simply read such a plan from the top down as that's not how the data is being generated. Starting from the bottom and working up toward statement 0 allows you to understand where the intermediate results are generated and how they are accumulated and passed to the final processing steps of the plan.
     
    Odyssey2001 likes this.
  3. Odyssey2001

    Odyssey2001 Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    France
    Hello David,

    Thank you very much for your help. Indeed, your example shows that my algorithm is wrong.

    You said:
    Here, by 'bottom', do you mean the last line of the plan, that is, the line, with the biggest id?

    Also, how do you know, when you read the plan, what is the order of the evaluation when there are several
    operations on the same level? Let me explain what I mean based on the example you provided above:

    As you said : "... there are several steps that feed portions of the data to the result set ..."
    Of course, this is obvious for parent/child operations, for example, in your plan above:

    • Id=7 is evaluated before Id=6, because Id=6 is the parent of Id=7 and therefore needs the result of Id=7 before it can be evaluated by oracle.

    • Id=9 is evaluated before Id=8. For the very same reason.
    So a child (which is more indented to the right than its parent) is always evaluated before its parent.

    But what if, there are several operations exactly on the same level, that is, siblings in the query plan tree?

    For example, The INTERSECTION (Id=5) uses the result of both Id 6 and 8. But which one, Id 6 or 8 is evaluated first by oracle? And by this, I mean, which one do you read first when you look at this plan?
     
  4. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes.

    Follow the indentation. Plans are trees and branches on the same level have the same indentation. Look at the plan I posted and you'll answer your own question.

    Operations on the same level are usually read in reverse order (largest id to smallest id) as they occur sequentially.
     
    Odyssey2001 likes this.
  5. Odyssey2001

    Odyssey2001 Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    France
    Hello,

    Thanks a lot David for your help. Based on your indications here is therefore how I read your query plan:
    Code (SQL):
    --------------------------------------------------------------------------------------------------
    | Id  | Operation             | Name                     | ROWS  | Bytes | Cost (%CPU)| TIME     |
    --------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT      |                          |       |       |     4 (100)|          |
    |   1 |  MINUS                |                          |       |       |            |          |
    |   2 |   SORT UNIQUE NOSORT  |                          |     1 |  2015 |     1 (100)| 00:00:01 |
    |*  3 |    INDEX FULL SCAN    | LF_HOTEL_TEMP_PK         |     1 |  2015 |     0   (0)|          |
    |   4 |   INTERSECTION        |                          |       |       |            |          |
    |   5 |    INTERSECTION       |                          |       |       |            |          |
    |   6 |     SORT UNIQUE NOSORT|                          |     4 |    48 |     1 (100)| 00:00:01 |
    |*  7 |      INDEX FULL SCAN  | LF_TS_ROOMTYPE_PROP_IDX  |     4 |    48 |     0   (0)|          |
    |   8 |     SORT UNIQUE NOSORT|                          |     4 |    48 |     1 (100)| 00:00:01 |
    |*  9 |      INDEX FULL SCAN  | LF_GTA_ROOMTYPE_PROP_IDX |     4 |    48 |     0   (0)|          |
    |  10 |    SORT UNIQUE NOSORT |                          |     4 |    48 |     1 (100)| 00:00:01 |
    |* 11 |     INDEX FULL SCAN   | LF_HB_ROOMTYPE_PROP_IDX  |     4 |    48 |     0   (0)|          |
    --------------------------------------------------------------------------------------------------
    - First Id=9, INDEX FULL SCAN is evaluated

    - Then Id=8, its parent, SORT UNIQUE NOSORT is evaluated

    - Then Id=7, INDEX FULL SCAN is evaluated

    - Then Id=6, its parent, SORT UNIQUE NOSORT is evaluated

    - Then Id=5, INTERSECTION is evaluated which is the parent for both SORT operations (Id 8 and 6) and consumes their output.

    - Then Id=11, INDEX FULL SCAN is evaluated

    - Then Id=10, its parent, SORT UNIQUE NOSORT is evaluated

    - Then Id=4, INTERSECTION, the parent of Id 5 and 10 is evaluated.

    - Then Id=3, INDEX FULL SCAN is evaluated

    - Then Id=2, its parent, SORT UNIQUE NOSORT is evalauted

    - Then Id=1, MINUS, the parent of Id 2 and 4 is evaluated

    - Then Id=0, the whole query and therefore the final result will be obtained

    Is this correct?
     
  6. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,639
    Likes Received:
    368
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Yes.
     
    Odyssey2001 likes this.
  7. Odyssey2001

    Odyssey2001 Starter

    Messages:
    4
    Likes Received:
    0
    Trophy Points:
    10
    Location:
    France
    Awesome! Thank you very much for your time and your help. I really appreciated your clear description and example.