Oracle, Technical

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.

About Jeremy

Building and running reliable data platforms that scale and perform. about.me/jeremy_schneider


2 thoughts on “Making Simple Performance Charts

  1. Jeremy, I totally agree with you about the importance of charting data. I’m curious about any need to add formulas to a spreadsheet in support of data generated from Oracle. I’ve found that you can code nearly all you need within a cursor so it’d be just one cut-paste into a spreadsheet and you’re done. This includes generating trend lines. Am I missing something?

    Of course you can take it to the next step and use a link from the spreadsheet to the db, save canned queries in the spreadsheet and then generate and graph at the same time. This is what Tanel Poder’s Perfstat spreadsheet does for you.

    Or if on Linux you could use gnuplot and generate charts right on the server and email them.

    Thanks again for the article!


    Posted by Dave Herring | November 30, 2011, 1:41 pm
  2. Hi Dave!

    The reason I used a spreadsheet was that I didn’t know ahead of time which charts or formulas I wanted. I was investigating a performance problem and wanted to quickly and easily visualize many combinations of the data. I quickly copied about 30 metrics or so into columns in the spreadsheet and then started adding various formulas to combine them. Then I could very quickly create a number of charts with subsets of the stats and formulas.

    If you wanted to generate one specific graph on a regular basis, then cursors or gnuplot would be great. Tanel’s perfsheet spreadsheet actually looks really cool – I didn’t know about that one. In this particular case, I was doing some remote consulting work and I didn’t have a direct connection to the database… only a terminal window. But I’ll have to play with Tanel’s spreadsheet; looks like a better solution for cases with direct database access.

    Thanks for stopping by! :)


    Posted by Jeremy Schneider | November 30, 2011, 3:26 pm


This is my personal website. The views expressed here are mine alone and may not reflect the views of my employer.

contact: 312-725-9249 or schneider @ ardentperf.com




Enter your email address to receive notifications of new posts by email.

Join 56 other subscribers
%d bloggers like this: