Statspack basics

Can you explain what is Statspack and how to use it?

Statspack is a set of tools that captures and reports on database statistics. It is included with the Oracle9i server software and is implemented as a set of SQL scripts in the $ORACLE_HOME/rdbms/admin directory. (Note that Statspack has been replaced by the Automatic Workload Repository in Oracle10g).

You set up Statspack by running the SPCREATE.SQL script, which creates the Statspack schema owner and objects.

You then take a "snapshot" of current statistics by executing the procedure STATSPACK.SNAP. After a period of time of your choosing, you take another snapshot, then run the script SPREPORT.SQL, which produces a report showing the statistics attributable to the time between the snapshots, by computing the delta of the two sets of statistics.

Some DBAs take Statspack snapshots periodically, and detect trends in statistics by viewing reports for each interval. Another application of Statspack might be to see the statistics produced by a certain operation or load profile. Statspack is not a substitute for analysis of individual session trace files, but it can be useful in revealing an Oracle server's overall resource-use profile.

Dig Deeper on Oracle database performance problems and tuning

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.