Installing Oracle Statspack

I must thank my fellow DBA Franky Weber Faust for his publication in his blog.
Introduction: – This article is about Oracle Statspack. We are going to learn what is Oracle Statspack? how does it work, why do we need of Oracle Statspack? Statspack is a performance tuning tool provided by Oracle with the Oracle9i database distribution. With minimal effort, it can be installed on any Oracle9i database to rapidly gather detailed analysis of the performance of that database instance.

What is Oracle Statspack?

The Statspack package is a set of SQL, PL/SQL, and SQL*Plus scripts that allow the collection, automation, storage, and viewing of performance data. Statspack stores the performance statistics perennially in Oracle tables, which can later be used for reporting and analysis. The data collected can be analyzed using Statspack reports, which includes an instance health and load summary page, high resource SQL statements, and the traditional wait events and initialization parameters.

How does Oracle Statspack work?

When we run the Statspack installation script, the PERFSTAT user is created automatically.  PERFSTAT have all objects needed by the Statspack package and is granted limited query-only privileges on the V$ views mandatory for performance tuning.Statspack users become accustomed to the concept of a snapshot, a single collection of performance data. Each snapshot taken is recognized by a snapshot ID, which is a unique number generated at the time the snapshot is taken. Each time a new collection is taken, a new SNAP_ID is generated. The SNAP_ID, along with the database identifier (DBID) and instance number (INSTANCE_NUMBER), comprise the unique key for a snapshot. Use of this unique merger allows storage of multiple instances of an Oracle Real Application Clusters database in the same tables.

After snapshots are taken, We can run the performance report. The report prompts you for start and end snapshot IDs and then calculates activity on the instance between the two snapshots, much like a BSTAT/ESTAT report. To compare, the first SNAP_ID supplied can be considered the analogous of running BSTAT; the second SNAP_ID specified can be considered the equivalent of ESTAT. Unlike BSTAT/ESTAT, which can by its nature only compare two static data points, the report can compare any two snapshots specified.

We can adjust the STATSPACK Collection Level 

STATSPACK has two types of collection options, level and threshold. The level parameter controls the type of data collected from Oracle, while the threshold parameter acts as a filter for the collection of SQL statements into the stats$sql_summary table.

SQL> SELECT * FROM stats$level_description ORDER BY snap_level;

Level 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.
Level 5	This level includes capturing high resource usage SQL Statements, along with all data captured by lower levels.
Level 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.
Level 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.
Level 10	This level includes capturing Child Latch statistics, along with all data captured by lower levels.

By default Statspack is not installed, so we need to first install it and configure it to run the snapshots. Detailed documentation of the utility you find in $ ORACLE_HOME / rdbms / admin / spdoc.txt or also has this unofficial documentation: SP Survival Guide . The procedure I run here runs from Oracle 8i to 12c (pay attention to detail * when creating the SP in the CDB).

Before installing Statspack I always like to create a unique tablespace for it:

SQL> create tablespace PERFSTAT datafile ‘+DATA’ size 50M autoextend on next 50M maxsize 5G;

Tablespace created.

* If you, like me, are running the Statspack structure build script on an Oracle Database 12c CDB, change the “_oracle_script” parameter to TRUE in your session so that you do not get the error reported for the common PERFSTAT user name.

… Creating PERFSTAT user
create user perfstat
*
ERROR at line 1:
ORA-65096: invalid common user or role name

With the SYS user run the following scripts:

SQL> alter session set “_oracle_script”=true;

Session altered.

SQL> alter session set “_oracle_script”=true;

Session altered.SQL> @?/rdbms/admin/spcreate.sql

Choose the PERFSTAT user’s password
———————————–
Not specifying a password will result in the installation FAILING

Enter value for perfstat_password: oracle
oracle

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
—————————— ——— —————————-
PERFSTAT PERMANENT
SYSAUX PERMANENT *
USERS PERMANENT

Pressing <return> will result in STATSPACK’s recommended default
tablespace (identified by *) being used.

Enter value for default_tablespace: PERFSTAT

Using tablespace PERFSTAT as PERFSTAT default tablespace.

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 work areas). Specifying the SYSTEM
tablespace for the user’s temporary tablespace will result in the
installation FAILING, as using SYSTEM for work areas 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:

Using tablespace TEMP as PERFSTAT temporary tablespace.

… Creating PERFSTAT user

… Installing required packages

… Creating views

… Granting privileges

NOTE:
SPCUSR complete. Please check spcusr.lis for any errors.
.
.
. Muitas linhas serão criadas, então resumi um pouco o resultado…
.
.

1 row created.

Commit complete.

Synonym created.

Synonym created.

NOTE:
SPCTAB complete. Please check spctab.lis for any errors.

SQL> — Create the statistics Package
.
.
.
.
Creating Package STATSPACK…

Package created.

No errors.
Creating Package Body STATSPACK…

Package body created.

No errors.

NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.

Make sure there were no errors in the process by accessing the spcpkg.lis, spctab.lis and spcusr.lis files.

After being with Statspack installed I recommend that you collect PERFSTAT schema statistics, otherwise you may have problems running some packages that query / write to SP tables:

SQL> exec dbms_stats.gather_schema_stats(‘PERFSTAT’);

PL/SQL procedure successfully completed.

Connect with the PERFSTAT user and generate an initial snapshot: