August - Visualization 1 - SQL Execution Plan Volatility

The first visualization I will be working on is something I have been wanting for a while. I need a way to quickly show SQL Statement performance over time for 1 statement, especially to identify Execution Plan volatility. When researching performance problems I often need to quickly digest historical performance of a trouble statement - a statement that usually has run fine for years but now has started acting up.

Luckily Oracle 10g/11g/12c has this type of information in the Active Workload Repository. If you are licensed, the DBA_HIST views are a treasure trove of useful performance information. Don't forget to up your default retention from 8 days to something larger. On my prod instances I usually set it to 400 days so I have a year worth of performance data plus some padding.

Unluckily I have usually just seen this presented in tabular format with no easy way to gain some context.

My dream is to have some type of color map + bar graph hybrid which will allow me to see the difference between the worst and best execution plans and how often they are used when executing the SQL statement. Let's start working through the available data to see if I can reach that goal.

To find out the SQL statements running in your database with the most volatile plans

-- For all AWH History find SQL statements that have used more than 1 PLAN_HASH_VALUE
select sql_id, count(*) from (
select distinct sql_id, plan_hash_value from dba_hist_sqlstat)
group by sql_id

Run this against one of your databases and you will find SQL statements that are recorded in AWR that use multiple plans.

Data Organization
DBA_HIST_SQLSTAT will be where we focus on getting our data. If you have Diagnostics and Tuning pack then you can access this view via OEM or query it directly.

A line is recorded for each sql_id, plan_hash_value combination that ran during the snap period. Details on the execution including # of times executed, and elapsed time can be used to calculate an average elapsed time per execution.

Since I am most interested in which plan_hash_values contribute to good and to bad performance I have come up with this query to drive the visualization:

SELECT end_interval_time,
       ROUND(elapsed_time_total/1000000,2) AS ElapsedTotal,
       ROUND((elapsed_time_total/1000000)/executions_total,2) AS ElapsedPerExec
  FROM dba_hist_sqlstat JOIN dba_hist_snapshot
    ON dba_hist_sqlstat.snap_id=dba_hist_snapshot.snap_id
       AND dba_hist_sqlstat.instance_number=dba_hist_snapshot.instance_number
 WHERE sql_id IN (TRIM(:sql_id))
   AND executions_total       > 0
 ORDER BY DBA_HIST_SNAPSHOT.end_interval_time,


My doodles are available here.


The SQL Developer DBMS_OUTPUT/HTML report type should have enough capabilities for me to get a basic version of this report across. In my dreams I have a full D3.JS interactive zoomable pannable graph available, but at this point I want to get a solid proof of concept running.

The SQL Developer DBMS_OUTPUT/HTML report type will render basic HTML directly in a SQL Developer output window. The advantages are convenience and workflow, the disadvantages are that this rendering engine inside of SQL Developer is not as robust as a real browser. But I can do basic Font, Table, and CSS Styling to get my point across.


Too many to list here, but the main difference from the prototypes is making the format a HTML table of horizontal bars. For SQL Developer HTML output it is just easier for now.

I didn't find an easy way to dynamically assign SQL plans to colors so I came up with my own crude mapper that uses the plan hash value. I wanted to make sure I created something in an anonymous PL/SQL block that had zero footprint... And hey this is my v1 :) If I end up implementing something browser based in Javascript I have better and more reliable ways to do this color assignment.

v1 Result
Since perfection is the enemy of delivering, here is an example and code for v1 of my graph:

Code is available here: AWRVolatilityColorMap-v1.sql
Please give it a try as a SQL Developer "DBMS_Output" report or run in SQLPLUS and spool the output to a html file and open with a browser to view the results.
To Do List

  • Pay more attention to time scale - v1 just serially lists all snaps that the SQL_ID appears in and sorts by snap_end_time descending.
  • Implement in d3 somehow - more on that in a future article
  • Add scales to improve readability


Hi David,

I use sql developer report when plan has not changed.

Chart Type Bar - Vertical Stack Dual Y in sql developer using following sql:

(select to_char(trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24,' hh24:mi:ss') time,
sum(hss.executions_delta) executions,
round(sum(hss.cpu_time_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cpu_time,
round(sum(hss.iowait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) io_time,
round(sum(hss.clwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cl_time,
round(sum(hss.apwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) ap_time,
round(sum(hss.ccwait_delta)/1000000/decode(sum(hss.executions_delta),0,null,sum(hss.executions_delta)),3) cc_time
from dba_hist_sqlstat hss, dba_hist_snapshot hs
where hss.sql_id=:SQL_ID
and hss.snap_id=hs.snap_id
and hss.instance_number=hs.instance_number
and hs.begin_interval_time>=trunc(sysdate)-:DAYS_HISTORY+1
group by hss.instance_number, trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24
order by trunc(sysdate-:DAYS_HISTORY+1)+trunc((cast(hs.begin_interval_time as date)-(trunc(sysdate-:DAYS_HISTORY+1)))*24/(:INTERVAL_HOURS))*(:INTERVAL_HOURS)/24
UNPIVOT (Time_S FOR STATE_TIME IN (cpu_time AS 'CPU_TIME', executions AS 'EXECS',io_time AS 'IO_TIME', cl_time AS 'CL_TIME', ap_time AS 'AP_TIME', cc_time as 'CC_TIME'))


HI Vishal, thanks for the contribution.
I haven't used the double stack yet, I will have to check it out.
For benefit of readers here is an example output of your SQL as a SQL Developer Double Stack graph (click for larger version):

Add new comment