Why PerfSheet4: PerfSheet4 is aimed at querying and displaying time-series data from AWR repository tables. This is very a rich source of information to analyze database workloads and trends in the context of performance analysis or capacity planning. The tool automates several time consuming tasks and provides a point-and-click user interface:
- PerfSheet4 comes with a set of pre-defined queries to extract data from some of the most common AWR table and in particular to compute deltas for the performance values were relevant.
- PerfSheet4 can query the database directly or work in a offline mode (you run the sql script from sqlplus and later load the csv file into PerfSheet4)
- PerfSheet4 uses Excel Pivot Charts. Pivot Charts provide a powerful interface to display and navigate through AWR data.
- PerfSheet4 comes with a set of pre-defined graphs for each of the pre-defined queries. This makes preparing some of the most common workload graphs a matter of a few clicks and also provides a good starting point into more advanced usages of the Pivot Charts.
What's new: If you have used previous versions of PerfSheet4 you will find in v3.7 (February 2015 update) a larger collection of pre-defined graphs. This is aimed at making PerfSheet4 easier to use for many of the standard tasks. Typically 4 graphs will be presented for each of the pre-defined queries. Another new feature is the choice of using Oracle ODBC drivers, as opposed to using the default Microsoft ODBC driver for Oracle. This feature has been introduced especially for Windows 8/8.1 where Microsoft ODBC driver for Oracle does not seem to work correctly.
PerfSheet4 is a free tool and is available for download from GitHub or download from this link. The tool has been developed and tested for Oracle 11gR2 and 12cR1. On the client side it has been tested for Excel 2010 and 2013 for Windows (7 and 8/8.1). The figures here below show two examples of the user interface. See also the work on PerfSheet.js.
 |
| Figure 1: The start page and user interface of PerfSheet4. This is where you can run the query of interest to extract data from AWR. or load csv data into Excel. From this page you can then launch the Pivot Chart plot. |
 |
| Figure 2: A Pivot Chart generated by PerfSheet4. The pre-defined graph is just a starting point. Customize the chart from there by selecting the data of interest and/or changing any other feature of the graph. |
Additional details: Here below is the list of AWR queries and charts available in PerfSheet4, v3.7 (February 2015). The tool is open source and therefore fully configurable, in case you want to modify his default behavior. There are two versions packaged in the zip file of the tool: the main version is called called PerfSheet4_v3.7.xlsm. A second version is available, called PerfSheet4_v3.7_noActivex_controls.xlsm, which avoids the use of Activex controls and should be better suited for those cases where Activex controls don't work well and/or for older versions of Excel.
Query: Workload data
- Extracts data from dba_hist_sysmetric_summary. System metrics provide details on the usage of system resources over time.
- Pre-defined charts: "Average active sessions", "CPU usage and database time", "Physical reads and writes IOPS", "Physical reads and writes throughput".
Query: System statistics
- Extracts data from dba_hist_sysstat. Hundreds of system statistics provide valuable details on the workload. The query computes delta values between snapshots and rates (that is delta values divided over delta time).
- Pre-defined charts: "Physical read and write IOPS", "Physical read and write throughput", "CPU and wait time", "CPU used per instance".
Query: Wait events
- Extracts data from dba_hist_system_event. This provides information on all the wait events. The query computes delta values between snapshots and rates (i.e. delta values divided over delta time).
- Pre-defined charts: "db file sequential read - wait event for single block reads", "db file scattered read - wait event for multiblock reads into the buffer cache", "log file sync - wait event for commit time".
Query: Wait events per class
- Extracts data from dba_hist_system_event. This provides information on all the wait events, computes delta values and rates (delta value over delta time) aggregating over wait class.
- Pre-defined chart: "Wait time grouped per class".
Query: IO stats details
- Extracts data from dba_hist_iostat_detail. This provides details on the I/O operations performed by Oracle, computes delta values and rates (delta value over delta time) aggregating over function and file type.
- Pre-defined charts: "Small read IOPS", "Large read throughput", "Small write IOPS", "Large write throughput".
Query: IO wait histograms
- Extracts data from dba_hist_event_histogram for I/O related events. This provide drill-down information on latency. The query computes delta values between snapshots and rates (i.e. delta values divided over delta time).
- Pre-defined charts: "db file sequential read latency histogram - number of wait events", "db file sequential read latency histogram - time waited (millisec/sec)", "commit time wait latency histogram - number of wait events per sec", "commit time wait latency histogram - time waited (millisec/sec)".
Query: 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) aggregating by service name.
- Pre-defined charts: "DB time", "CPU time", "User I/O wait time".
Query: Top 3 wait events and CPU
- Extracts data from dba_hist_system_event and dba_hist_sysstat. This query selects the top 3 non-idle wait events and the CPU usage for each instance. It computes delta values and rates (delta value over delta time).
- Pre-defined charts: "Instance N.1 - top 3 wait and CPU", "Instance N.2 - top 3 wait and CPU", "All instances - top 3 wait and CPU".
Previous work and acknowledgments: PerfSheet4 is a spin off of previous original work by Tanel Poder, rewritten and integrated with additional functionality for AWR analysis and with important changes to the user interface with the goal of making it more 'point-and-click'. A previous post on PerfSheet4 can be found at this link. Many thanks go to Tanel for his original work on PerfSheet and for his support to this development.
Getting started video: http://youtu.be/sdvx4zB-fvo