Oracle Performance Survival Guide - Scripts
Here you can obtain the scripts and examples from Oracle Performance Survival Guide by Guy Harrison.
An archive containing the full set of scripts can be downloaded here. Individual files can be downloaded below.
The archive contains both utility scriptsand all of the SQL files that I used to create various test loads while writing the book. The utility scripts are described in the index.html file contained within the archive. These scripts provide reports on various aspects of database health and performance.
The rest of the SQL files are provided as is, and without descriptions. You might find them useful if you are trying to replicate my workloads.
Some of the scripts will only work if you install some packages, views and permissions. In particular, the OPSG_PKG package provides an ability to generate short term delta and rate reports against the time model and wait interface. See installation, below, for instructions.
I hope you find these scripts - and my book - useful. If you have any problems please contact me at firstname.lastname@example.org.
Many scripts can be run without any installation providing that the user has access to V$ views. However, a few require specialized views and that the OPSG_PKG be installed. To install the package:
1. Open a command prompt within the "install" directory
2. execute either install_opsg.bat (Windows) or install_opsg.sh (Linux/Unix)
3. Respond to the prompts
Here is an example session:
C:\tmp\opsg\install>sqlplus /nolog @install_opsg
SQL*Plus: Release 22.214.171.124.0 - Production on Thu Sep 10 10:55:02 2009
Copyright (c) 1982, 2008, Oracle. All rights reserved.
Enter password for the (new) OPSG user:opsg
Enter SYS password:
Enter TNSNAMES entry:g11r2ga
The script creates a user OPSG which has privileges to run all of the scripts and which has appropriate permissions. You can modify the script if you want to install a different user. You do need the SYS password to install the user.
Extending the SH schema
I found the data volumes in Oracle's SH schema too low to illustrate some of the SQL tuning principles. The script extend_sh_schema.sql in the scripts directory will add about 2 million rows to the SALES and COSTS tables. These updates are applied directly to the SH schema.