Install statspack
cd $ORACLE_HOME/rdbms/admin sqlplus "/ as sysdba" @spdrop.sql -- Drop and install statspack sqlplus "/ as sysdba" @spcreate.sql -- Enter tablespace names when prompted
Take performance snapshots of the database
sqlplus perfstat/perfstat exec statspack.snap; -- Take a performance snapshots -- or exec perfstat.statspack.snap(i_snap_level=>10); -- or instruct statspack to do gather more details in the snapshot -- (look up which oracle version supports which level)
The spauto.sql script can be customized and executed to schedule the collection of STATPACK snapshots.
Statspack reporting
-- Get a list of snapshots select SNAP_ID, SNAP_TIME from STATS$SNAPSHOT; @spreport.sql -- Enter two snapshot id's for difference report
Source: