How to install, configure, and use Statspack in Oracle

Hello guys, in this article we are going to learn how to install, configure and use statspack in oracle. In this activity, I’m going to use the Oracle 12c environment.

Introduction to Statspack

If the DB server running very slow, then we need to perform DB tuning and it is very important to have an established baseline for later comparison. On the behalf of baseline data, we identify the performance problems.

  • the volume of transactions on the system increased?
  • transaction profile or application changed?
  • the number of users increased?

The Statspack is a collection of SQL, PL/SQL, and SQL*Plus scripts that allow us to collect automation, storage, and viewing of performance information. The Statspack saved all the performance statistics permanently in the tables, which can be used for reporting and analysis later. using the Statspack reports collected data can be analyzed, which include a database instance health and load a summary page, high resource-consuming SQL statements, and the traditional wait events and initialization parameters.

AWR vs ADDM vs ASH

How Statspack Works

The PERFSTAT user is created automatically once the statspack installation completed. The PERFSTAT user is the owner of all the objects needed by the statspack package.

Space Requirements for Statspack

A minimum of 64 MB space required for the installation of statspack which is used for creating tables and indexes.

In other words, you can say that the amount of space required by the statspack package and its depends on the recap of snapshots, the size of the DB and instance, and the range of data collected, which can be configured. It is therefore difficult to provide unprivileged storage clauses and database space utilization predictions that are accurate at each site.

Important key‘s:

  • If you install the package in a dictionary-managed tablespace, then you should monitor the space used by the objects created and, if required, adjust the storage clauses of the segments.
  • If you install the package in a locally managed tablespace, then storage clauses are not required, because the storage characteristics are automatically managed.

Installing Statspack

There are two ways to installing statspack listed below.

  1. Interactive Statspack Installation
  2. Batch Mode Statspack Installation

Interactive Statspack Installation

For statspack installation we need to create a dedicated tablespace for statspack user, it’s optional you can use any tablespace. Check current tablespace details.

SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "size in MB" from dba_data_files order by 1;
Statspack in Oracle

Create a dedicated tablespace for statspack user.

SQL> create tablespace stat_pack
  2  datafile '/u01/app/oracle/oradata/digital/stat_pack01.dbf'
  3  size 150M
  4  autoextend on
  5  maxsize 5G;

Tablespace created.
Statspack in Oracle

Start the statspack installation

Now it’s time to run the statspack installation, so just run the SPCREATE.sql script which is creating the PERFSTAT user and their objects.

During the script execution, it will be asking for the PERFSTAT user password, default tablespace, and temporary tablespace.

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


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

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

Enter value for default_tablespace: STAT_PACK

Using tablespace STAT_PACK 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 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: TEMP

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.

SQL>
SQL> --
SQL> --  Build the tables and synonyms
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;

Session altered.

SQL>
SQL> -- set this parameter for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = TRUE;

Session altered.

SQL>
SQL> @@spctab
SQL> Rem
SQL> Rem $Header: rdbms/admin/spctab.sql /main/52 2013/11/08 09:02:22 kchou Exp $
SQL> Rem
SQL> Rem spctab.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2013, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spctab.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to create tables to hold
SQL> Rem         start and end "snapshot" statistical information
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as STATSPACK user, PERFSTAT
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    kchou       10/30/13 - Bug# 17504669: Add New Column
SQL> Rem                             remaster_type to STATS$DYNAMIC_REMASTER_STATS
SQL> Rem    shsong      06/29/11 - shsong 06/28/11 - Bug 12702106: display
SQL> Rem                           v$IOSTAT_FUNCTION_DETAIL
SQL> Rem    traney      04/06/11 - 35209: long identifiers dictionary upgrade
SQL> Rem    kchou       01/10/11 - Forward Merge of Bug Fix 9800868 to 12.1 Mainline
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add Missing Idle Events for
SQL> Rem                           11.2.0.2for Statspack & Standby Statspack
SQL> Rem    kchou       08/11/10 - Bug#9800868 - Add missing idle events to 11.2.0.2
SQL> Rem    kchou       01/10/11 - XbranchMerge kchou_bug-9800868 from
SQL> Rem                           st_rdbms_11.2.0
SQL> Rem    cgervasi    05/13/09 - add idle event: cell worker idle
SQL> Rem    cgervasi    04/02/09 - bug8395154: missing idle events
SQL> Rem    rhlee       02/22/08 -
> Rem    cdgreen     03/14/07 - 11 F2
SQL> Rem    shsong      06/14/07 - Add idle events
SQL> Rem    cdgreen     02/28/07 - 5908354
SQL> Rem    cdgreen     04/26/06 - 11 F1
SQL> Rem    cdgreen     06/26/06 - Increase column length
SQL> Rem    cdgreen     05/10/06 - 5215982
SQL> Rem    cdgreen     05/24/05 - 4246955
SQL> Rem    cdgreen     04/18/05 - 4228432
SQL> Rem    cdgreen     03/08/05 - 10gR2 misc
SQL> Rem    vbarrier    02/18/05 - 4081984
SQL> Rem    cdgreen     10/29/04 - 10gR2_sqlstats
SQL> Rem    cdgreen     07/16/04 - 10gR2
SQL> Rem    cdialeri    03/25/04 - 3516921
SQL> Rem    vbarrier    02/12/04 - 3412853
SQL> Rem    cdialeri    12/04/03 - 3290482
SQL> Rem    cdialeri    11/05/03 - 3202706
SQL> Rem    cdialeri    10/14/03 - 10g - streams - rvenkate
SQL> Rem    cdialeri    08/05/03 - 10g F3
SQL> Rem    cdialeri    02/27/03 - 10g F2: baseline, purge
SQL> Rem    vbarrier    02/25/03 - 10g RAC
SQL> Rem    cdialeri    11/15/02 - 10g F1
SQL> Rem    cdialeri    09/27/02 - sleep4
SQL> Rem    vbarrier    03/20/02 - 2143634
SQL> Rem    vbarrier    03/05/02 - Segment Statistics
SQL> Rem    cdialeri    02/07/02 - 2218573
SQL> Rem    cdialeri    01/30/02 - 2184717
SQL> Rem    cdialeri    01/11/02 - 9.2 - features 2
SQL> Rem    cdialeri    11/30/01 - 9.2 - features 1
SQL> Rem    cdialeri    04/22/01 - Undostat changes
SQL> Rem    cdialeri    03/02/01 - 9.0
SQL> Rem    cdialeri    09/12/00 - sp_1404195
SQL> Rem    cdialeri    04/07/00 - 1261813
SQL> Rem    cdialeri    03/20/00 - Support for purge
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    01/26/00 - 1169401
SQL> Rem    cdialeri    11/01/99 - Enhance, 1059172
SQL> Rem    cmlim       07/17/97 - Added STATS$SQLAREA to store top sql stmts
SQL> Rem    gwood       10/16/95 - Version to run as sys without using many views
SQL> Rem    cellis.uk   11/15/89 - Created
SQL> Rem
SQL>
SQL> set showmode off echo off;

If this script is automatically called from spcreate (which is
the supported method), all STATSPACK segments will be created in
the PERFSTAT user's default tablespace.
...................
...................
SQL> set echo off;
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.

Taking a Statspack Snapshot

Once stats pack installation successfully completed, then you can take a statspack snapshot. For taking statspack snapshot login with PERFSTAT using and execute below procedure.

Taking statspack snapshot manually

Use below command to take statspack snapshot manually.

SQL> conn perfstat/perfstat
Connected.
SQL>
SQL>
SQL> show user
USER is "PERFSTAT"
SQL>
SQL>
SQL> exec statspack.snap;

PL/SQL procedure successfully completed.

Automatic statspack snapshot gathering

The DBMS_JOB the package is an Oracle-automated way for scheduling and running different tasks, such as collecting statistics. A demo script on how to do this is supplied in SPAUTO.SQL, which schedules a snapshot every hour, on the hour.

SQL> @?/rdbms/admin/spauto.sql
SQL> Rem
SQL> Rem $Header: spauto.sql 16-feb-00.16:49:37 cdialeri Exp $
SQL> Rem
SQL> Rem spauto.sql
SQL> Rem
SQL> Rem  Copyright (c) Oracle Corporation 1999, 2000. All Rights Reserved.
SQL> Rem
SQL> Rem    NAME
SQL> Rem         spauto.sql
SQL> Rem
SQL> Rem    DESCRIPTION
SQL> Rem         SQL*PLUS command file to automate the collection of STATPACK
SQL> Rem         statistics.
SQL> Rem
SQL> Rem    NOTES
SQL> Rem         Should be run as the STATSPACK owner, PERFSTAT.
SQL> Rem         Requires job_queue_processes init.ora parameter to be
SQL> Rem         set to a number >0 before automatic statistics gathering
SQL> Rem         will run.
SQL> Rem
SQL> Rem    MODIFIED   (MM/DD/YY)
SQL> Rem    cdialeri    02/16/00 - 1191805
SQL> Rem    cdialeri    12/06/99 - 1059172, 1103031
SQL> Rem    cdialeri    08/13/99 - Created
SQL> Rem
SQL>
SQL>
SQL> spool spauto.lis
SQL>
SQL> --
SQL> --  Schedule a snapshot to be run on this instance every hour, on the hour
SQL>
SQL> variable jobno number;
SQL> variable instno number;
SQL> begin
  2    select instance_number into :instno from v$instance;
  3    dbms_job.submit(:jobno, 'statspack.snap;', trunc(sysdate+1/24,'HH'), 'trunc(SYSDATE+1/24,''HH'')', TRUE, :inst
o);
  4    commit;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL>
SQL>
SQL> prompt

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

     JOBNO
----------
         1

SQL>
SQL> prompt

SQL> prompt     Job queue process
Job queue process
SQL> prompt     ~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~
SQL> prompt     Below is the current setting of the job_queue_processes init.ora
Below is the current setting of the job_queue_processes init.ora
SQL> prompt     parameter - the value for this parameter must be greater
parameter - the value for this parameter must be greater
SQL> prompt     than 0 to use automatic statistics gathering:
than 0 to use automatic statistics gathering:
SQL> show parameter job_queue_processes

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes                  integer     1000
SQL> prompt

SQL>
SQL> prompt

SQL> prompt     Next scheduled run
Next scheduled run
SQL> prompt     ~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~
SQL> prompt     The next scheduled run for this job is:
The next scheduled run for this job is:
SQL> select job, next_date, next_sec
  2    from user_jobs
  3   where job = :jobno;

       JOB NEXT_DATE NEXT_SEC
---------- --------- --------
         1 30-APR-21 18:00:00

1 row selected.

SQL>
SQL> spool off;

Creating a Statspack Report 

How to generate statspack report in oracle 12c

To creating statspack report we need minimum two snapshots, so that we can analyze the duration between two statspack snapshots. Use below query to creating a statspack report.

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

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

   DB Id    Inst Num DB Name      Instance     Host
----------- -------- ------------ ------------ ------------
  753220875        1 DIGITAL      digital      digital.shri
                                               pal.com

Using  753220875 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
------------ ------------ --------- ----------------- ----- ------------------
digital      DIGITAL              1 30 Apr 2021 17:28     5
                                  2 30 Apr 2021 17:46     5
                                  3 30 Apr 2021 17:46     5
                                  4 30 Apr 2021 17:46     5



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

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



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

Enter value for report_name: /u01/spreport_digital

Using the report name /u01/spreport_digital

Purge perfstat data

Purge unwanted data from the PERFSTAT schema using script SPPURGE.SQL. Which deletes snapshots that fall between the begin and end snapshot IDs you specify.

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


Database Instance currently connected to
========================================
                                Instance
   DB Id    DB Name    Inst Num Name
----------- ---------- -------- ----------
  753220875 DIGITAL           1 digital


Snapshots for this database instance
====================================
                               Base-  Snap
 Snap Id   Snapshot Started    line? Level Host
-------- --------------------- ----- ----- ---------------
Comment
--------------------
       1  30 Apr 2021 17:28:30           5 digital.shripal

       2  30 Apr 2021 17:46:01           5 digital.shripal

       3  30 Apr 2021 17:46:03           5 digital.shripal

       4  30 Apr 2021 17:46:06           5 digital.shripal

       5  30 Apr 2021 18:00:12           5 digital.shripal



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: 3
Using 3 for lower bound.

Enter value for hisnapid: 4

Purge of specified Snapshot range complete.

How to run statspack report in oracle 11g, You can use above method also for oracle 11g and 19c.

So this is the complete guide how to generate statspack report in oracle 12c.

subscribe on youtube

1 thought on “How to install, configure, and use Statspack in Oracle”

Leave a Comment

%d bloggers like this:
Enable Notifications    OK No thanks