/PerfSheet4

PerfSheet4 is a tool for performance troubleshooting of Oracle databases. Query and visualize Oracle AWR data using pivot charts.

GNU General Public License v3.0GPL-3.0

PerfSheet4 - A tool for Oracle RDBMS performance analysis

PerfSheet4 provides a graphical interface to extract and visualize Oracle workload and performance metrics using MS Excel as a front end. Oracle metrics are extracted from AWR repository (note this requires the diagnostic pack licensing). Metrics are visualized using Excel pivot charts.

Author: Luca.Canali@cern.ch
Latest minor updates: October 2019
Created: March 2013, updated June 2016

Compatibility:

  • tested using Excel 365 and Excel 2016 on Windows 10
  • tested with Oracle 19c and 11g

Note: an alternative version of this tool, that runs in the browser (so does not require Excel) is PerfSheet.js

Screen shots:

Example PerfSheet4 interface

Example PerfSheet4 PivotChart

Get started:

  • download the repo and open PerfSheet4_v3.7.3 in Excel
  • get started with the provided examples:
    • use "Load" to import csv files from the example_data folder
    • use "Plot" to display data and experiment with the pre-defined graphs
  • run it on you Oracle DBs: gather AWR data and generate the plots:
    • option 1: run the AWR data extraction queries from Excel.
      For this you will need the Oracle instant client and ODBC driver installed on the machine running PerfSheet4
      • to install the ODBC driver, download from Oracle instant client downloads
      • run odbc_install.exe (note you will need a 64 bit odbc if you use Excel 64 bit).
      • configure "ODBC name" in the PerfSheet4 interface. Note, you can find the ODBC name using the ODBC Data Source Administrator in Windows: go to ODBC Administrator -> drivers -> look for the installed Oracle ODBC driver name
    • option 2: extract AWR data from your DBs into csv files using the sqlplus scripts provided
      • browser the sqlplus_scripts directory and run the script/scripts of interest
      • from the PerfSheet4 interface, use "Load" button to load the csv file for visualization with PerfSheet4.
  • Multiple DBIDs
    • You will need to do some (simple) updates if your AWR repository has multiple DBIDs (I typically work with only 1 DB per AWR repository). If you look at the scripts you will find commented out "--ss.dbid, --uncomment if you have multiple dbid in your AWR"
  • PDBs
    • Oracle container databases are currently not supported, it's a matter of improving the scripts and is in the TODO list.
  • Getting started video: http://youtu.be/sdvx4zB-fvo

Blog entry: https://db-blog.web.cern.ch/blog/luca-canali/2015-02-latest-updates-perfsheet4-tool-oracle-awr-data-mining-and-visualization

Contents:

Script name Short description
PerfSheet4_v3.7.3 Main version of PerfSheet4. Tested with Excel 365 and 2016 on Windows 10.
more/PerfSheet4_v3.7.3_noActivex_Controls A lightweight version of PerfSheet4 modified by removing Activex Controls. This makes it easier to run on older versions of Excel and/or if there are compatility problems with Excel Activex controls.
more/PerfSheet4_v3.7.2_HPS Customized version by Hans-Peter and Mark Sloot. This version adds a filter mask to the UI. It also adds three more pre-defined queries, including a query for Statspack.
sqlplus_scripts Folder with example SQL scripts to extract data from AWR into csv files.
example_data A few example csv files with AWR data.