Oracle Scratchpad

September 3, 2014

Order of Operation

Filed under: Execution plans,Oracle — Jonathan Lewis @ 9:42 am GMT Sep 3,2014

One response to my series on reading execution plans was an email request asking me to clarify what I meant by the “order of operation” of the lines of an execution plan. Looking through the set of articles I’d written I realised that I hadn’t made any sort of formal declaration of what I meant, all I had was a passing reference in the introduction to part 4; so here’s the explanation.

 

By “order of operation” I mean the order in which the lines of an execution plan start to produce a rowsource. It’s worth stating this a little formally as any other interpretation could lead to confusion; consider the following simple hash join:


-------------------------------------
| Id  | Operation           | Name  |
-------------------------------------
|   0 | SELECT STATEMENT    |       |
|*  1 |  HASH JOIN          |       |
|   2 |   TABLE ACCESS FULL | T1    |
|   3 |   TABLE ACCESS FULL | T2    |
-------------------------------------

The tablescan at line 2 is the first operation to start producing a rowsoruce; the hash join at line 1 consumes the output and builds a hash table – so it definitely has to do some work before line 3 starts to run – but it doesn’t start generating a rowsource at this point. It’s only after line 3 starts its tablescan and starts to generate its rowsource that line 1 can produce a rowsource by consuming the rows coming from line 3 and probing the in-memory hash table. So line 1 starts to produce its rowsource only after line 3 starts producing its rowsource. The “order of operation” is 2, 3, 1, 0. Perhaps, for the purposes of avoiding confusion, it would be better in future if I remembered to say: “the order of rowsource generation”.

4 Comments »

  1. I guess the bigger question is – how do you infer the order of operation/rowsource generation from the query plan?

    Comment by Johan — September 3, 2014 @ 12:26 pm GMT Sep 3,2014 | Reply

    • Johan,
      That’s quite a good guess. Perhaps the answer will be found in the series on reading execution plans that I mentioned and linked to in the first line of the post.

      Comment by Jonathan Lewis — September 3, 2014 @ 3:13 pm GMT Sep 3,2014 | Reply

  2. I am a little confused by this blog entry. I think you are referring to the ‘order the plan actually runs’. Is that correct or am I misreading what you are saying? If this is wrong, please feel free to slap me down…

    I also want to add that oracle’s documentation on this is awful.

    How to determine order of operation
    – look for the row source farther to the right
    – if 2 entries are the same depth whatever is on top goes first

    So farthest to the right and on top goes first. I did find 1 ‘sort of’ exception that I outline at the bottom. For complex plans.. I often print them out and number them to get my order of operation. I consider this the most important piece in understanding a plan. If you know the order, the plan is less ‘greek’.

    BTW, I believe the query below will print out the order. Its been a while since I did this, so my order by may be wrong, but I believe its just 2 fields. Note the ‘ID’ is just a rownum field. It has nothing to do with order of execution.

    – might have my order by wrong… I have not needed to use this in a while.
    select object_Name,object_type,operation,option, — anything I might want
    from v$sql_plan
    where sql_id =
    and child_number =
    order by depth, position

    For complex queries, this can be alot easier. Because if you know the order of operation, its easier to figure out waht is going on. Typically you gain more efficiency, by improving filters that run early in the query than ones that run late. Sometimes I would actually run the above and do a ‘minus’ between complex plans to see the differences based on the order of execution.

    I think there is 1 ‘sort of’ exception to this rule. Its when you have a query with a where clause like this

    where (columnA= or columnB=)

    With this you actually get a different plan depending on whether columnA always finds a value. If it doesn’t and you go to column be, you get a tree underneatth your initial plan. So oracle checked ColumnA, nothing, then checks column B. If data is found in column B, you actually don’t get the 2nd part.

    Sorry, I don’t have an example handy.

    Comment by Ryan — September 11, 2014 @ 4:56 pm GMT Sep 11,2014 | Reply

    • Ryan,

      I have given a definition of what I mean by “order of operation”. You have made a comment about the ‘order the plan actually runs’ without making any statement about what it means to “actually run”. When is a line of a plan “actually running” ? In my example (in the article) the order in which the lines START to consume CPU is 0,1,2,3; the order in which the lines START to handle data is 2,1,3,0; the order in which the lines START to produce a rowsource is 2,3,1,0.

      Consider the plan below:

      
      select name from user$ where ctime > trunc(sysdate)
      union all
      select name from obj$ where obj# = 99
      ;
      
      ---------------------------------------------------------------------------------------
      | Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
      ---------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT             |        |     2 |    53 |     6   (0)| 00:00:01 |
      |   1 |  UNION-ALL                   |        |       |       |            |          |
      |*  2 |   TABLE ACCESS FULL          | USER$  |     1 |    22 |     3   (0)| 00:00:01 |
      |   3 |   TABLE ACCESS BY INDEX ROWID| OBJ$   |     1 |    31 |     3   (0)| 00:00:01 |
      |*  4 |    INDEX RANGE SCAN          | I_OBJ1 |     1 |       |     2   (0)| 00:00:01 |
      ---------------------------------------------------------------------------------------
      
      Predicate Information (identified by operation id):
      ---------------------------------------------------
         2 - filter("CTIME">TRUNC(SYSDATE@!))
         4 - access("OBJ#"=99)
      
      

      Line 2 is the first one to start producing a rowsource; according to the document you quote (and your SQL fragment) it would be line 4.

      Please note that this blog item was just a brief note referencing a series on reading Execution Plans – I am not going to repeat here the things that I’ve described in 15,000 words of articles.

      Comment by Jonathan Lewis — September 11, 2014 @ 5:22 pm GMT Sep 11,2014 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,422 other followers