Performance Visualization made easy – PerfSheet 2.0 beta

Update: Luca Canali has written a newer version of Perfsheet (PerfSheet v4) and you can download it here. I am not updating/maintaining the old versions anymore (and Perfsheet v3 is only tested on up to Excel 2007) so I’d recommend to check out Luca’s PerfSheet v4.

Hi all,

I have been extremely busy over last couple of months, that’s why there haven’t been any blog entries (no, I haven’t ran out of good ideas ;-)
I just managed to find some time on a day-time 9-hour flight from Shanghai to Finland, thus here’s a blog entry about something what I had wanted to write about for long time… I’m talking about the PerfSheet tool I wrote a year ago and have been showing at few conferences already. It has been a great time saver for me over this year when working through performance data for troubleshooting or capacity planning tasks.

If you don’t want to read further through my comments, just download PerfSheet from http://www.tanelpoder.com/files/PerfSheet.zip and see how it works yourself!

For others, let me give some history first:

  1. A client had some serious performance problems after an HBA driver upgrade on a weekend.
  2. DBAs were blamed first and they started looking into Statspack reports (as that was the only performance history data they had).
  3. Current Statspack reports didn’t show anything too extraordinary (as everybody knew this database was running quite non-optimally anyway).
  4. By that time, both storage and Unix admins “had done the healthchecks and everything looked fine”
  5. But something was definitely different today as the end user response times were much worse.
  6. (This is a case where we didn’t care about general inefficiency of the application as this is what the application had been like all the time. Instead of systematically starting optimizing the database we just had to find what had gone wrong over the weekend and fix it. In other words, something had got F-ed up over the weekend and we needed to un-F it ;).
  7. Thus the troubleshooters (including me) started comparing the “current” statspack reports with previous ones to see what were the main differences in wait and load profiles were.
  8. But it wasn’t as easy as just taking last days snapshot from the same time as the load & wait profile of that system was fluctuating anyway as some heavily parallel batch jobs started and finished (at different times).
  9. Based on the statspack numbers the DBAs suspected that there was something wrong with IO configuration (especially as there had been a HBA driver upgrade over the weekend)
  10. Taking tens of 50-page statspack reports to a fingerpointing meeting with SAs, storage admins, high level directors etc didn’t work well…

Then I thought to visualize the numbers in Excel to get our point (and proof) through better.

Running Statspack reports manually and pasting numbers from there into Excel very boring, slow and error prone task. I actually started doing this but after 10 (painful) minutes I thought, heck, why am I doing all this manually?

This is how PerfSheet was born. I wrote the first version in couple of hours and it just ran an analytic query (downloaded the query from Tim Gorman’s site) against statspack repository. After fetching the SQL resultset to Excel, the sheet also automatically displayed the results in a graph (so we could refresh the graph after querying different stats out of statspack repository).

And armed with the wait profile graph above we were able to show hard evidence in a simple package (as opposed to tens of statspack reports) at the next meeting with storage and Unix teams. It’s very hard to argue with evidence like that, especially if managers are involved :) It’s much easier to discard a DBA with 500 pages of statspack reports blabbering about Oracle wait interface than someone who shows a visual overview of database health.

This led the Unix SAs to do another healthcheck and this time they found out that one of the two HBAs had been left deactivated after the driver upgrade. After the HBA was activated (online), the performance went back to normal.

So, sometimes, especially if multiple teams are involved in troubleshooting a sudden peformance problem (and there’s negative fingerpointing and protectionism going on), it’s hard to beat historical performance visualization. Instead of going through tens of pages of raw numbers you can just visualize that data and see if your eye catches a sudden change, trend or outlier.

And this leads me to version 2.0 (beta) of PerfSheet.

It allows you to do ad-hoc visualization of any SQL resultset. Excel runs your specified SQL statement, fetches data onto a worksheet and shows the results on a PivotChart. I have actually written only few lines of code to glue Excel’s SQL execution, data fetching and visualization modules together for convenience, Excel itself does most of the work.

When you open up the PerfSheet.xls, just fill in the connect details on the first page and click “Show me” and see what I mean (note that some queries do query v$active_session_history and DBA_HIST tables so make sure you have the proper licenses for querying these views).

Once the graph is displayed you can even drag and drop the data fields around into different axis to get a different breakdown of the statistics. Try it out and you’ll love it :)
Also, you can hover your mouse over some data element to get its description, as I’ve done here to see a SQLID matching an ASH sample on an ASH query:

As a next step, just navigate around at the Queries, Views and Help tabs and read the comments at column headers on how to define your own queries and visualization views. And if it doesn’t work, let me know.

Also, one more important thing. I used Excel for data fetching and visualization and I uset it on purpose. I haven’t seen a corporation yet where there is no Windows workstation and Excel available. Often I hear people saying that I should just use some Perl/Python/TCL/Java module or toolkit suth RRD etc as then you can put your graphs into web and “everybody has a web browser”.

I say that the mentioned technologies are for entirely different purpose. Yes, everybody does have a web browser, but In a corporation it’s not easy at all to get a (virtual) webserver for your purpose (and get all the software downloaded, configured and installed). When you have a serious production problem to troubleshoot, you don’t want to start installing and configuring webservers :)

The webserver approach is good for proactive, predetermined visualization, e.g. you know what you want to monitor and you make that data available in the intranet. But PerfSheet is perfect (thus the name :) for ad-hoc performance visualization of any SQL resultset on pretty much any corporate workstation.

So, Merry Christmas and happy visualization in the next year ;)

Note that this year’s only Advanced Oracle Troubleshooting class takes place in the end of April/May 2014, so sign up now if you plan to attend this year!

This entry was posted in Uncategorized. Bookmark the permalink.

72 Responses to Performance Visualization made easy – PerfSheet 2.0 beta

  1. ChrisvB says:

    This is cool. Thanks.

  2. Pingback: Oracle Peformance Visualization… | Tanel Poder's blog: Core IT for Geeks and Pros

  3. Pingback: Daily Roundup of News, Tips and Tricks for 2010-02-04 | Eddie Awad's Blog

  4. Hi Tanel,

    Nice! I like to visualize things with Oracle especially performance as it lends well to show clients and management.

    Cheers,
    Ben Prusinski

  5. Abhishek says:

    this is really good , I am facing some issue
    Related to

    ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:=ChartDataSource & “!” & DataRange _
    ).CreatePivotTable _
    TableDestination:=””, _
    TableName:=”Pivot” & ChartDataSource, _
    DefaultVersion:=xlPivotTableVersion10
    ————————————-
    1.both the table exist
    2.Version of Excel Microsoft Excel 2000 (9.0.7616 SP-3)

    type c:\windows\odbcinst.ini | find “[”

    [SQL Server (32 bit)]
    Driver=C:\WINDOWS\System32\SQLSRV32.dll
    Setup=C:\WINDOWS\System32\sqlsrv32.dll
    32Bit=1
    [ODBC 32 bit Drivers]
    SQL Server (32 bit)=Installed
    Microsoft Excel Driver (*.xls) (32 bit)=Installed
    Microsoft Text Driver (*.txt; *.csv) (32 bit)=Installed
    Microsoft Excel-Treiber (*.xls) (32 bit)=Installed
    Microsoft Visual FoxPro Driver (32 bit)=Installed
    Microsoft Visual FoxPro-Treiber (32 bit)=Installed
    Microsoft Paradox-Treiber (*.db ) (32 bit)=Installed
    Microsoft Access-Treiber (*.mdb) (32 bit)=Installed
    Microsoft ODBC for Oracle (32 bit)=Installed
    Microsoft dBase VFP Driver (*.dbf) (32 bit)=Installed
    Microsoft Text-Treiber (*.txt; *.csv) (32 bit)=Installed
    Microsoft Paradox Driver (*.db ) (32 bit)=Installed
    Driver da Microsoft para arquivos texto (*.txt; *.csv) (32 bit)=Installed
    Driver do Microsoft Excel(*.xls) (32 bit)=Installed
    Microsoft dBase-Treiber (*.dbf) (32 bit)=Installed
    Oracle Lite 40 ODBC Driver (client)=Installed
    Driver do Microsoft Access (*.mdb) (32 bit)=Installed
    Driver do Microsoft dBase (*.dbf) (32 bit)=Installed
    Oracle Lite 40 ODBC Driver (32 bit)=Installed
    Microsoft dBase Driver (*.dbf) (32 bit)=Installed
    Microsoft FoxPro VFP Driver (*.dbf) (32 bit)=Installed
    Microsoft Access Driver (*.mdb) (32 bit)=Installed
    Driver do Microsoft Paradox (*.db ) (32 bit)=Installed
    Driver para o Microsoft Visual FoxPro (32 bit)=Installed
    BMF MySQL ODBC Driver (32 bit)=Installed
    [Microsoft Access Driver (*.mdb) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odbcjt32.dll
    32Bit=1
    [Microsoft Text Driver (*.txt; *.csv) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odtext32.dll
    32Bit=1
    [Microsoft Excel Driver (*.xls) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odexl32.dll
    32Bit=1
    [Microsoft dBase Driver (*.dbf) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\oddbse32.dll
    32Bit=1
    [Microsoft Paradox Driver (*.db ) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odpdx32.dll
    32Bit=1
    [Microsoft Visual FoxPro Driver (32 bit)]
    Driver=C:\WINDOWS\System32\vfpodbc.dll
    Setup=C:\WINDOWS\System32\vfpodbc.dll
    32Bit=1
    [Microsoft FoxPro VFP Driver (*.dbf) (32 bit)]
    Driver=C:\WINDOWS\System32\vfpodbc.dll
    Setup=C:\WINDOWS\System32\vfpodbc.dll
    32Bit=1
    [Microsoft dBase VFP Driver (*.dbf) (32 bit)]
    Driver=C:\WINDOWS\System32\vfpodbc.dll
    Setup=C:\WINDOWS\System32\vfpodbc.dll
    32Bit=1
    [Microsoft Access-Treiber (*.mdb) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odbcjt32.dll
    32Bit=1
    [Microsoft Text-Treiber (*.txt; *.csv) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odtext32.dll
    32Bit=1
    [Microsoft Excel-Treiber (*.xls) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odexl32.dll
    32Bit=1
    [Microsoft dBase-Treiber (*.dbf) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\oddbse32.dll
    32Bit=1
    [Microsoft Paradox-Treiber (*.db ) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odpdx32.dll
    32Bit=1
    [Microsoft Visual FoxPro-Treiber (32 bit)]
    Driver=C:\WINDOWS\System32\vfpodbc.dll
    Setup=C:\WINDOWS\System32\vfpodbc.dll
    32Bit=1
    [Driver do Microsoft Access (*.mdb) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odbcjt32.dll
    32Bit=1
    [Driver da Microsoft para arquivos texto (*.txt; *.csv) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odtext32.dll
    32Bit=1
    [Driver do Microsoft Excel(*.xls) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odexl32.dll
    32Bit=1
    [Driver do Microsoft dBase (*.dbf) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\oddbse32.dll
    32Bit=1
    [Driver do Microsoft Paradox (*.db ) (32 bit)]
    Driver=C:\WINDOWS\System32\odbcjt32.dll
    Setup=C:\WINDOWS\System32\odpdx32.dll
    32Bit=1
    [Driver para o Microsoft Visual FoxPro (32 bit)]
    Driver=C:\WINDOWS\System32\vfpodbc.dll
    Setup=C:\WINDOWS\System32\vfpodbc.dll
    32Bit=1
    [Microsoft ODBC for Oracle (32 bit)]
    Driver=C:\WINDOWS\System32\msorcl32.dll
    Setup=C:\WINDOWS\System32\msorcl32.dll
    32Bit=1
    [Oracle Lite 40 ODBC Driver (32 bit)]
    32Bit=1
    Driver32=C:\first\product\10.1.3.1\OracleAS_2\Mobile\Sdk\BIN\olod2040.dll
    Setup=C:\first\product\10.1.3.1\OracleAS_2\Mobile\Sdk\BIN\oladcl2040.dll
    [BMF MySQL ODBC Driver (32 bit)]
    Driver=C:\Program Files\Quest Software\Benchmark Factory for Databases\Repository\MySQL\bin\myodbc3.dll
    Setup=C:\Program Files\Quest Software\Benchmark Factory for Databases\Repository\MySQL\bin\myodbc3S.dll
    32Bit=1

    Please Advise

    Regards
    Abhishek

  6. 弥勒佛 says:

    can you do this with your tool ? I mean as nicelooking … excel ouput seems not so good ( quality )
    http://www.flickr.com/photos/10907624@N02/4586372563/sizes/o/
    tell me
    regards

  7. Tanel Poder says:

    @弥勒佛

    Interesting looking screenshot. Not sure how practical though.

    So, where can one download your tool from? And is it completely flexible (run any SQL like in PerfSheet and slice/dice the data & view it from any angle like Excel’s pivotcharts allow you to?)

    If a chart looks realllly nice, it doesn’t automatically mean it’s gonna be very practical & useful! ;-)

  8. 弥勒佛 says:

    Hi
    thank you for you feedback
    actually its is pivotchart based …
    I am am neither using Excel nor using your code , and I don’t have a windows based system
    so I can’t use perfsheet , that’s all
    good work pal , but try to post better screenshots on your log or a screencast , would be GREAT !
    regards

  9. 弥勒佛 says:

    so basically very flexible , any sql can run against the db with slicing/dicing capabilities
    unfornunately I don’t have a weblog , so no download
    pretty much perfsheit little brotherl

  10. Pingback: Graphing the AAS with Perfsheet a la Enterprise Manager « Karl Arao's Blog

  11. Fernando says:

    I cannot find the perfsheet. Can someone show me where it is located?

  12. Vivek K says:

    Tried to download the excel file. Seems that the link is broken. Where can I find the file ?

  13. cam says:

    Looks great but having some trouble… PerfSheet v3.1 public.xls in Excel 2007 SP2.. Seems to connect fine (I can see an open EXCEL.EXE session) and the query returns results when I try directly form sqlplus with the same user…

    But from the spreadsheet, there is some flickering after I hit ‘Show Me’, I can see ‘Reading Data..’ count up on the Excel status bar and then the tab changes to ‘Queries’ and that’s it.. no charts, nothing…

    Anyone else seen this?

    cam

    • cam says:

      Further developments.. having now enabled all the tickboxes on the front page, I am getting results! As for people above, however, the sheet is failing with ‘Runtime Error 9 – Subscript out of range’ when I enable Debug VBA on error. This means the data is returned but no charts are drawn… I can manually create my own pivot tables and charts, but it would be interesting to see how these had been designed… Also, most of the queries seem to return the snap_id instead of a date – the latter is much more useful!

      Anyway, thanks!

  14. Pingback: DB Optimizer » ASH Visualizations: R, ggplot2, Gephi, Jit, HighCharts, Excel ,SVG

  15. Arun says:

    from where can i download this.. the link seems to be broken.

    thanks
    arun

  16. max says:

    Hi Tanel,

    Do you have the original perfsheet version 2.0? There are some query that used to be in 2.0 and i cant see to find them in 3.0? Would you have that download avaiable ? I tried searching on your site and on google but cant seem to fine version 2.0 ?

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>