Oracle Statspack
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. Table data is used for reporting and analysis later. 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.
How Statspack Works
The PERFSTAT user is created automatically once the statspack installation is completed. This user is the owner of all the objects needed by the statspack package.
Space Requirements for Statspack
A minimum of 64 MB space is 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.
How to check statspack is installed or not?
Using the below query, you can check statspack is installed or not. If the query returns some result that men's the stats pack is installed or if showing error ORA-00942 that means it's not installed in your system.
SQL> select * from stats$level_description;The result if statspack installed:
If not installed:
ERROR at line 1:
ORA-00942: table or view does not exist (STATSPACK are not installed before)Installing Statspack
There are two ways to install statspack listed below.
- Interactive Statspack Installation
- Batch Mode Statspack Installation
Interactive Statspack Installation
For statspack installation we need to create a dedicated tablespace for statspack users, it's optional you can use any tablespace. Check current tablespace details.
SQL> set lin 300 pagesize 300
SQL> col FILE_NAME for a65
SQL> select FILE_ID,TABLESPACE_NAME,FILE_NAME,BYTES/1024/1024 "size in MB" from dba_data_files order by 1;
Step 1. Create a dedicated tablespace for statspack users.
SQL> create tablespace stat_pack
datafile '/u01/app/oracle/oradata/digital/stat_pack01.dbf'
size 150M
autoextend on
maxsize 5G;
Tablespace created.
Step 2. Execute spcreate.sql script
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.After installation, the parameter timed_statistics value must be true.
SQL> show parameter timed_statisticStatspack 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.
Take statspack snapshot manually
Use the below command to take statspack snapshots manually.
SQL> conn perfstat/perfstat
Connected.
SQL>
SQL>
SQL> show user
USER is "PERFSTAT"
SQL>
SQL> select name,snap_id,to_char(snap_time,'DD-MON-YYYY:HH24:MI:SS')
"Date/Time" from stats$snapshot,v$database;
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;Check jobs
SQL> select JOB, SCHEMA_USER, INTERVAL, BROKEN, WHAT from dba_jobs where JOB=1;Change snapshots taking time in job, using below query
SQL> exec dbms_job.interval(1, 'trunc(SYSDATE+1/48,"HH")');
Time examples:
Every one hour from now: sysdate+1/24
Every 1/2 hour from now: sysdate+1/48
Every 15 Minuts from now: sysdate+15/1440Creating a Statspack Report
How to generate statspack report in oracle 12c
To create a statspack report we need a minimum of two snapshots so that we can analyze the duration between two statspack snapshots. Use the below query to create a statspack report.
SQL> @?/rdbms/admin/spreport.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.Truncate statspack tables
SQL> @?/rdbms/admin/sptrunc.sql Uninstall Statspack
The spdrop.sql script will remove all data related to statspack.
SQL> @?/rdbms/admin/spdrop.sqlOutput:
SQL> @?/rdbms/admin/spdrop.sql
Session altered.
Dropping old versions (if any)
Synonym dropped.
Sequence dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
View dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Table dropped.
Synonym dropped.
Package dropped.
NOTE:
SPDTAB complete. Please check spdtab.lis for any errors.
SQL>
SQL>
SQL> --
SQL> -- Drop PERFSTAT user
SQL>
SQL> @@spdusr
SQL> Rem
SQL> Rem $Header: rdbms/admin/spdusr.sql /main/17 2020/07/20 02:19:43 dgoddard Exp $
SQL> Rem
SQL> Rem spdusr.sql
SQL> Rem
SQL> Rem Copyright (c) 1999, 2017, Oracle and/or its affiliates.
SQL> Rem All rights reserved.
SQL> Rem
SQL> Rem NAME
SQL> Rem spdusr.sql
SQL> Rem
SQL> Rem DESCRIPTION
SQL> Rem SQL*Plus command file to DROP user which contains the
SQL> Rem STATSPACK database objects.
SQL> Rem
SQL> Rem NOTES
SQL> Rem Must be run when connected to SYS (or internal)
SQL> Rem
SQL> Rem BEGIN SQL_FILE_METADATA
SQL> Rem SQL_SOURCE_FILE: rdbms/admin/spdusr.sql
SQL> Rem SQL_SHIPPED_FILE: rdbms/admin/spdusr.sql
SQL> Rem SQL_PHASE: UTILITY
SQL> Rem SQL_STARTUP_MODE: NORMAL
SQL> Rem SQL_IGNORABLE_ERRORS: NONE
SQL> Rem END SQL_FILE_METADATA
SQL> Rem
SQL> Rem MODIFIED (MM/DD/YY)
SQL> Rem shsong 02/02/09 - remove drop STATS$X_$KCFIO etc
SQL> Rem shsong 07/03/08 - drop view STATS$X_$KCBFWAIT etc
SQL> Rem cdgreen 08/22/05 - 4562627
SQL> Rem cdgreen 05/24/05 - 4246955
SQL> Rem cdialeri 11/07/03 - 10g - streams - rventkate
SQL> Rem cdialeri 04/23/01 - 9.0
SQL> Rem cdialeri 09/12/00 - sp_1404195
SQL> Rem cdialeri 04/07/00 - 1261813
SQL> Rem cdialeri 02/16/00 - 1191805
SQL> Rem cdialeri 11/04/99 - 1059172
SQL> Rem cdialeri 08/13/99 - Created
SQL> Rem
SQL>
SQL> set echo off;
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
View dropped.
Synonym dropped.
User dropped.
NOTE:
SPDUSR complete. Please check spdusr.lis for any errors.
SQL>
SQL>
SQL> -- Bug#25233027: xxx Set this parameter to FALSE for creating common objects in consolidated database
SQL> alter session set "_oracle_script" = FALSE;
Session altered.
Drop associated tablespace
SQL> drop tablespace STAT_PACK including contents and datafiles;
Tablespace dropped.
Run the statspack report in oracle database 11g. Steps are the same also for oracle 11g,12c, 19c, and 21c.
So, this is the complete guide on how to generate a statspack report in oracle 12c.
One thought on “How to install, configure, and use Statspack in Oracle”