Introduction
STATSPACK is a performance diagnosis tool available since Oracle8i. It stores snapshots of system statistics over time, giving you a historical baseline to compare against when troubleshooting performance issues, without requiring the licensed Diagnostics and Tuning Packs that AWR normally depends on. This makes it a practical option for Standard Edition environments, where AWR isn't available. This guide walks through configuring and using STATSPACK step by step.
Understanding Snapshot Levels
Oracle snapshots are moment-in-time collections of database statistics that Oracle continuously tracks internally. STATSPACK can capture these at several levels, each level building on the one below it and collecting progressively more detail, at the cost of slightly more overhead per snapshot.
| Level | Information Collected |
|---|---|
| 0 | General performance statistics |
| 5 | Additional data: SQL statements |
| 6 | Additional data: SQL plans and SQL plan usage |
| 7 | Additional data: segment level statistics |
| 10 | Additional data: parent and child latches |
Prerequisites
control_management_pack_accessshould be set toNONE. This confirms you're not relying on the licensed AWR/ADDM packs, since STATSPACK is meant as the unlicensed alternative.
Environment Used in This Guide
- Hostname: apex
- IP: 192.168.101.7
ORACLE_SID: APEXDB- DB Name: APEXDB
- PDB Name: APEXPDB
Step 1: Check Parameters and Confirm STATSPACK Isn't Already Configured
Confirm the management pack parameter is set correctly, then check whether STATSPACK objects already exist. Both queries returning "no data" confirms you're starting from a clean state.
Parameter check:
[oracle@apex ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 4 15:55:13 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL>
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APEXPDB READ WRITE NO
SQL> show parameter control_management_pack_access
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
control_management_pack_access string NONE
Verify STATSPACK isn't already configured:
SQL> select username from dba_users where username like '%STAT%';
no rows selected
SQL>
SQL> select * from stats$level_description;
select * from stats$level_description
*
ERROR at line 1:
ORA-00942: table or view does not exist
Step 2: Create the PERFSTAT Tablespace
STATSPACK stores its own tables and indexes in a dedicated tablespace rather than mixing them into SYSTEM or USERS, which keeps performance data isolated and makes it easy to manage or drop later if needed.
SQL> col file_name for a150
SQL> set lines 200 pages 1000
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_sysaux_mvyr3vf8_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_system_mvyr435s_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_undotbs1_mvyr4bj1_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_users_mvyr4hbg_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/user01.dbf
SQL> CREATE TABLESPACE PERFSTAT DATAFILE '/u02/app/oracle/oradata/APEXPDB/datafile/perfstat01.dbf' SIZE 500M autoextend on;
Tablespace created.
SQL> select file_name from dba_data_files;
FILE_NAME
---------------------------------------------------------------------------------------------------------------------------
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_sysaux_mvyr3vf8_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_system_mvyr435s_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_undotbs1_mvyr4bj1_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/o1_mf_users_mvyr4hbg_.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/user01.dbf
/u02/app/oracle/oradata/APEXPDB/datafile/perfstat01.dbf
7 rows selected.
Step 3: Run spcreate.sql to Configure STATSPACK
The spcreate.sql script creates the PERFSTAT user, its objects, and the required packages. It's interactive, prompting for a password and tablespace choices, choosing the recommended defaults (marked with an asterisk) is generally the safest path unless you have a specific reason to deviate.
[oracle@apex ~]$ cd $ORACLE_HOME/rdbms/admin
[oracle@apex admin]$
[oracle@apex admin]$ ll | grep -i spcreate.sql
-rwxr-xr-x. 1 oracle oinstall 1796 May 29 2017 spcreate.sql
[oracle@apex admin]$
[oracle@apex admin]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Fri Apr 4 16:00:26 2025
Version 19.26.0.0.0
Copyright (c) 1982, 2024, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production
Version 19.26.0.0.0
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APEXPDB READ WRITE NO
SQL> alter session set container=APEXPDB;
Session altered.
SQL> !pwd
/u01/app/oracle/product/19.0.0/dbhome_1/rdbms/admin
SQL> @spcreate.sql
Session altered.
Choose the PERFSTAT user's password
-----------------------------------
Not specifying a password will result in the installation FAILING
Enter value for perfstat_password: YourP@ssw0rd
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 user'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
*
TABLESPACE_NAME CONTENTS
------------------------------ ---------------------
STATSPACK DEFAULT TABLESPACE
----------------------------
USERS PERMANENT
Pressing 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 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 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> -- Next two scripts run as perfstat user
SQL> ALTER SESSION SET CURRENT_SCHEMA = PERFSTAT;
Session altered.
... output continue ...
... output continue ...
NOTE:
SPCPKG complete. Please check spcpkg.lis for any errors.
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.
Important: use a real, unique password when prompted, and keep a secure note of it, you'll need it to connect as PERFSTAT in Step 6.
Step 4: Verify the STATSPACK Configuration
Confirm the level description view now exists and check timed_statistics, this parameter needs to be enabled for STATSPACK to capture meaningful timing data rather than just counts.
SQL> set pages 999 lines 180
SQL> col DESCRIPTION for a60
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
5 rows selected.
SQL>
SQL> show parameter timed_statistics
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
timed_statistics boolean TRUE
Step 5: Take a Manual Snapshot
Before setting up automatic scheduling, take one manual snapshot to confirm the whole configuration actually works end to end.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APEXPDB READ WRITE NO
SQL>
SQL> exec statspack.snap;
PL/SQL procedure successfully completed.
SQL>
SQL> select name, snap_id, to_char(snap_time, 'DD-MON-YYYY:HH24:MI:SS') "Date/Time"
from stats$snapshot, v$database;
NAME SNAP_ID Date/Time
--------- ---------- -----------------------------
APEXDB 1 04-APR-2025:16:14:01
1 row selected.
Step 6: Schedule Automatic Snapshots
Manually running statspack.snap every time isn't practical for ongoing monitoring, so we grant PERFSTAT the ability to create a scheduled job, then run spauto.sql to set up hourly snapshots automatically.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
2 PDB$SEED READ ONLY NO
3 APEXPDB READ WRITE NO
SQL> alter session set container=APEXPDB;
Session altered.
SQL> show user
USER is "SYS"
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APEXPDB READ WRITE NO
SQL> GRANT CREATE JOB TO PERFSTAT ;
Grant succeeded.
SQL> conn PERFSTAT/YourP@ssw0rd@APEXPDB
Connected.
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
----------
63
Job queue process
~~~~~~~~~~~~~~~~~
Below is the current setting of the job_queue_processes init.ora
parameter - the value for this parameter must be greater
than 0 to use automatic statistics gathering:
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
job_queue_processes integer 80
Next scheduled run
~~~~~~~~~~~~~~~~~~
The next scheduled run for this job is:
JOB NEXT_DATE NEXT_SEC
---------- --------- --------------------------------
63 05-APR-25 22:00:00
SQL>
SQL> SELECT job, what, next_date, broken FROM dba_jobs WHERE what LIKE '%statspack.snap%';
JOB WHAT NEXT_DATE B
---------- ------------------------------ -------------------- -
61 statspack.snap; 04-APR-2025 17:00:00 N
1 row selected.
Note the job number returned (63 in this example), you'll need it later if you ever want to modify or remove the scheduled job.
Step 7: Confirm Snapshots Are Being Generated Automatically
After waiting for a scheduled run or two, check that new snapshots are appearing on their own, without any manual intervention.
select name, snap_id, to_char(snap_time, 'DD-MON-YYYY:HH24:MI:SS') "Date/Time"
from stats$snapshot, v$database
order by 2;
NAME SNAP_ID Date/Time
--------- ---------- -----------------------------
APEXDB 1 04-APR-2025:16:14:01
APEXDB 2 04-APR-2025:17:00:00
APEXDB 3 04-APR-2025:18:00:00
Step 8: Generate a STATSPACK Report
With multiple snapshots collected, run spreport.sql to generate a readable performance report comparing any two snapshots, this is where STATSPACK actually becomes useful for diagnosing what changed or what was slow between two points in time.
SQL> show pdbs
CON_ID CON_NAME OPEN MODE RESTRICTED
---------- ------------------------------ ---------- ----------
3 APEXPDB READ WRITE NO
SQL> @?/rdbms/admin/spreport
Current Instance
~~~~~~~~~~~~~~~~
DB Id DB Name Inst Num Instance
----------- ------------ -------- ------------
1196724728 APEXDB 1 APEXDB
Instances in this Statspack schema
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
DB Id Inst Num DB Name Instance Host
----------- -------- ------------ ------------ ------------
1196724728 1 APEXDB APEXDB APEXDB
Using 1196724728 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 without specifying a number lists all completed snapshots.
Listing all Completed Snapshots
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Instance DB Name Snap Id Snap Started Level Comment
------------ ------------ --------- ----------------- ----- --------------------
APEXDB APEXDB 1 04 Apr 2025 16:14 5
2 05 Apr 2025 17:00 5
3 05 Apr 2025 18:00 5
Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 2
Begin Snapshot Id specified: 2
Enter value for end_snap: 3
End Snapshot Id specified: 3
Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is sp_2_3. To use this name, press to continue, otherwise enter an alternative.
Enter value for report_name: sp_2_3
Using the report name sp_2_3
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
3003259501 apexdb 1 02-Apr-25 20:14 19.0.0.0.0 NO
The report gets saved to the server directory you ran spreport from. Open it in a text editor to review wait events, top SQL, and load profile changes between the two snapshots you selected.
Thank you for reading!
I hope this content has been helpful to you. Your feedback and suggestions are always welcome. Feel free to leave a comment or reach out with any queries.
Abhishek Shrivastava
Oracle DBA with hands-on experience managing production Data Guard, RAC, GoldenGate, and APEX environments. I write practical, tested installation and troubleshooting guides based on real deployment work.
Comments
Post a Comment