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. Pingback: Performance Visualisation made easy « Phat4Oracle

  2. Ricardo says:

    Tanel,

    this work is amazing. I have just tested it in my production environment and it helped to solve a problem similar to the issue described by you !

    Thank you and Happy new year!

  3. tanelp says:

    Thanks Ricardo!

    It’s good to hear that my work has been useful for someone else as well :)

  4. Pingback: Log Buffer #129: a Carnival of the Vanities for DBAs

  5. Anand says:

    Hi Tanel,

    I am using Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
    When i am trying to use the PerfSheet.xls i am getting ORA-00942:table or view does not exist.
    As you have already mentioned “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” so i checked for it and found that DBA_HIST — table or view does not exist.What do i need to do.

  6. tanelp says:

    Hi Anand,

    10.2.0.1 should have those views installed by default in Enterprise Edition ( I haven’t checked Oracle Standard Edition, but you’re on EE anyway ).

    What user are you using for logging on? Can you log using the same username with sqlplus and run these queries:

    SQL> select count(*) from all_views where view_name = 'V_$ACTIVE_SESSION_HISTORY';

    COUNT(*)
    ----------
    1

    SQL> select count(*) from all_views where view_name like 'DBA_HIST%';

    COUNT(*)
    ----------
    78

  7. tanelp says:

    Also, you can try the “Sinus” subquery, it will access only dual, just to see whether PerfSheet is working in principle

  8. will says:

    Thank you for sharing. When I launch your tool, I have Visual Basic error message : “Missing project or library”. Is it due to the excel version ?

    Thanks for your help

  9. Tanel Poder says:

    Hi Will, which Excel version are you using and on which OS?

    I have tested this on Windows XP with Office 2003 and Office 2007.

  10. will says:

    Here are the versions :

    Excel : Excel 2000 (9.0.7616 SP-3)
    OS : Windows 2000 5.00.2195 Service Pack 4
    Oracle : 10g Enterprise Edition Release 10.2.0.3.0

  11. Tanel Poder says:

    I haven’t tested this on Excel 2000, so it’s probably because of the version.

    It *may* be something simple… When you get the error, are you presented with option “Debug”? If yes, then please send me on what code line this error happened. If it’s something simple to fix we may be able to fix it, but if not then it’s time for upgrade :)

  12. will says:

    Thanks for your reply.
    Yes the error occurs in sheet 1 (Front Page). The line highlighted is :

    DataRange = “$A$65536:” & Worksheets(ChartDataSource).Cells.Find(What:=”*”, After:=[$A$2], SearchDirection:=xlPrevious, SearchOrder:=xlByRows).Address()

    Many thanks in advance

  13. Tanel Poder says:

    Can you comment this line out (with a leading single quote) and use this just for testing whether the sheet would work in principle:

    DataRange = “$A$1:$F$100″

  14. Tanel,

    I wish I had this smart tool 7 years ago when I was needing something like that.
    I had a similar experience as yours, but instead of an HBA it was NFS. In my case, blames started at the dba level, passed by the network team, the system team, the storage guys, until I finally identified the root cause at the system level (solaris 8 kernel parameters).
    In these situations, I not only had tens of statspack reports, but also Solaris system reports, storage (NAS) head reports and network (switch) reports.
    To put all these people around a table and understand the problem/find a solution is very challenging.

    At the time I added some extra tables to the perfstat schema to store system/network/storage stats when taking a snapshot, then correlated the wait events to the other stats and put it on an excel chart.

    I can tell how much more you can say with images than with a thousand words :-)

    Thanks,

    Alexis

  15. Sridhar says:

    Hi Tanel,

    I was in your class today and I have excel 2000. When I try to run the perfsheet.xls I am not able to click the “SHOWME” option (=EMBED(“Forms.CommandButton.1″,””) Instead it is editable.

    Thanks

    Sridhar

  16. Tanel Poder says:

    Hi Sridhar!

    I have tested PerfSheet only on Excel 2002,2003 and 2007 on Windows platform (I don’t have Excel 2000 anymore)

    Are you in design mode in perhaps, there should be a button “design mode” in VBA control toolbox. If you’re in design mode then you can edit the form layouts etc, but if you’re out of the design mode then you can click on that button.

    However it looks like Excel 2000 has a problem with (at least) one line of my code. If you verify that you’re out of design mode and get this button clicking working then lets see if we can troubleshoot any other issues.

  17. Tanel Poder says:

    Alexis,

    Yeah, I wish I had had such tool some 7 years ago as well (just about when Excel 2002 was out on which PerfSheet runs well :)

    I would have saved quite many hours of my life with Perfsheet and saved myself from lots of boring mundane number-crunching work. But at least we’ve got it now! :)

  18. Sridhar says:

    I got my previous post problem resolved but when i click the showme button i am getting the following error

    Compile error variable not defined

    From the debug screen DefaultVersion:=xlPivotTableVersion10

    Thanks

    Sridhar

  19. Tanel Poder says:

    Try to change this to xlPivotTableVersion2000

    See this link for reference:
    http://msdn.microsoft.com/en-us/library/bb241438.aspx

  20. Sridhar says:

    Same error ( Compile error: Variable not defined)

    ActiveWorkbook.PivotCaches.Add( _
    SourceType:=xlDatabase, _
    SourceData:=ChartDataSource & “!” & DataRange _
    ).CreatePivotTable _
    TableDestination:=””, _
    TableName:=”Pivot” & ChartDataSource, _
    DefaultVersion:=xlPivotTableVersion2000

    Thanks

    Sridhar

  21. Tanel Poder says:

    Ok, just put 0 (zero) instead of the xlPivotTableVersion2000, 0 should mean excel 2000 pivot table type…

  22. Pingback: Performance Visualization, Capacity planning and Hotsos Symposium | Tanel Poder's blog: Core IT for Geeks and Pros

  23. Coskan says:

    Tanel,

    I am trying to add my own queries. I can run them but everytime I run my own queries I have to do drag drop for configuring chars. it does not save or keep my previous PData1 sheet configuration for the same view.

    Is there a way to keep my chart configuration

    I am awful with excel :(

  24. Coskan says:

    found the solution

    I did not put the query name for all columns :)

    thank you for this fantastic product

  25. Tanel Poder says:

    Yep, I want to add a “save view” button in version 3.0, but for now you’ve got to manually add this stuff to views page yourself…

  26. Balakrishna says:

    Hi Tanel,

    It Seems very good to analyze the performance but i am not able to use this. I request you to please throw some light how to use this perfsheet. I am not able to understand properly from where to start and analyze the performance in this sheet.

    Thanks and Regards

    Balakrishna.

  27. Tanel Poder says:

    Perfsheet is a simple easy to use data visualization sheet. Its up to you to visualize whatever SQL output you want however you want. So its not a tuning tool as such – but it allows you to visualize performance data if you want to. But its up to you to decide what data and how you want to see it.

  28. Pingback: Sydney Oracle Meetup #2: Visualizing Oracle Performance | Pythian Group Blog

  29. Pingback: ORA-04031 errors and monitoring shared pool subpool memory utilization with sgastatx.sql | Tanel Poder's blog: Core IT for Geeks and Pros

  30. Pingback: Diagnosing and Resolving “gc block lost” « Karl Arao’s Blog

  31. Pingback: Oracle Apps

  32. Satbis says:

    I am trying to use this spreadsheet but I am getting following error.
    Error – 2147467259: [Microsoft][ODBC driver for Oracle Runtime error '9' : subscript out of range excel

    Can you provide some hint how to resolve this error?

  33. Tanel Poder says:

    Hi Satbis,

    Which Excel version are you running?

    Which query are you trying to run?

    It looks though that there’s some problem with the ODBC driver or connectivity. Can you tnsping the database name youre trying to connect to from your PC?

  34. Pingback: Using Perfsheet and TPT scripts for solving real life performance problems | Tanel Poder's blog: Core IT for Geeks and Pros

  35. satbis says:

    Which Excel version are you running?
    – Excel 2003
    Which query are you trying to run?
    –> Test
    It looks though that there’s some problem with the ODBC driver or connectivity.
    Can you tnsping the database name youre trying to connect to from your PC?
    –> Yes. I created ODBC Datasource under User DSN and SYTEM DSn and none of them
    are working.
    I tested if the ODBC connection using a new excel spreadsheet then
    data –> Import external data –> New Database query .
    It can retrieve data using this connection.

  36. Tanel Poder says:

    Hi,

    You don’t need to create data sources manually ( i use dynamic data sources ) so you can just write the tnsnames.ora db alias in the data source box.

    Try another query – like the V$SEGMENT_STATISTICS Test.

  37. satbis says:

    Hi Tanel

    It didn’t work. I am still getting this error.

    Error – 2147467259: [Microsoft][ODBC driver for Oracle Runtime error '9' : subscript out of range excel

    Regards
    Satya

  38. Tanel Poder says:

    Hi Satya,

    Do you see another error message box just before getting this “runtime error 9″?

    Also – when you get this error – press “End” (not debug or cancel) after receiving the error, this should reset the execution state of vbs.

  39. satbis says:

    Hi Tanel

    I did that it still didn’t solve the problem. When I press debug . Debugger goes to this line

    With Worksheets(ChartDataSource)

    I checked the value of ChartDataSource it is showing NULL value. It seems it is getting NULL value and that is what is causing the error. I don’t know how to resolve this error.

    Regards,
    Satya

  40. Tanel Poder says:

    There may be something wrong with ODBC drivers I suspect. Can you try first on another computer?

    Which Oracle client version do you have installed?

    And lets look into this step by step:

    1) Please deliberately put a wrong password for a username – do you get an “invalid username/password” error first or still the same runtime error only?

    2) Can you paste me the contents of this command (run in cmd.exe):

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

    This should list the odbc drivers installed…

  41. Tanel Poder says:

    Also, deleta the three fields in Debug sheet, run the query again and paste me anything what has appeared on Debug sheet then

  42. Toli says:

    Hi Tanel,

    I have the same error:
    Error – 2147467259: [Microsoft][ODBC driver for Oracle Runtime error '9' : subscript out of range excel

    1. I have this error if I put a wrong password;
    2. c:\tolq\tmp
    > type c:\windows\odbcinst.ini | grep "\["
    [SQL Server (32 bit)]
    [ODBC 32 bit Drivers]
    [Microsoft Access Driver (*.mdb) (32 bit)]
    [Microsoft Text Driver (*.txt; *.csv) (32 bit)]
    [Microsoft Excel Driver (*.xls) (32 bit)]
    [Microsoft dBase Driver (*.dbf) (32 bit)]
    [Microsoft Paradox Driver (*.db ) (32 bit)]
    [Microsoft Visual FoxPro Driver (32 bit)]
    [Microsoft FoxPro VFP Driver (*.dbf) (32 bit)]
    [Microsoft dBase VFP Driver (*.dbf) (32 bit)]
    [Microsoft Access-Treiber (*.mdb) (32 bit)]
    [Microsoft Text-Treiber (*.txt; *.csv) (32 bit)]
    [Microsoft Excel-Treiber (*.xls) (32 bit)]
    [Microsoft dBase-Treiber (*.dbf) (32 bit)]
    [Microsoft Paradox-Treiber (*.db ) (32 bit)]
    [Microsoft Visual FoxPro-Treiber (32 bit)]
    [Driver do Microsoft Access (*.mdb) (32 bit)]
    [Driver da Microsoft para arquivos texto (*.txt; *.csv) (32 bit)]
    [Driver do Microsoft Excel(*.xls) (32 bit)]
    [Driver do Microsoft dBase (*.dbf) (32 bit)]
    [Driver do Microsoft Paradox (*.db ) (32 bit)]
    [Driver para o Microsoft Visual FoxPro (32 bit)]
    [Microsoft ODBC for Oracle (32 bit)]

    My environment:
    Windows XP Pack 3.
    Oracle 10.1.0.5.0.
    Excel 2002 SP3

    Thank you,
    Toli.

    P.S. Your blog is just fantastic. I learn a lot.

  43. Tanel Poder says:

    Hi Toli

    Hmm… I guess I need to build some better diagnosability into the perfsheet.

    Did you mean that when you have the wrong username/password you will get this “ODBC driver for Oracle Runtime error ’9′” error? Do you get the same with correct username/password?

    Can you try to run the “V$SEGMENT_STATISTICS test” query?

  44. Toli says:

    Hi Tanel,

    Yes, error is the same (’9′) regardless wrong or correct usename/password.

    I run ‘V$SEGMENT_STATISTICS test’ query and received the same error.

    I can send you snapshot if you like.

  45. Tanel Poder says:

    can you send me a screenshot to my email tanel@tanelpoder.com just in case i’m missing something.

    also do you have a chance to test this on another computer with excel 2003? I have tested it in past with excel 2002 and it worked but maybe some more recent improvements have broken something for 2002

  46. Pingback: UKOUG DBMS SIG July 2009 « Coskan’s Approach to Oracle

  47. Pingback: Great Expectations: An Interview with Tanel Poder « So Many Oracle Manuals, So Little Time

  48. Mel Y. says:

    I loved reading this and I dont really like to read :)

  49. Pingback: Advertisement: Be an Early Bird « So Many Oracle Manuals, So Little Time

  50. Pingback: Working with statspack-part-1a-Diagnosis « Coskan’s Approach to Oracle

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>