How to install STATSPACK in Oracle Database 12c

[eivanovic]

Sometimes a customer is asking if someone can check why his database was so slow yesterday around 2 p.m.

With Oracle Standard Edition there is no possibility to use ASH and ADDM which are ENTERPRISE FEATURES. But you can use STATSPACK instead of ASH and ADDM. Statspack is for free and can be used with any Oracle Database. This article describes how Statspack can be installed.

Installing Statspack
First create the tablespace to hold the Statspack tables.

Code: Select all

[i]SQL> create tablespace STATSPACK_REPO datafile size 512M autoextend on ;[/i]

Tablespace created.

Run the ORACLE_HOME/rdbms/admin/spcreate.sql script to create the PERFSTAT schema. The script runs three scripts: spcuser.sql to create the PERFSTAT user, spctab.sql to create the Statspack tables and synonyms and finally spcpkg.sql to create the statistic gathering packages.

Code: Select all

[i]@?/rdbms/admin/spcreate.sql  (you will be prompted for password, default Tablespace, Temporary Tablespace ) [/i]

Logs of the execution are written to spcuser.lis, spctab.lis and spcpkg.lis. Providing there are no errors we have successfully installed Statspack.

Statspack is taking frequent snapshots and is storing predefined diagnostic information in the oracle database.

Code: Select all

SNAP_LEVEL
----------
DESCRIPTION
------------------------------------------------------------------------------------------------------------------------
         0
This level captures general statistics, including rollback segment, row cache, SGA, system events, background events, se
ssion events, system statistics, wait statistics, lock statistics, and Latch information

         5
This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels

         6
This level includes capturing SQL plan and SQL plan usage information for high resource usage SQL Statements, along with
 all data captured by lower levels

         7
This level captures segment level statistics, including logical and physical reads, row lock, itl and buffer busy waits,
 along with all data captured by lower levels

        10
This level includes capturing Child Latch statistics, along with all data captured by lower levels

[/i]

If you want to take a snapshot at a different level you can pass in the desired level using the i_snap_level parameter.

You can then create a snapshot by issuing

Code: Select all

SQL> exec statspack.snap(i_snap_level => 7);

PL/SQL procedure successfully completed.

[/I]

If you want to change the default parameter for statspack ( 5 ) to 7 for example you can do this by issuing

Code: Select all

SQL> exec statspack.modify_statspack_parameter(i_snap_level=>7,i_modify_parameter=>'true');

PL/SQL procedure successfully completed.

[/I]


Automating snapshot collection

Automating the collection of snapshots through out the day provides useful information to aid in detecting the source of performance related issues. Care should be taken that the snapshots are not taken so frequently that the collections become a source of problems and not so far apart that it is difficult to obtain useful information.

Below is a schedule and job to collect Statspack snap shots every 20 minutes. This schedule will make collections at 10, 30 and 50 after the hour. The choice of 10 before and after the hour is used to avoid collecting at the same time AWR data is collected which defaults to every hour.

Code: Select all

SQL> BEGIN
  2    DBMS_SCHEDULER.CREATE_SCHEDULE(
  3     schedule_name => 'perfstat.statspack_every20',
  4     repeat_interval => 'FREQ=MINUTELY;BYMINUTE=10,30,50');
  5
  6    DBMS_SCHEDULER.CREATE_JOB(
  7     job_name => 'perfstat.sp_snapshot',
  8     job_type => 'STORED_PROCEDURE',
  9     job_action => 'perfstat.statspack.snap',
 10     schedule_name => 'perfstat.statspack_every20',
 11     comments => 'Statspack collection');
 12
 13    DBMS_SCHEDULER.ENABLE('perfstat.sp_snapshot');
 14  END;
 15  /
 
PL/SQL procedure successfully completed.

[/I]

To generate a report you just have to execute

Code: Select all

@?/rdbms/admin/spreport.sql  - for instance Report 
and 
@?/rdbms/admin/sprepsql.sql - for sqlreports

[/i]
To purge data from the Statspack Repository from time to time you can drop a range of snapshots with

Code: Select all

[i]@?/rdbms/admin/spurge.sql[/i]