Making Simple Performance Charts

Before I dive into this blog post, quick heads up for anyone attending UKOUG: on Tuesday only, I’ll be hanging out with some very smart people from the IOUG RAC Special Interest Group in the “gallery” above the exhibition hall. We’re ready to help anyone run a RAC cluster in a virtual environment on their own laptop. And if your laptop doesn’t meet the minimum requirements then you can try with one of our demo workstations. Come find us!!

Why Make Charts

I’ve heard Kyle Hailey speak on a few different occasions, and more than once he’s talked about the power of visualizing data. (In fact Kyle was a key person behind Grid Control’s performance screens.)

I couldn’t agree more. I regularly visualize data when I’m working. Two reasons come immediately to mind:

1. It helps me to better understand what’s happening. There have been times when I’ve had an “aha” moment very quickly after seeing the picture.
2. It helps others more easily understand what i’m trying to communicate. It’s great for management reports and such – not because it’s fluff, but because it’s a good communication tool.

Last week, I made a few quick charts as illustrations for a performance report. The process really isn’t that complicated, but I thought I’d put the steps into a blog post… for myself to reference in the future and for anyone else who might find this helpful. :)

Making Simple Charts

This demonstration will use data from the AWR to build graphs. Note that if you run these queries, Oracle legally requires you to purchase the extra-cost “diagnostic pack” license. But similar queries could be written from free statspack or S-ASH tables.

You need multiple data points to make a graph. For this demo, The AWR was configured to take snapshotsd every 30 minutes and I’m looking at a query which ran for about 10 hours. Also, it was the only query running in the instance for most of that time – so I will also look at some instance-wide statistics.

1. The first step is to get any needed parameters for pulling performance data. In the case of the AWR, I will need the INSTANCE_NUMBER, the SQL_ID and the first/last SNAP_ID.

   It’s pretty easy to get this information from Grid Control or from Database Console. But if you don’t have access to the web console then you can still get the info from SQLPlus.

   Here’s a useful query to get an overview of the SNAP_IDs:

   SQL> select to_char(BEGIN_INTERVAL_TIME,'MON YYYY') month, 
          min(snap_id) min_snap, max(snap_id) max_snap
        from dba_hist_snapshot 
        where instance_number=4
        group by to_char(BEGIN_INTERVAL_TIME,'MON YYYY');
   
   MONTH        MIN_SNAP     MAX_SNAP
   -------- ------------ ------------
   APR 2009        10239        10240
   DEC 2009        28752        31924
   FEB 2010        38115        40939
   MAY 2010        47498        48783
   AUG 2010        54975        55013
   NOV 2010        60979        61986
   DEC 2010        61987        64218
   JAN 2011        64219        66448
   FEB 2011        66449        67803
   MAR 2011        67804        69291
   APR 2011        69292        70731
   MAY 2011        70732        72219
   JUN 2011        72220        73655
   JUL 2011        73656        75139
   AUG 2011        75140        76608
   SEP 2011        76609        78048
   OCT 2011        78049        79536
   NOV 2011        79537        80338
   
   18 rows selected.

   Something similar to this might also be useful:

   SQL> select snap_id,instance_number,begin_interval_time,snap_level 
        from dba_hist_snapshot
        where begin_interval_time between '11-nov-11 17:30' and '11-nov-11 19:00'
          and instance_number=4
        order by snap_id, instance_number;

   For this demo I’m going to use INSTANCE_NUMBER 4 and SQL_ID 8suhywrkmpj5c between snaps 80298 and 80318.

2. Now create a new spreadsheet in your office suite. I use the free OpenOffice spreadsheet application, but Excel or iWork Numbers should work pretty much the same.

   In the second row of the new spreadsheet, enter the time of the first snapshot you’re going to analyze. In the third row, enter this formula:

   = A2 + 1/24/60 * [minutes between snaps]

   Select several rows below this formula and select Edit > Fill > Down to copy the formula to the following rows. Repeat this until you have reached the end of your analysis window.

   

3. Open a SQLPlus session. We will copy-and-paste directly from SQLPlus into the spreadsheet.

   Use a SQL like this to retrieve data for one system statistic:

   set pagesize 999
   col value format 999999999999999
   
   select value from dba_hist_sysstat
    where instance_number=4 and snap_id between 80298 and 80318
      and stat_name='transaction tables consistent read rollbacks'
    order by snap_id

   You can copy the statistic name directly from an AWR report of there’s a certain stat you’re interested in. You can find more information about system statistics in Oracle’s docs.

   Now move right to the next empty column. First, copy the name of this statistic into the first row. Then, in the second box, enter a formula to find the difference between its left peer and the left upper peer. For cell C3, the formula is B3-B2. Choose Edit > Fill > Down again, as before.

   = B3 - B2

   

   You can repeat this step to access further system statistics. You can also create another column where you divide or multiply each other.

   

4. The previous SQL statement retrieved system statistics. Another easy query runs against historical V$SQLSTAT snapshots. (This only works for long-running queries.)

   SQL> select BUFFER_GETS_TOTAL value from dba_hist_sqlstat
        where instance_number=4 and snap_id between 80298 and 80318
          and sql_id='8suhywrkmpj5c'
        order by snap_id

   Once again, you can read about the available fields and statistics in the oracle docs. You can repeat this step to quickly get additional statistics for a particular SQL, and you can then combine some stats for better graphs.

   

That’s it! I know, really not that complicated. I hope it’s helpful. :)

Post Script

For anyone who’s curious, the charts in this article are related to a SQL report which was recently discussed on the mailing list.