Statspack

All about Statspack

Installing Statspack

Run the ‘spcreate.sql’ script using SQL*Plus as user SYS. User PERFSTAT is created by this script, owning all objects needed by the statspack package.

SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spcreate

Removing Statspack
SQL> connect / as sysdba
SQL> @%ORACLE_HOME%\rdbms\admin\spdrop

Taking Snapshot
Interactive way to take a snapshot

SQL> connect perfstat/perfstat
SQL> execute statspack.snap;

Automatically gather StatsPack snapshots
To use an Oracle-automated method for collecting statistics, you can use dbms_job. A sample script on how to do this is supplied in spauto.sql, which schedules a snapshot every hour, on the hour.

• change snapshot interval
  execute dbms_job.interval(,'SYSDATE+(1/48)');
  
• remove the autocollect job,
  execute dbms_job.remove();

To gather a STATSPACK report
@%oracle_home%/rdbms/admin/spreport

Some free statspack analysis report

http://www.statspackanalyzer.com/analyze.asp