Mauro Pagano's Blog

Execution plan tree temperature

7 Comments

 

During the Xmas holidays I made several additions to SQLd360 I had on my TODO list for quite a while, I’ll try to blog about the most important ones in the coming days.

Something I wanted to do for a long time was to make understanding execution plan easier, I hope the tree representation introduced here achieved such goal.

SQLd360 v1601 takes this chart a step further, marking nodes with different colors depending on how often such execution plan step shows up in ASH. Basically depending on “how hot” (the temperature) each step is a color between yellow and red is used to color the node, making it easier to determine in which section of the plan you should put your attention.
All those steps that never show up in ASH are represented in white.

So in example a silly SQL like the following takes 21 secs in my database

select count(*) 
  from (select rownum n1 from dual connect by rownum <= 10000),   
       (select rownum n1 from dual connect by rownum <= 100000)

------------------------------------------------------------------------
| Id  | Operation                         | Name | E-Rows | Cost (%CPU)|
------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |      |        |     4 (100)|
|   1 |  SORT AGGREGATE                   |      |      1 |            |
|   2 |   MERGE JOIN CARTESIAN            |      |      1 |     4   (0)|
|   3 |    VIEW                           |      |      1 |     2   (0)|
|   4 |     COUNT                         |      |        |            |
|   5 |      CONNECT BY WITHOUT FILTERING |      |        |            |
|   6 |       FAST DUAL                   |      |      1 |     2   (0)|
|   7 |    BUFFER SORT                    |      |      1 |     4   (0)|
|   8 |     VIEW                          |      |      1 |     2   (0)|
|   9 |      COUNT                        |      |        |            |
|  10 |       CONNECT BY WITHOUT FILTERING|      |        |            |
|  11 |        FAST DUAL                  |      |      1 |     2   (0)|
------------------------------------------------------------------------

18 ASH samples captured the execution on the BUFFER SORT step and 3 samples captured the SORT AGGREGATE.

The execution plan tree temperature looks like this

Screen Shot 2016-01-06 at 5.56.28 PM

Hopefully this will make it easier for people who don’t look into execution plans all the day to quickly spot where they should focus their attention 😀

7 thoughts on “Execution plan tree temperature

  1. cool stuff!
    Here is a script from Tim Gorman doing something similiar but instead of being graphic it puts a count of ASH lines next to each line in the execution plan:
    https://github.com/khailey/sqltuning/blob/master/ash_xplan.sql

    Like

  2. PS here is an example of output from Tim’s script https://github.com/khailey/sqltuning/blob/master/ash_xplan_example.txt

    Like

  3. hi Mauro,

    Great stuff, I was wondering if there is a stand alone version of this tool instead of it being bundled in sqld360 ?? or any plans to do a stand alone version in the future ?? maybe like passing in SQLID as input ???

    Like

    • Hey Max, frankly it’s not on the top of my list, SQLd360 usually takes just a few mins to run and it’s free so the price to pay is pretty small. Have you found any issue using SQLd360 that made you dislike the tool?

      Liked by 1 person

  4. No issues with sqld360, i was just curious and i think I sometimes overlook this in sqld360 as it provides lots of great info and i miss this part…so i was curious to see if there is any standalone version…

    Thanks for the great work and may GOD bless u…

    Like

  5. Pingback: Top Executions SQL Monitoring styles reports in SQLd360 | Mauro Pagano's Blog

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