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.

Research
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
HAVING COUNT(*) > 1
ORDER BY 2 DESC;

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,
       sql_id,
       plan_hash_value,
       ROUND(elapsed_time_total/1000000,2) AS ElapsedTotal,
       executions_total,
       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,
       DBA_HIST_SNAPSHOT.INSTANCE_NUMBER

Prototypes

My doodles are available here.

Construction

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.

Refinement

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