Note added: An update to this post with the details of a more recent version of PerfSheet4 can be found at this link. See also the work on PerfSheet.js.
Context: There is much information in the counters and metrics of Oracle's AWR that can be of substantial help for troubleshooting and for capacity planning. Besides the standard AWR report, time-based analysis is often very useful. However this type of access is made slow and tedious by a list of tasks, such as, fetching data, calculating incremental changes and producing interesting graphs.This normally requires developing or downloading a bunch of scripts and adding some visualization techniques on top. See for example Karl Arao's scripts and blog.
Tanel Poder has already demonstrated a few years ago that the pivot chart functionality of Excel can be an excellent platform for performance data visualization including AWR (and Perfstat) data. See his work on Perfsheet 2.0 and PerfSheet3.
What's new: The main drive to develop PerfSheet4 was to (try to) make the user interface as simple as possible, without compromising the general nature of the tool. A set of pre-defined queries for some common AWR analysis come with the tool. The plotting functionality will generate custom graphs for such data, an easy entry point to analysis.
If direct access to the DB is not possible, data can be extracted from the DB with the scripts supplied in the sql*plus_scripts directory and then loaded as csv. Similarly, export to csv can be used to archive data for later analysis.
PerfSheet4 is a free tool and is available for download from GitHub or download from this link. The tool has been developed and tested on Excel 2010 against Oracle 11gR2. See here below a snapshot of the user interface with some additional details.
Why Excel: Pivot tables and charts are a great feature available in Excel that allows to quickly and easily analyze multidimensional data. This can be of great help when troubleshooting problems in production, for example with the need to try out different ideas and data combinations
Why pre-defined graphs: This feature is again to make life easier for the user. The idea is to automates the tedious task of setting up the pivot chart fields and provides an entry point to get to the needed graph.
Available queries: version 3.2p beta comes with the following queries (and pre-defined graphs):
| Query name name | Short description |
|---|---|
| Wait events | Extracts data from dba_hist_system_event, computes delta values between snapshots and rates (i.e. delta values divided over delta time) |
| System statistics | Extracts data from dba_hist_sysstat, computes delta values between snapshots and rates (i.e. delta values divided over delta time) |
| Stats per service | Extracts data from dba_hist_service_stat, computes delta values between snapshots and rates (i.e. delta values divided over delta time) |
| Workload data | Extracts data from dba_hist_sysmetric_summary, metrics give details on the usage of system resources over time ranges |
| IO wait latency | Extracts data from dba_hist_event_histogram for io related events, computes delta values between snapshots and rates (i.e. delta values divided over delta time) |
| Top 5 wait per instance | Extracts data from dba_hist_system_event and dba_hist_sysstat, computes delta values and rates (delta value over delta time) and selects top 5 non idle events for each instance |
| Wait events per class | Extracts data from dba_hist_system_event, computes delta values and rates (delta value over delta time) aggregating over wait class |
Future work: Stabilize Perfsheet4 against bugs and for different execution environments. For this feedback from other users would be very much appreciated. Possibly add more pre-defined queries (for AWR analytics but not necessarily only that). Add a post with examples of how analysis performed with PerfSheet4 have been useful for my job in various occasions. In the mean time if you find the tool useful or find bugs or have ideas for possible improvements, I'll be interested to know! Acknowledgments: Many thanks to Tanel Poder, the co-author of the tool.
Getting started video: http://youtu.be/kC0EJCLZWIw
Great !!!
ReplyDeleteHi Luca,
ReplyDeleteI have the Oracle Client and Microsoft ODBC for Oracle installed in my Windows 7 (64bits). I don't know why I am receiving the message "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
Thanks
Legatti
Hi Luca,
ReplyDeleteI noticed that my Excel is 64 bits, my Windows is 64 bitts, but my Oracle Client is 32 bits. I know that the problem is the ODBC driver (64 bits vs 32 bits). Do you know how to solve this issue?
PS: I did a test in another machine Windows XP 32 bits and the perfsheet worked well.
Thanks
Legatti
Hi Eduardo, thanks for the update. I am afraid I don't have enough experience on this, however it looks like you are already on the right track. I can just take a guess and suggest that you try using Oracle client 64 bits or else do a change on the Office side and use Excel + ODBC drivers 32 bits.
DeleteThis comment has been removed by the author.
ReplyDeleteHi Luca, how are y ?
ReplyDeleteIt was a very great job. This upgrade of Tanel's perfsheet is very cool.
I did some modifications in your perfsheet to help me to configure a snapshot time more quickly and also I modified the search engine because the snap statistics values are stored in ending of snapshot time at least for me based on some studies.
See, this post talks about one specific VIEW but in some studies that I did, this applied to others : http://shallahamer-orapub.blogspot.com.br/2012/04/when-are-dbahistsysstat-values-correct.html
Send me your email and I send the perfsheet4 with the modifications and you can see if you like.
Again, continue this great work, I'm wait for the next. What about develop something to Statspack ?. Count with me if you need some help.
Tks
Wellington Prado
www.wellingtonprado.com
Hi Wellington, thanks for your comments, feel free to send your version of perfsheet.
Delete