statsPackDemo.html
Dan Bikle

Statspack is a utility provided by Oracle to help you performance tune your database.

Some information about Statspack may be found here:

http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=statspack+site%3Aoracle.com&btnG=Google+Search

Before you use Statspack you need to set it up.

To set it up, login to sqlplus as SYSDBA and then run this script:

$ORACLE_HOME/rdbms/admin/spcreate.sql

The above script will create an Oracle user named PERFSTAT and then a variety of
database objects owned by PERFSTAT.

The concept behind operating Statspack is simple.  Think of it as
a mechanism which does two things.

First, it establishes points in time and assigns labels to each point.
Second, it allows you to look at detailed database performance
information between any two of the labeled points of time.

The jargon associated with establishing a point in time is the single
word "snapshot" which is a bit of a misnomer since you need at least
two snapshots to look at detailed database performance.  After you create
at least two snapshots, you  look at detailed database performance via 
a simple sqlplus report:

$ORACLE_HOME/rdbms/admin/spreport.sql

Before you create the snapshots and then run the report, you need to
set up Statspack via spcreate.sql.  A screen dump is provided below of
running spcreate.sql:

spcreate-sqlSD.txt

Once you are done running spcreate.sql you are ready to create snapshots
which is an easy task and is demonstrated below:

snapshotCreate.txt

After you are done creating at least two snapshots, you are in a
position to run the report.  A screen dump of running the report is
displayed below:

spReptSD.txt

If you want to automate the running of the snapshot procedure,
Oracle provides a nice demo script which sets up a job to run once
an hour:

$ORACLE_HOME/rdbms/admin/spauto.sql

Please remember to run the spauto.sql script as the perfstat user.

Many parts of a Statspack report are comprehensive to someone with
good technical common sense.  A discussion about all the parts of a
Statspack report can be a lengthy one.  URLs which contain discussions
about Statspack reports are listed below.

http://otn.oracle.com/deploy/performance/pdf/statspack.pdf
http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96533/instance_tune.htm#16849
http://www.nocoug.org/download/2002-11/statspack.ppt