Oracle Scratchpad

December 7, 2018

Plans and Trees

Filed under: Uncategorized — Jonathan Lewis @ 5:58 pm GMT Dec 7,2018

Prompted by a question on the ODC database forum – and also because I failed to get to the “Bonus slides” on my presentation on basic execution plans at both the DOAG and UKOUG conferences, here’s a small of slides demonstrating how to convert a text execution plan into a tree that you can read using the mechanism described in Oracle’s white paper by the phrase: “start from the bottom left and work across and then up”.

The file is a Microsoft Powerpoint file (early version).

 

1 Comment »

  1. Hi Jonathan,
    I pondered over an automatic approach for generating such plan diagrams.
    I have used GraphViz several times for similar tasks.
    I came up with the following query to convert a plan into a GraphViz diagram:

    with input_plan(id, parent_id, depth, position, operation, options, object_name) as (
      select id, parent_id, depth, position, operation, options, object_name
        from plan_table
       where 1 = 1
         --and statement_id = 'right_deep'
         --and statement_id = 'zig_zag'
         and statement_id = 'left_deep'
      ),
      edges(path) as (
      select id||' -> '||parent_id
        from input_plan
       where parent_id is not null),
      nodes(name) as (
      select id||' [label="'||operation||nvl2(options, ' '||options, '')||nvl2(object_name, ' '||object_name, '')||'"]'
        from input_plan),
      leaves(id, depth) as (
      select id, depth
        from input_plan
       where connect_by_isleaf = 1
       start with id = 0
       connect by parent_id = prior id),
      left_most_leaf(id, depth) as (
      select min(id), min(depth) keep (dense_rank first order by id)
        from leaves)
    select 'digraph Plan { rankdir = BT' diagram
      from dual
     union all
    select * 
      from nodes
     union all
    select *
      from edges
     union all
    select '{ rank = same; '||left_most_leaf.id||'; '||listagg(leaves.id||'; ') within group (order by leaves.id)||'}'
      from leaves,
           left_most_leaf
     where leaves.depth > left_most_leaf.depth
     group by left_most_leaf.id
     union all
    select '}' from dual;
    

    It appears to be a bit contrived but I have tried to make the first leaf, or the first operation of a plan without children, to be on the bottom of the diagram.
    At the same time, the subsequent leaf nodes do not need to be on the bottom if their level, or depth from the operation of a plan with id = 0, is less than the level of the first leaf.

    Here is a sample diagram for the example from your presentation, which is a left-deep plan (it can be viewed using http://www.webgraphviz.com/):

    digraph Plan { rankdir = BT
    0 [label="SELECT STATEMENT"]
    1 [label="HASH JOIN"]
    2 [label="HASH JOIN"]
    3 [label="TABLE ACCESS FULL T1"]
    4 [label="TABLE ACCESS FULL T2"]
    5 [label="TABLE ACCESS FULL T3"]
    1 -> 0
    2 -> 1
    3 -> 2
    4 -> 2
    5 -> 1
    }
    

    The image: https://imgur.com/a/dnVjyDT
    The execution plan:

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

    A right-deep plan:

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

    The image: https://imgur.com/a/AoEALa6

    A zig-zag plan:

    -------------------------------------
    | Id  | Operation            | Name |
    -------------------------------------
    |   0 | SELECT STATEMENT     |      |
    |   1 |  HASH JOIN           |      |
    |   2 |   HASH JOIN          |      |
    |   3 |    TABLE ACCESS FULL | T1   |
    |   4 |    HASH JOIN         |      |
    |   5 |     TABLE ACCESS FULL| T2   |
    |   6 |     TABLE ACCESS FULL| T3   |
    |   7 |   TABLE ACCESS FULL  | T4   |
    -------------------------------------
    

    The image: https://imgur.com/a/wXXAR0K

    BR,
    Mikhail.

    Comment by Mikhail Velikikh — December 7, 2018 @ 9:42 pm GMT Dec 7,2018 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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 )

Google+ photo

You are commenting using your Google+ 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.