Statspack is a set of free performance monitoring and reporting utilities provided by Oracle.
From Oracle10g, there is also AWR and ADDM which provide additional statistics over Statspack. However, these are only available via the optional Diagnostic Pack at additional cost.
Install statspack
cd $ORACLE_HOME/rdbms/admin sqlplus "/ as sysdba" @spdrop.sql -- Drop and install statspack sqlplus "/ as sysdba" @spcreate.sql -- Enter password & tablespace names when prompted. (I use defaults of sysaux & temp)
Take performance snapshots of the database
sqlplus perfstat/perfstat -- (change password as set locally) exec perfstat.statspack.snap; -- Take a performance snapshots -- or exec perfstat.statspack.snap(i_snap_level=>10); -- or instruct statspack to gather further details in the snapshot -- (look up which oracle version supports which level using sql>select * from perfstat.stats$level_description;).
Statspack reporting
-- Get a list of snapshots select SNAP_ID, SNAP_TIME, snap_level, instance_number,startup_time from STATS$SNAPSHOT order by snap_time desc @$ORACLE_HOME/rdbms/admin/spreport.sql -- Enter two snapshot id's for difference report @C:\oracle\product\10.2.0\client_1\RDBMS\ADMIN\spreport
Collecting statistics for a specific session
It is also possible to specify a session id for which statistics have to be collected:
begin statspack.snap(i_session_id=>49); end; /