What is STATSPACK and how does one use it?

Statspack is a set of performance monitoring and reporting utilities provided by
Oracle from Oracle8i and above. Statspack provides improved BSTAT/ESTAT
functionality, though the old BSTAT/ESTAT scripts are still available. For more
information about STATSPACK, read the documentation in file
$ORACLE_HOME/rdbms/admin/spdoc.txt.

Install Statspack:

cd $ORACLE_HOME/rdbms/admin
sqlplus "/ as sysdba" @spdrop.sql	-- Install Statspack -
sqlplus "/ as sysdba" @spcreate.sql	-- Enter tablespace names when prompted

Use Statspack:

sqlplus perfstat/perfstat
exec statspack.snap;			-- Take a performance snapshots 
exec statspack.snap; 
	 
-- Get a list of snapshots
select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; 

@spreport.sql				-- Enter two snapshot id's for difference report

Other Statspack Scripts:

• sppurge.sql - Purge a range of Snapshot Id's between the specified begin and end Snap Id's
• spauto.sql - Schedule a dbms_job to automate the collection of STATPACK statistics
• spcreate.sql - Installs the STATSPACK user, tables and package on a database (Run as SYS).
• spdrop.sql - Deinstall STATSPACK from database (Run as SYS)
• sppurge.sql - Delete a range of Snapshot Id's from the database
• spreport.sql - Report on differences between values recorded in two snapshots
• sptrunc.sql - Truncates all data in Statspack tables