SQL Tuning with STATSPACK Oracle Tips by Burleson Consulting

The Oracle STATSPACK utility was first introduced in Oracle8i, with a back-port to Oracle8. Introduced with little fanfare, the STATSPACK utility is one of the most powerful Oracle utilities for system tuning and for Oracle SQL tuning.

The tuning of individual SQL statements is the most time-consuming of all of the processes in Oracle tuning. While Oracle SQL tuning is a very time-consuming job, the tuning of SQL also promises the most benefits in the overall performance of the Oracle system. It is not uncommon to increase performance by an order of magnitude by using the proper Oracle SQL tuning techniques.

This chapter will also show how to use Oracle STATSPACK utility in order to monitor the behavior of SQL within your library cache and periodically alert the Oracle professional to SQL statements that may not be optimized for maximum performance. This is done by examining the SQL source in the stats$sql_summary table.

There is also a section on managing SQL statements within the library cache. As every Oracle professional knows, SQL statements are very transient within the Oracle instance and may only reside in the library cache for a short period of time. At any given point in time, information with the library cache may change. SQL statements that enter the library cache remain in the library cache until they age out and are no longer available to the Oracle instance. I will show you how to capture information from the library cache in a way that will build the foundation for using special scripts that will interrogate the library cache at a given point in time and prepare detailed reports showing the execution plans for all of the important SQL statements that are in the Oracle library cache.

The STATSPACK utility monitors the library cache information and can be used to create automated alert reports that will show whenever poorly tuned SQL statements are being executed within the Oracle instance.

The tuning of individual SQL statements will show how to change the execution plans with the use of SQL hints. We will examine actual examples for SQL tuning and show how the execution time of SQL statements can be reduced from hours down to only a few minutes.

In addition to storing system statistics, STATSPACK is also very useful for the tuning of Oracle SQL statements. Because STATSPACK stored all SQL statements that meet the snapshot selection criteria, you can create a historical record of all of the important SQL statements that were in the library cache at the time of the snapshot. Because most shops take STATSPACK snapshots hourly, you can quickly develop a complete picture of all historical SQL statements.

From this database of SQL statements, you can write STATSPACK extraction utilities to extract and re-explain all SQL statements after making changes to important Oracle initialization parameters or adding indexes. In this fashion, you can accurately predict the performance benefit of a SQL tuning change without impacting the production database.

This chapter contains the following topics:

• Setting the STATSPACK SQL collection thresholds

• Querying the historical STATSPACK table to extract SQL

• Extracting specific SQL statements from STATSPACK

Let’s begin by reviewing the Oracle STATSPACK utility and review how STATSPACK collects and stores historical SQL statements.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.