User Tools

Site Tools


tuning:oracle_statspack_usage

The usage of Oracle statspack

Overview

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

Prerequisites:

  • 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
 
sql>@spcreate.sql
 
# 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:

sql>@spauto.sql

Create the first report:

sql>@?/rdbms/admin/spreport.sql
# 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
 
sql>exit

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


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

Get the Hash Value of SQL Statements

@?/rdbms/admin/sprepsql.sql

RAC

Create job for the second instance:

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

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

Maintainance

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
 
BEGIN
 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);
 
END;
/
 
 
commit
 
BEGIN
  dbms_job.submit(
	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);
 
END;
/
 
 
commit;
 
 
SET verify off

Additional Informatoin

"Autor: Gunther Pipperr"
tuning/oracle_statspack_usage.txt · Last modified: 2016/11/01 20:03 by gpipperr