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.