While the main focus of this text is on practical techniques for Oracle tuning, you'll find its STATSPACK is used as a common thread throughout the body of this text. Using STATSPACK as a vehicle for aiding performance tuning is a very important concept that will be the main focus of the chapters in this book. While you can back-port STATSPACK to Oracle 8.0 releases by using the statsrep80.sql report, there are some known problems with the utility. The report from STATSPACK on an Oracle release level prior to 8.1.6 has inaccuracies with the data and cannot always be trusted. The STATSPACK table structure also changed between 8.1.7 and Oracle9i, and a migration procedure for STATSPACK is detailed in Chapter 3.

We will begin in Chapter 2 with an overview of the Oracle9i STATSPACK utility and show how Oracle STATSPACK is installed and configured in order to measure Oracle server metrics. We will show the installation procedure for the STATSPACK tables, the architecture of all of the STATSPACK tables, and a complete listing showing you where to find all of the salient server metrics that you need in order to pull useful information from the STATSPACK tables.

We will also take a look at the nature of STATSPACK snapshots, and show how to create reports that average metrics over time, as well as look at specific time intervals, spanned over long periods of time. This is an especially important technique because it shows the Oracle professional how to slice the STATSPACK information in order to get exactly what they are looking for.

We will take a look at sample queries that can be run against the STATSPACK tables and learn how we can get relevant metrics from STATSPACK to identify what was happening in our database at any given point in time.

While this text will utilize prewritten STATSPACK scripts, it's very important for the Oracle professional to understand the nature of STATSPACK queries and be able to formulate their own queries based upon the specific problem that they encounter within the Oracle environment. There are many dozens of scripts that come as a part of this text, but the Oracle professional will always be encouraged to extend upon these reports and use them to see additional details about their system.

Because the STATSPACK utility is limited to the domain of the Oracle database, we will show some of the ways that Oracle STATSPACK can be extended to capture information from the external environment. We will begin by showing how STATSPACK can be extended to capture server metrics, and we will devote Chapter 5 to exploring how the UNIX vmstat utility can be used to capture server information and store them in STATSPACK extension tables. We will also show how the Oracle professional can interrogate these tables and see what's going on inside the database server when performance problems occur.

We will also extend the STATSPACK tables by capturing information on disk I/O. We will devote Chapter 8 to the tuning of the disk I/O subsystem so that the Oracle professional can understand the nature of disk I/O, the interaction between Oracle file placement and disk I/O, and how STATSPACK can be extended to monitor what's going on in the I/O subsystem over time.

Towards the end of this book we will also take a look at the extension of STATSPACK towards long-term statistical trend reporting. In Chapters 14 and 15, you'll find dozens of useful scripts that allow you to plot important Oracle metrics over time and use the existing statistics as a predictive model for developing a plan for additional database resources. While it is not necessary to have a background in statistics to fully understand the techniques in Chapter 15, it is important the Oracle administrator have a general knowledge of the Oracle performance metrics and how they may change over time.

When doing Oracle trend analysis with STATSPACK, it's important to understand that we will be aggregating information along many dimensions. For example, we will use Oracle scripts that will show you how to aggregate information by day of the week, so that you can see the relative stress on your system during specific days of the week, as shown in Figure 1-11.

Figure 11: A day-of-the-week average report

We will also show how to break down Oracle STATSPACK information by hour of the day, so the Oracle professional can see on an hourly basis when end-user processes are causing stress on the Oracle database, as shown in Figure 1-12.

Figure 12: An hour-of-the-day average activity summary

We will use the same techniques for the measurement of daily and hourly information to extrapolate the overall performance of the database over periods of weeks and months. By taking summarized STATSPACK information and feeding it into statistical analysis routines, we can use predictive models to perform linear regression and predict the future performance of the Oracle database based upon the existing metrics.

We will also briefly cover the different methods for linear regression, including single, double, and triple exponential smoothing techniques as well as the more common sum-of-the-least-squares techniques for predicting overall Oracle performance.

These long-term trend reports are especially useful for the manager who is charged with predicting the amount of hardware resources required for the Oracle systems. We will show scripts that will accurately predict future disk storage requirements, future RAM memory requirements, as well as future CPU requirements—all based on statistics gathered from the STATSPACK tables over time.

For plotting STATSPACK information, it is important that we use a tool that is available to almost all Oracle administrators. While a powerful statistical package such as SAS or SPSS can give incredibly detailed analysis of statistics, we've chosen in this book to use the Microsoft Excel worksheet product. By confining our examples to MS Excel, we can provide easy to understand methods for extracting, plotting, and predicting the behavior of our database, all using tools that are readily available on most desktops as shown in Figure 1-13.

Figure 13: A sample of a predictive STATSPACK trend analysis using MS Excel

Because STATSPACK has been configured to capture virtually everything that's going on in an Oracle database at any given point in time, you'll find that there are many hundreds of metrics that are useful when captured within the STATSPACK tables. While it is very tempting for this author to cover every single one of these metrics, it's more important to the practicing Oracle professional that we focus only on those server metrics of the most importance for the tuning of the Oracle system. Hence, we will confine our discussion to those metrics that will provide the Oracle professional with the information they need to very quickly make intelligent performance and tuning decisions for their system.