User Tools

Site Tools


The usage of Oracle statspack


If you look inside the Oracle statistical views, most of the data in not very helpfully at this time. You see a value but it is very difficult to interpret this single value. You need the chronological sequence of the values and the difference between the points in time.

With statspack you snap most of the statics value in the v$ tables of the database to a repository inside the database. For the snap you define an interval who often you need the data.

Later you generate differential reports over these values and now it is possible to interpret this data.

 Oracle statspack architecture

After some snapshots it is possible to create the differential report over the values inside the statspack repository with the help of the script $ORACLE_HOME/rdbms/admin/spreport.sql

 Oracle statspack report

Install Oracle statspack


  • create table space for the statspack repository (or decide to use an existing one)

The installation script can be only started local on the database machine and the user connect must be done with “/ as sysdba”. The user PERFSTAT will be created. This user is the owner of the statspack repository and the snapshot job.

The example installation is done in a Microsoft Windows PowerShell environment:

# set your DB enviroment, SID and ORACLE_HOME!
cd $env:ORACLE_HOME/rdbms/admin
sqlplus / as sysdba
# define password for the perfstat user
# choose tablespace for the perfstat schema
# choose temp tablespace
# creation of the repositroy tables starts
# Main pls/sql package statspack will be installed
# finish

Create the first snap with the statspack default values:

sql>EXEC statspack.snap
# Wait SOME minutes AND do something WITH the DATABASE
#create the NEXT snap:
sql>EXEC statspack.snap

Create a job to snap every hour the statisic:


Create the first report:

# choose the snapshot id TO START
# choose the snapshot id TO END
# Enter the path AND the name OF the report LIKE d:\temp\statspack_gpi_1_to_2.txt
# Report will be created

Now you can open your first statspack report (in our example: d:\temp\statspack_gpi_1_to_2.txt) with an editor.

Please us for MS Windows Notepad++, free, open source, best editor of the world!

Get the Hash Value of SQL Statements



Create job for the second instance:

variable jobnum NUMBER;
 DBMS_JOB.SUBMIT (:jobnum , 'statspack.snap;' , to_date('11.05.2015 16:05',' hh24:mi'), 'sysdate+1/24', TRUE, 2);

Scripts ?/rdbms/admin/

  • spreport.sql ⇒ Generates a Statspack Instance report
  • sprepins.sql ⇒ Generates a Statspack Instance report for the database and instance specified
  • sprepsql.sql ⇒ Generates a Statspack SQL report for the SQL Hash Value specified
  • sprsqins.sql ⇒ Generates a Statspack SQL report for the SQL Hash Value specified, for the database and instance specified
  • sppurge.sql ⇒ Purges a limited range of Snapshot Id’s for a given database instance
  • sptrunc.sql ⇒ Truncates all Performance data in Statspack tables
  • spuexp.par ⇒ An export parameter file supplied for exporting the whole PERFSTAT user


Rebuild Statspack indexes:

SELECT 'alter index '||owner||'.'||segment_name||' rebuild;'
  FROM dba_segments
 WHERE segment_name IN (SELECT index_name FROM dba_indexes WHERE owner = 'PERFSTAT');

Delete Jobs for old snap in a cluster:

define DBID=1517503088
prompt CHECK IF you have SET the correct DB ID = &DBID !
variable jobno NUMBER
SET verify ON
 dbms_job.submit(job => :jobno
  , what => 'statspack.purge(i_num_days=>14,i_extended_purge=>true,I_DBID=>&&DBID, I_INSTANCE_NUMBER=> 1 ); '
  , next_date => sysdate
  , INTERVAL  => 'trunc(SYSDATE+1)+(((1/24)*4)+((1/(26*60))*5))'
  , no_parse  => FALSE
  , instance  => 1
  , force     => TRUE);
	job       => :jobno
  , what      => 'statspack.purge(i_num_days=>14,i_extended_purge=>true,I_DBID=>&&DBID, I_INSTANCE_NUMBER=> 2 ); '
  , next_date =>  sysdate
  , INTERVAL  => 'trunc(SYSDATE+1)+(((1/24)*4)+((1/(26*60))*12))'
  , no_parse  => TRUE
  , instance  => 2
  , force     => TRUE);
SET verify off

Additional Information

"Autor: Gunther Pipperr"
tuning/oracle_statspack_usage.txt · Last modified: 2019/04/02 09:20 by gpipperr