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.

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

To Be continued...

Comments

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 *
FROM   
(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,'dd.mm.yyyy 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'))

Thanks,

Vishal

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