Oracle Stats Pack

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;
/