Oracle Statspack is a powerful free tool for performance analysis in Standard Edition (SE2) and the latest Oracle Database 26ai Free environments. Even in licensed environments where AWR (Automatic Workload Repository) is unavailable, you can visualize the database’s operating status over time using Statspack. This article explains the Statspack installation procedure in a CDB/PDB configuration, how to set up automatic snapshots every hour, and basic report generation methods using the Oracle Database 26ai Free environment.
- Conclusion: Statspack is Usable in 26ai Free
- What is Statspack? Difference from AWR
- Step 1: Preparation and Connection to PDB
- Step 2: Installation of Statspack (spcreate)
- Step 3: Setting Up Automatic Snapshot Acquisition (Hourly)
- Step 4: Checking Acquired Data and Creating Reports
- Troubleshooting: Common Errors
- Operational and Monitoring Notes
- Summary
Conclusion: Statspack is Usable in 26ai Free
First, here is a summary of the conclusion and key points.
- Availability: It operates without issues in Oracle Database 26ai Free, just as in past versions.
- Important Note: Installation in
CDB$ROOTis not supported. You must connect to a PDB (Pluggable Database) to install it. - Quick Steps:
- Connect to the target PDB (
alter session set container = ...) - Create a dedicated tablespace (for PERFSTAT)
- Execute
@?/rdbms/admin/spcreate - Enable automatic acquisition with
@?/rdbms/admin/spauto
- Connect to the target PDB (
By reading this article, you will be able to build a professional DB performance analysis infrastructure in the latest 26ai environment without incurring costs.
What is Statspack? Difference from AWR
Here is a simple explanation for beginners.
Overview of Statspack
This is a tool that has existed since the Oracle 8i era, used to capture and accumulate performance statistical information. It calculates the difference between specific points in time (snapshots) and generates reports on “which SQL is heavy” or “what wait events are occurring.”
Difference from AWR (Important)
AWR, which can be used in Enterprise Edition (EE), is very convenient, but it requires the paid “Diagnostics Pack” option. On the other hand, Statspack is available in Standard Edition and the Free version without any additional cost. Although 26ai Free is a developer license, there are no restrictions on the use of Statspack.
Step 1: Preparation and Connection to PDB
In a multitenant environment (CDB/PDB), Statspack is installed per PDB. Trying to install it in CDB$ROOT (Container Root) will result in an error or incorrect operation. First, verify the target PDB and switch the connection.
-- Check the current container
SHOW CON_NAME
-- Check the list of PDBs (Verify the default PDB name for 26ai Free. Example: FREEPDB1)
SHOW PDBS
-- Switch to PDB (Important!)
ALTER SESSION SET CONTAINER = FREEPDB1;
-- Re-confirm that the switch was successful
SHOW CON_NAME
Creating a Tablespace for the PERFSTAT User
You will be asked for a default tablespace within the installation script. While it is possible to use SYSAUX, it is strongly recommended to create a dedicated tablespace to prevent data bloating.
-- Example of creating a tablespace (Please change the path according to your environment)
-- If OMF (Oracle Managed Files) is enabled, the datafile specification can be omitted
CREATE TABLESPACE perfstat_ts
DATAFILE SIZE 100M AUTOEXTEND ON NEXT 100M MAXSIZE 2G;
-- Check temporary tablespace (Usually TEMP exists)
SELECT tablespace_name FROM dba_temp_files;
Step 2: Installation of Statspack (spcreate)
With the connection established to the PDB, execute the installation script spcreate.sql.
-- Execute with sysdba privileges
CONN / AS SYSDBA
-- Verify again if connected to the PDB (Re-connecting might return you to CDB$ROOT)
ALTER SESSION SET CONTAINER = FREEPDB1;
-- Execute installation script
@?/rdbms/admin/spcreate
During execution, you will input the following 3 items interactively:
- PERFSTAT user’s password: Enter an arbitrary password (e.g.,
perfstat) - Default tablespace: Enter
perfstat_tscreated earlier - Temporary tablespace: Enter
temp
Once these are completed, spcusr.sql (user creation), spctab.sql (table creation), and spcpkg.sql (package creation) are called automatically, and the installation finishes. If “No errors” is displayed at the end, it is successful.
Note: If the installation fails, please delete it using
@?/rdbms/admin/spdropand then try executing it again.
Step 3: Setting Up Automatic Snapshot Acquisition (Hourly)
Since Statspack is a tool that looks at the difference between “point A” and “point B,” it is necessary to take snapshots periodically. If you use spauto.sql provided by Oracle, you can easily register a job (DBMS_JOB) that acquires a snapshot every hour.
-- Connect as PERFSTAT user
CONN perfstat/perfstat@localhost:1521/freepdb1
-- Execute script for automatic acquisition
@?/rdbms/admin/spauto
-- Check the registered job
SELECT job, next_date, next_sec, interval, what FROM user_jobs;
Checking JOB_QUEUE_PROCESSES
To run DBMS_JOB, the initialization parameter job_queue_processes must be greater than 0.
SHOW PARAMETER job_queue_processes
-- If 0, change the setting (Example: 4)
ALTER SYSTEM SET job_queue_processes = 4 SCOPE=BOTH;
Step 4: Checking Acquired Data and Creating Reports
Checking Snapshot Acquisition Status
To check if data is being accumulated, view the STATS$SNAPSHOT view.
-- List of acquired snapshots
SELECT snap_id, snap_time, snap_level
FROM stats$snapshot ORDER BY snap_id;
Execution Example
SQL> alter session set nls_date_format='YYYY/MM/DD HH24:MI:SS';
SQL> SELECT snap_id, snap_time, snap_level
2 FROM stats$snapshot ORDER BY snap_id;
Snap Id SNAP_TIME SNAP_LEVEL
--------- ------------------- ----------
1 2026/01/20 22:54:15 5
2 2026/01/20 23:00:00 5
3 2026/01/21 00:00:00 5
4 2026/01/21 01:00:02 5
11 2026/01/21 02:00:02 5
12 2026/01/21 03:00:00 5
13 2026/01/21 04:00:00 5
14 2026/01/21 05:00:01 5
15 2026/01/21 06:00:00 5
16 2026/01/21 07:00:00 5
17 2026/01/21 08:00:00 5
18 2026/01/21 09:00:00 5
19 2026/01/21 10:00:00 5
20 2026/01/21 11:00:00 5
21 2026/01/21 12:00:00 5
If you want to take a snapshot manually right now, execute the following:
EXEC statspack.snap;
Creating a Performance Report
Specify the Begin Snap ID and End Snap ID for the time period where the issue occurred to output a report.
-- Execute as PERFSTAT user
@?/rdbms/admin/spreport
- Begin Snapshot Id: Enter the ID for the start point
- End Snapshot Id: Enter the ID for the end point
- Report Name: Output filename (Default is OK)
If you open the output .lis file (text file) in an editor, you can check wait events and high-load SQLs.
Statspack obtained
[oracle@26ai-single admin]$ cat sp01.lst
STATSPACK report for
Database DB Id Instance Inst Num Startup Time Release RAC
~~~~~~~~ ----------- ------------ -------- --------------- ----------- ---
1491844267 FREE 1 20-Jan-26 21:12 23.0.0.0.0 NO
Host Name Platform CPUs Cores Sockets Memory (G)
~~~~ ---------------- ---------------------- ----- ----- ------- ------------
26ai-single Linux x86 64-bit 1 1 1 3.5
Snapshot Snap Id Snap Time Sessions Curs/Sess Comment
~~~~~~~~ ---------- ------------------ -------- --------- ------------------
Begin Snap: 2 20-Jan-26 23:00:00 2 8.5
End Snap: 3 21-Jan-26 00:00:00 2 8.5
Elapsed: 60.00 (mins) Av Act Sess: 0.0
DB time: 0.06 (mins) DB CPU: 0.05 (mins)
Cache Sizes Begin End
~~~~~~~~~~~ ---------- ----------
Buffer Cache: 496M 480M Std Block Size: 8K
Shared Pool: 496M 512M Log Buffer: 4,192K
Load Profile Per Second Per Transaction Per Exec Per Call
~~~~~~~~~~~~ ------------------ ----------------- ----------- -----------
DB time(s): 0.0 0.8 0.00 0.37
DB CPU(s): 0.0 0.6 0.00 0.29
Redo size: 2,522.3 1,816,055.2
Logical reads: 14.4 10,381.0
Block changes: 2.9 2,075.0
Physical reads: 0.0 0.0
Physical writes: 0.2 131.2
User calls: 0.0 2.0
Parses: 0.8 575.6
Hard parses: 0.1 36.4
W/A MB processed: 0.0 7.6
Logons: 0.0 2.0
Executes: 1.5 1,094.6
Rollbacks: 0.0 0.0
Transactions: 0.0
Instance Efficiency Indicators
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 100.00 Redo NoWait %: 100.00
Buffer Hit %: 100.00 Optimal W/A Exec %: 100.00
Library Hit %: 94.67 Soft Parse %: 93.68
Execute to Parse %: 47.41 Latch Hit %: 100.00
Parse CPU to Parse Elapsd %: 93.02 % Non-Parse CPU: 71.12
Shared Pool Statistics Begin End
------ ------
Memory Usage %: 90.97 91.14
% SQL with executions>1: 66.87 63.73
% Memory for SQL w/exec>1: 69.86 65.00
Top 5 Timed Events Avg %Total
~~~~~~~~~~~~~~~~~~ wait Call
Event Waits Time (s) (ms) Time
----------------------------------------- ------------ ----------- ------ ------
resmgr:cpu quantum 75 5 70 58.6
CPU time 3 30.7
direct path write 46 1 18 9.3
latch: shared pool 3 0 19 .6
local write wait 4 0 4 .2
-------------------------------------------------------------
Host CPU (CPUs: 1 Cores: 1 Sockets: 1)
~~~~~~~~ Load Average
Begin End User System Idle WIO WCPU
------- ------- ------- ------- ------- ------- --------
0.36 0.74 1.10 0.27 90.13 0.47
Instance CPU
~~~~~~~~~~~~ % Time (seconds)
-------- --------------
Host: Total time (s): 3,501.5
Host: Busy CPU time (s): 345.7
% of time Host is Busy: 9.9
Instance: Total CPU time (s): 2.9
% of Busy CPU used for Instance: 0.8
Instance: Total Database time (s): 3.7
%DB time waiting for CPU (Resource Mgr): 0.0
Virtual Memory Paging
~~~~~~~~~~~~~~~~~~~~~
KB paged out per sec: 1.9
KB paged in per sec: .2
Memory Statistics Begin End
~~~~~~~~~~~~~~~~~ ------------ ------------
Host Mem (MB): 3,628.7 3,628.7
SGA use (MB): 1,081.1 1,081.1
PGA use (MB): 16.3 21.1
% Host Mem used for SGA+PGA: 30.2 30.4
-------------------------------------------------------------
Time Model System Stats DB/Inst: FREE/FREE Snaps: 2-3
-> Ordered by % of DB time desc, Statistic name
Statistic Time (s) % DB time
----------------------------------- -------------------- ---------
sql execute elapsed time 3.5 94.6
DB CPU 2.9 76.6
parse time elapsed 1.6 43.4
hard parse elapsed time 1.6 42.3
PL/SQL execution elapsed time 0.1 2.8
hard parse (sharing criteria) elaps 0.1 1.4
PL/SQL compilation elapsed time 0.0 .9
repeated bind elapsed time 0.0 .1
DB time 3.7
-------------------------------------------------------------
Foreground Wait Events DB/Inst: FREE/FREE Snaps: 2-3
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
direct path write 32 0 1 18 6.4 6.4
resmgr:cpu quantum 3 0 0 11 0.6 .4
library cache lock 1 0 0 15 0.2 .2
Allocate UGA memory from OS 130 0 0 0 26.0 .0
Allocate CGA memory from OS 55 0 0 0 11.0 .0
control file sequential read 91 0 0 0 18.2 .0
Allocate PGA memory from OS 98 0 0 0 19.6 .0
Disk file operations I/O 14 0 0 0 2.8 .0
-------------------------------------------------------------
Background Wait Events DB/Inst: FREE/FREE Snaps: 2-3
-> Only events with Total Wait Time (s) >= .001 are shown
-> ordered by Total Wait Time desc, Waits desc (idle events last)
Avg %Total
%Tim Total Wait wait Waits Call
Event Waits out Time (s) (ms) /txn Time
---------------------------- ------------ ---- ---------- ------ -------- ------
resmgr:cpu quantum 72 0 5 73 14.4 58.2
direct path write 14 0 0 19 2.8 2.9
latch: shared pool 3 0 0 19 0.6 .6
local write wait 4 0 0 4 0.8 .2
reliable message 37 0 0 0 7.4 .1
db file sequential read 13 0 0 1 2.6 .1
Allocate UGA memory from OS 66 0 0 0 13.2 .0
control file sequential read 78 0 0 0 15.6 .0
Allocate CGA memory from OS 52 0 0 0 10.4 .0
-------------------------------------------------------------
:
Troubleshooting: Common Errors
Q. ORA-65049: NO_PDB_CHECK mode does not allow creation of local users or roles
- Cause: You are executing
spcreatewhile still connected toCDB$ROOT(Container Root). - Solution: Execute
alter session set container = <PDB Name>;to switch to the PDB, then install.
Q. The job is not running (Snapshots are not increasing)
- Cause:
job_queue_processesmight be 0, or the PDB might not be OPEN. - Solution: Check the parameters and ensure the PDB is open in READ WRITE mode.
Operational and Monitoring Notes
- Disk Space Monitoring: Statspack continues to accumulate data unless deleted. Periodically delete unnecessary data (using
sppurge.sqlorsptrunc.sql). - Snapshot Level: The default is “Level 5”. If you want details up to SQL execution plans, set it to “Level 6” or higher, but since this increases load, 5 is usually sufficient.
- Impact: A momentary load occurs when acquiring a snapshot. Avoid acquiring at excessive frequencies, such as every minute.
Summary
- Statspack is actively usable even in 26ai Free.
- Always connect to the PDB before installing.
- You can start 1-hour automatic acquisition immediately using
spauto.sql. - This is an essential skill for performance analysis in Standard Edition operations and learning environments.
Start by installing it in your own verification environment (26ai Free) and browsing through the reports.
[reference]
Get Started with Performance Tuning
This article explains the procedure targeting Oracle Database 26ai Free. Verification for this article was performed on the Free version, so specifications or default values may differ slightly when the formal version (Enterprise Edition, etc.) is released.
コメント