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

=== 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.

{{ :dba:oracle_statspack_architecture_01.png | 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** 

{{ :dba:oracle_statspack_architecture_02.png | 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:

<code powershell>
# 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
</code>

Create the first snap with the statspack default values:

<code sql>
sql>exec statspack.snap

# Wait some minutes and do something with the database

#create the next snap:
sql>exec statspack.snap

</code>


----
==== Job to capture the data ====


Create a job to snap every hour the statisic:

<code sql>

sql>@spauto.sql

</code>


Create the first report:

<code sql>

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

</code>


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 [[http://notepad-plus-plus.org | Notepad++]], free, open source, best editor of the world!


Get the Hash Value of SQL Statements

<code sql>
@?/rdbms/admin/sprepsql.sql
</code>

===RAC===
Create job for the second instance:
<code sql>

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


</code>


----

==== 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

{{tag>statspack tuning}}



----

==== Maintainance ====

Rebuild Statspack indexes:
<code sql>
select 'alter index '||owner||'.'||segment_name||' rebuild;'
  from dba_segments
 where segment_name in (select index_name from dba_indexes where owner = 'PERFSTAT');
</code>

Delete Jobs for old snap in a cluster:

<code sql>

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

</code>

----

==== Additional Information ====


  * https://jonathanlewis.wordpress.com/statspack-examples/
  * http://www.hoopoes.com/cs/orca_oracle.shtml
  * https://www.akadia.com/services/ora_statspack_survival_guide.html
  * http://www.orafaq.com/wiki/Statspack
  * https://gist.github.com/tousinn/4617587