Oracle 18c - Use Statspack / Perfstat to analyse the performance of the SE Edition


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

Job to capture the data

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

Diese Website verwendet Cookies. Durch die Nutzung der Website stimmen Sie dem Speichern von Cookies auf Ihrem Computer zu. Außerdem bestätigen Sie, dass Sie unsere Datenschutzbestimmungen gelesen und verstanden haben. Wenn Sie nicht einverstanden sind, verlassen Sie die Website.Weitere Information
dba/oracle18c_statspack_perfstat.txt · Zuletzt geändert: 2019/04/02 09:20 von gpipperr