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 Execution Plan volatility. When researching performance problems I often need to quickly digest historical performance of a trouble statement.
Luckily Oracle 10g/11g/12g have this type of information in the Active Workload Repository.
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.
ResearchTo 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 OrganizationDBA_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
PrototypesMy doodles are available here.
ConstructionThe 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.
To Be continued...