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
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
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
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
Querying the historical STATSPACK table to
Extracting specific SQL statements from
Let’s begin by reviewing the Oracle STATSPACK
utility and review how STATSPACK collects and stores historical SQL
This is an excerpt from "Oracle High-Performance SQL Tuning" by
Donald K. Burleson, published by Oracle Press.