Installing and Configuring Statspack

From http://www.oracledistilled.com/oracle-database/performance/installing-and-configuring-statspack/

With the addition of the Active Workload Repository (AWR) and Active Session History (ASH) in Oracle Database 10g and higher it might seem a little odd to still be talking about Statspack. Both AWR and ASH require an Oracle Diagnostics Pack license which is limited to the Enterprise Edition of the database.

This document will detail the interactive setup of Statspack along with setting up a schedule and job to automate the snapshot collection process.

Installing Statspack

First create the tablespace to hold the Statspack tables.

1 2 3 4 5 6 7 8 9

SQL> create tablespace statspack_data   2  datafile '/data/oracle/database/11gR2/oradata/scratch/statspack_data01.dbf' size 500M   3  autoextend on maxsize 2G   4  extent management local uniform size 1M   5  segment space management auto;   Tablespace created.   SQL>

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 finallyspcpkg.sql to create the statistic gathering packages.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42

C:\app\oracle\product\11.2.0\dbhome_1\RDBMS\ADMIN>sqlplus / as sysdba   SQL*Plus: Release 11.1.0.7.0 - Production on Tue Aug 10 09:55:07 2010   Copyright (c) 1982, 2008, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> @spcreate.sql   Choose the PERFSTAT user's password ----------------------------------- Not specifying a password will result in the installation FAILING   Enter value for perfstat_password:   Provide a password for the PERFSTAT user and hit enter.   Choose the Default tablespace for the PERFSTAT user --------------------------------------------------- Below is the list of online tablespaces in this database which can store user data.  Specifying the SYSTEM tablespace for the user's default tablespace will result in the installation FAILING, as using SYSTEM for performance data is not supported.   Choose the PERFSTAT users's default tablespace.  This is the tablespace in which the STATSPACK tables and indexes will be created.   TABLESPACE_NAME                CONTENTS  STATSPACK DEFAULT TABLESPACE ------------------------------ --------- ---------------------------- EXAMPLE                        PERMANENT STATSPACK_DATA                 PERMANENT SYSAUX                         PERMANENT * USERS                          PERMANENT   Pressing <return> will result in STATSPACK's recommended default tablespace (identified by *) being used.   Enter value for default_tablespace: STATSPACK_DATA

A list of available table spaces will be presented next. Type in the name of the table space created earlier and hit the enter key.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

Choose the Temporary tablespace for the PERFSTAT user ----------------------------------------------------- Below is the list of online tablespaces in this database which can store temporary data (e.g. for sort workareas).  Specifying the SYSTEM tablespace for the user's temporary tablespace will result in the installation FAILING, as using SYSTEM for workareas is not supported.   Choose the PERFSTAT user's Temporary tablespace.   TABLESPACE_NAME                CONTENTS  DB DEFAULT TEMP TABLESPACE ------------------------------ --------- -------------------------- TEMP                           TEMPORARY *   Pressing <return> will result in the database's default Temporary tablespace (identified by *) being used.   Enter value for temporary_tablespace:

Next we need to select a temporary table space for the PERFSTAT user. You can hit the enter key to use the default temporary table space. Once you select the temporary table space the PERFSTAT user and all the objects owned byPERFSTAT will be created.

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

Taking Snapshots

Taking snapshots is as easy as executing the STATSPACK.SNAP procedure. There are five snap shot levels (0, 5, 6, 7, 10) with 5 being the default. Details of levels can be found in the documentation and from stats$level_description

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

SQL> select * from stats$level_description;   SNAP_LEVEL  DESCRIPTION ----------  --------------------------------------------------------------------          0  This level captures general statistics, including rollback segment,             row cache, SGA, system events, background events, session 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   SQL>

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

For example the following takes a snapshot at level 7

1 2 3 4 5

SQL> exec statspack.snap(i_snap_level => 7);   PL/SQL procedure successfully completed.   SQL>

If you would like to change the default level from 5 to 7 you can do so with STATSPACK.MODIFY_STATSPACK_PARAMETERusing the i_snap_level and i_modify_parameter parameters.

1 2 3 4 5

SQL> exec statspack.modify_statspack_parameter(i_snap_level=>7, i_modify_parameter=>’true’);   PL/SQL procedure successfully completed.   SQL>

Keep in mind that the higher the snapshot levels require more time and resources to execute than the lower snapshot levels.

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

Running a report

There are two types a reports you can run: an Instance report and a SQL Report.

ORACLE_HOME\rdbms\admin\spreport.sql is used to generate an instance level report. For an instance level report the beginning and ending snapshot id and the name of the output report are required.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59

SQL> @spreport   Current Instance ~~~~~~~~~~~~~~~~ ♀   DB Id    DB Name     Inst Num Instance ----------- ------------ -------- ------------  1253224498 ORCL                1 orcl   1 row selected.   SP2-0311: string expected but not found     Instances in this Statspack schema ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~      DB Id    Inst Num DB Name      Instance     Host ----------- -------- ------------ ------------ ------------  1253224498        1 ORCL         orcl         ODWIN   Using 1253224498 for database Id Using          1 for instance number     Specify the number of days of snapshots to choose from ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Entering the number of days (n) will result in the most recent (n) days of snapshots being listed.  Pressing <return> without specifying a number lists all completed snapshots.       Listing all Completed Snapshots                                                          Snap Instance     DB Name        Snap Id   Snap Started    Level Comment ------------ ------------ --------- ----------------- ----- -------------------- orcl         ORCL                 4 10 Aug 2010 13:10     5                                   5 10 Aug 2010 13:30     5                                   6 10 Aug 2010 13:50     5                                   7 10 Aug 2010 14:10     5     Specify the Begin and End Snapshot Ids ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for begin_snap: 4 Begin Snapshot Id specified: 4   Enter value for end_snap: 5 End   Snapshot Id specified: 5       Specify the Report Name ~~~~~~~~~~~~~~~~~~~~~~~ The default report file name is sp_4_5.  To use this name, press <return> to continue, otherwise enter an alternative.   Enter value for report_name: testreport.txt

Once you enter the name of the output file the Statspack report will be generated to the both the screen and the file.

After viewing the instance report you might want to look deeper at a particular SQL statement from the report. The scriptORACLE_HOME/rdbms/admin/sprepsql.sql can be used to generate an SQL report. In addition to the beginning and ending snapshot id and output file name the SQL Hash value is required.

Snapshot maintenance

Remove a snapshot or a range of snapshots using ORACLE_HOME/rdbms/admin/sppurge.sql

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52

SQL> @sppurge     Database Instance currently connected to ========================================                                   Instance    DB Id    DB Name    Inst Num Name ----------- ---------- -------- ----------  1253224498 ORCL              1 orcl     Snapshots for this database instance ====================================                                  Base-  Snap  Snap Id   Snapshot Started    line? Level Host            Comment -------- --------------------- ----- ----- --------------- --------------------        1  10 Aug 2010 12:12:59           5 ODWIN        2  10 Aug 2010 12:23:23           5 ODWIN        3  10 Aug 2010 12:50:06           5 ODWIN     Warning ~~~~~~~ sppurge.sql deletes all snapshots ranging between the lower and upper bound Snapshot Id's specified, for the database instance you are connected to.  Snapshots identified as Baseline snapshots which lie within the snapshot range will not be purged.   It is NOT possible to rollback changes once the purge begins.   You may wish to export this data before continuing.     Specify the Lo Snap Id and Hi Snap Id range to purge ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Enter value for losnapid: 1 Using 1 for lower bound.   Enter value for hisnapid: 2 Using 2 for upper bound.   Deleting snapshots 1 - 2.   Number of Snapshots purged: 2 ~~~~~~~~~~~~~~~~~~~~~~~~~~~   Purge of specified Snapshot range complete.     SQL>

Remove all snapshots using ORACLE_HOME/rdbms/admin/sptrunc.sql

Uninstall Statspack

If you decide you do not need Statspack installed any more you can remove/uninstall Statspack withORACLE_HOME/rdbms/admin/spdrop.sql. The spdrop.sql needs be dropped by a user with SYSDBA. Remember to remove any jobs you might have created to manage the Statspack environment.