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 Oracle9database 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:

 [oracle@loredata-srv ~]$ sqlplus perfstat/oracle

SQL*Plus: Release 12.1.0.2.0 Production on Wed Jul 20 13:00:29 2016

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, Automatic Storage Management, OLAP, Advanced Analytics,
Real Application Testing and Unified Auditing options

SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

Then create the auto-fetch job:

 SQL> @?/rdbms/admin/spauto.sql

PL/SQL procedure successfully completed.

Job number for automated statistics collection for this instance
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Note that this job number is needed when modifying or removing
the job:

JOBNO
———-
1

Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter – the value for this parameter must be greater
then 0 to use automatic statistics gathering:

NAME TYPE VALUE
———————————— ———– ——————————
job_queue_processes integer 1000

Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:

JOB NEXT_DATE NEXT_SEC
———- ——— ——–
1 20-JUL-16 14:00:00

Verify that the job has actually been scheduled:

 SQL> alter session set nls_date_format=’dd/mm/yyyy hh24:mi:ss’;

Session altered.

SQL> select job, what, LAST_DATE, NEXT_DATE, TOTAL_TIME, BROKEN, FAILURES from dba_jobs where SCHEMA_USER=’PERFSTAT’;

JOB WHAT LAST_DATE NEXT_DATE TOTAL_TIME B FAILURES
———- ————————————————– ——————- ——————- ———- – ———-
1 statspack.snap; 20/07/2016 14:00:00 0 N

Unlike the AWR report, the Statspack report has less detailed information, but it’s better than nothing, right? The report generated by Statspack is also only extracted in .txt format, but it is easy to read.

I’ll take another snapshot to get the 2 required to generate a report:

 SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

We can see the snapshots in view stats $ snapshot:
SQL> select name, snap_id, to_char(snap_time, ‘DD/MM/YYYY HH24:MI:SS’) “Snapshot Time” from stats$snapshot,v$database;

NAME SNAP_ID Snapshot Time
——— ———- ——————-
LOREDB 2 20/07/2016 13:44:18
LOREDB 1 20/07/2016 13:01:16

To generate the report run the script:

SQL> @?/rdbms/admin/spreport.sql

Current Instance
~~~~~~~~~~~~~~~~

DB Id DB Name Inst Num Instance
———– ———— ——– ————
1937271711 LOREDB 1 loredb

Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

DB Id Inst Num DB Name Instance Host
———– ——– ———— ———— ————
1937271711 1 LOREDB loredb loredata-srv
.localdomain

Using 1937271711 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
———— ———— ——— —————– —– ——————–
loredb LOREDB 1 20 Jul 2016 13:01 5
2 20 Jul 2016 13:44 5

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 1
Begin Snapshot Id specified: 1

Enter value for end_snap: 2
End Snapshot Id specified: 2

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_1_2. To use this name,
press <return> to continue, otherwise enter an alternative.

Enter value for report_name: statspack_rpt.txt

Using the report name statspack_rpt.txt

STATSPACK report for

–RESULTADO DO RELATORIO……..
.
.
.
End of Report ( statspack_rpt.txt )

The generated file can be opened with any text editor.
Our team hopes that this article is fruitful for your projects. Thank you for giving valuable time to read the article.

About The Author

Leave a Reply