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:
- 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.
- 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.
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.
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.
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.
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. :)
For anyone who’s curious, the charts in this article are related to a SQL report which was recently discussed on the mailing list.
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!
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! :)