Oracle STATSPACK: Installation, Snapshot Management, and Report Interpretation

The call comes in at 08:30: the database was slow between 02:00 and 04:00. Users noticed. Nobody raised an alert. You open the database and find nothing unusual — the problem has resolved itself.

Without performance data captured at the time of the incident, you are theorizing. You might guess it was a batch job, a long-running query, or a temporary resource spike. You might be right. But guessing is not diagnosis.

STATSPACK is what turns that 02:00 incident from a mystery into an investigation.

The answer

STATSPACK is Oracle’s built-in, license-free performance data repository. It captures cumulative database statistics as point-in-time snapshots stored in the PERFSTAT schema. A report comparing two snapshots shows what the database was doing during the interval between them: where elapsed time was spent, which SQL statements consumed the most resources, and how internal components behaved. It is available in all Oracle editions, requires no Diagnostics Pack license, and has been included since Oracle 8i.

Enable it before you need it. You cannot go back and capture what you did not record.

What you will learn

• When to use STATSPACK versus AWR and what each requires
• How to install STATSPACK, configure snapshot automation, and set the appropriate capture level
• How to generate a STATSPACK report and navigate its most diagnostic sections
• How to manage snapshot retention and purge data cleanly

Scope: Oracle Database 8i through 23ai, all editions. AWR comparison applies to Oracle 10g and later. SQL*Plus syntax used throughout (@? resolves to $ORACLE_HOME). Adjust paths for your environment.

STATSPACK or AWR: choosing the right tool

Both tools use the same principle — take periodic snapshots of cumulative performance statistics, then report on the delta between two snapshots. The difference is licensing.

If you hold a valid Diagnostics Pack license on Enterprise Edition, AWR provides richer data with less operational overhead. If you are on Standard Edition, or if your licensing scope does not cover Diagnostics Pack, STATSPACK is the correct tool. Using AWR without a Diagnostics Pack license is a compliance violation regardless of whether the data is useful.

How STATSPACK captures performance data

STATSPACK reads cumulative statistics from Oracle memory structures — primarily V$ views — at the moment a snapshot is taken. Each snapshot stores an absolute value for every counter at that point in time.

When you generate a report, STATSPACK computes the delta between the begin and end snapshots, then normalizes the results to per-second and per-transaction rates.

Snapshot N  (02:00)  ← absolute counters captured
    ↓
    delta = Snapshot N+1 − Snapshot N
    ↓
Snapshot N+1 (03:00)  ← absolute counters captured
    ↓
STATSPACK report covers the 02:00–03:00 interval

Shorter intervals produce more focused reports. Longer intervals average out spikes, which can hide a short but severe performance event inside an otherwise unremarkable hour.

Installation

STATSPACK scripts are located in $ORACLE_HOME/rdbms/admin/. Installation creates the PERFSTAT schema and all required objects.

Prerequisites

• Connected as SYSDBA
• A dedicated tablespace for PERFSTAT data (100 MB is a starting point; size based on snapshot frequency, retention period, and SQL capture level)
• Oracle 8.1.6 or later

Installation script

-- Connect as SYSDBA before running
@?/rdbms/admin/spcreate.sql

The script prompts for:

1. PERFSTAT password — choose a strong password; this schema owns all captured performance data
2. Default tablespace — specify a dedicated tablespace, not SYSTEM or SYSAUX
3. Temporary tablespace — typically TEMP

Expected outcome: the script confirms object creation without errors. A partial installation (errors mid-script) leaves the schema in an incomplete state. Drop and reinstall:

-- Remove a failed or unwanted STATSPACK installation
@?/rdbms/admin/spdrop.sql

Taking snapshots

Manual snapshot

-- Connect as SYSDBA or PERFSTAT
EXEC STATSPACK.SNAP;

This captures a level 5 snapshot by default. To specify a level explicitly:

EXEC STATSPACK.SNAP(I_SNAP_LEVEL => 7);

Snapshot levels and what they capture:

Level 5 is sufficient for most performance investigations. Level 7 adds segment-level I/O statistics useful when diagnosing hot objects or physical read patterns. Level 10 is rarely needed outside specific latch contention analysis and increases snapshot size meaningfully.

Automated snapshots

spauto.sql creates a DBMS_JOB that takes a level 5 snapshot every hour:

@?/rdbms/admin/spauto.sql

Verify the job is registered:

SELECT job, what, next_date, next_sec, interval
FROM dba_jobs
WHERE what LIKE '%STATSPACK%';

Hourly snapshots are a practical baseline for production environments. For a specific investigation requiring finer granularity, take manual snapshots at 15- or 30-minute intervals during the relevant window.

Adjusting SQL capture thresholds

SQL statements are only captured when they exceed resource thresholds. Check the current thresholds:

SELECT snap_level, executions_th, disk_reads_th, parse_calls_th,
       buffer_gets_th, sharable_mem_th, version_count_th
FROM stats$statspack_parameter;

To lower the buffer gets threshold and capture more SQL during an investigation:

EXEC STATSPACK.MODIFY_STATSPACK_PARAMETER(
    I_SNAP_LEVEL     => 5,
    I_BUFFER_GETS_TH => 1000
);

Lower thresholds increase the volume of SQL captured and the storage consumed per snapshot. Reduce thresholds for targeted investigations; restore the defaults when the investigation is complete.

Generating the report

-- Connect as PERFSTAT or SYSDBA
@?/rdbms/admin/spreport.sql

The script prompts for a begin snapshot ID, an end snapshot ID, and an output filename. To list available snapshots first:

SELECT snap_id,
       TO_CHAR(snap_time, 'DD-MON-YYYY HH24:MI') AS snap_time,
       snap_level
FROM stats$snapshot
ORDER BY snap_id;

The report is a plain text file. The most diagnostic content is in the first half. Start at the top and work down.

Reading the report: sections that matter

Top 5 Timed Events

Read this section first. It shows where the database spent its time during the interval.

Top 5 Timed Events
~~~~~~~~~~~~~~~~~~                                                     % Total
Event                                               Waits    Time (s) Db Time
----------------------------------------- ----------- ---------- -------
db file sequential read                        52,194      1,842   61.4
log file sync                                   8,321        301   10.0
CPU time                                                    287    9.6
db file scattered read                          4,118        198    6.6
latch: cache buffers chains                     1,021         89    3.0

Three diagnostic patterns:

• CPU time dominant — workload is CPU-bound; investigate SQL efficiency, excessive hard parsing, or workload volume
• I/O wait events dominant (db file sequential read, db file scattered read) — workload is I/O-bound; investigate index coverage, full table scans, and buffer cache sizing
• Contention events dominant (log file sync, enq:, latch:) — investigate redo configuration, commit frequency, or specific resource contention

A healthy OLTP database typically shows CPU near the top, with I/O events secondary. When a wait event displaces CPU below the second or third position, that event is the primary area of investigation.

SQL Statistics

The report includes several SQL ordered lists. For most investigations, begin with:

• SQL ordered by Elapsed Time — the most expensive statements overall; start here before narrowing to CPU or I/O
• SQL ordered by CPU Time — CPU-intensive statements; relevant when CPU dominates Top 5
• SQL ordered by Gets — high logical I/O; often indicates full scans or poorly selective predicates
• SQL ordered by Reads — high physical I/O; relevant when I/O events dominate Top 5

Each entry shows the SQL hash value, execution count, elapsed time, and the first 80 characters of the statement. Retrieve the full text:

SELECT sql_text
FROM stats$sqltext
WHERE hash_value = <hash_value>
ORDER BY piece;

Instance Activity Statistics

Shows absolute counts normalized to per-second and per-transaction rates for key workload indicators:

• physical reads — total disk block reads during the interval
• logical reads — total buffer cache reads (consistent gets + db block gets)
• redo size — volume of redo generated; indicator of write-intensive workload
• user commits and user rollbacks — transaction volume and rollback rate
• parse count (hard) — high hard parse count suggests cursor-sharing problems or missing bind variables
• execute count — total statement executions; compare with parse counts to assess parse-to-execute ratio

Compare these values across reports taken during normal and abnormal periods. Deviations from established baselines are the starting point, not the exception to explain away.

Buffer Pool Statistics

Shows buffer cache hit ratio and dirty buffer flush activity. A sustained hit ratio below 95% on an OLTP workload can indicate an undersized buffer cache. However, a large full table scan will depress the ratio without indicating a real problem — always interpret this section alongside the SQL Statistics to determine whether the I/O is expected or avoidable.

Initialization Parameters

The final section lists current initialization parameter values. When comparing reports across different periods or instances, parameter differences explain behavioral changes that would otherwise appear anomalous.

Managing snapshot retention

STATSPACK does not purge data automatically. Without active management, the PERFSTAT tablespace fills up and snapshot jobs begin failing silently.

Purge a specific range of snapshots:

@?/rdbms/admin/sppurge.sql
-- prompts for begin and end snapshot IDs

Or call the procedure directly:

EXEC STATSPACK.PURGE(
    I_BEGIN_SNAP        => 1,
    I_END_SNAP          => 100,
    I_SNAP_LEVEL        => 5,
    I_PURGE_ORPHAN_ROWS => TRUE
);

To remove all snapshot data while preserving the schema and configuration:

@?/rdbms/admin/sptrunc.sql

A practical retention target: 30 days of hourly snapshots. For a busy OLTP database at level 5, this typically requires 2–10 GB depending on SQL volume and the number of distinct SQL hashes captured. Monitor tablespace usage during the first week of operation and adjust the tablespace allocation or retention period before the first alert.

Do not do this

• Do not install STATSPACK into SYSTEM or SYSAUX. Use a dedicated tablespace sized for your retention target.
• Do not generate a report from a single snapshot. A single snapshot is a cumulative total since instance startup, not a workload period. You always need two snapshots.
• Do not use Level 10 in production without understanding the storage impact. It is rarely required outside specific latch contention investigations.
• Do not lower SQL capture thresholds permanently. Lower them for an investigation; restore the defaults when done.
• Do not purge snapshots that cover an open incident or an unresolved performance period. Once purged, the data cannot be recovered.
• Do not use AWR-based scripts or views as a substitute when Diagnostics Pack is not licensed. STATSPACK and AWR are separate repositories.

Official references

• Gathering Database Statistics — Oracle Database Performance Tuning Guide 19c
• Oracle Database Licensing Information User Manual

Conclusion

The 02:00 incident that generated no alerts and left no visible trace in the current database state is diagnosable — provided the snapshot job was running and the data was retained. STATSPACK has been included with every Oracle release since 8i, requires no additional license, and installs in minutes. There is no good reason to encounter a performance incident without it. Install it, automate the snapshots, and establish a retention policy before the next incident. Evidence collected before you know you need it is the only kind that matters.

Continue reading

• Oracle Time Zone Layers: Why Changing One Setting Is Never Enough
• One Row. Two Indexes. One Deadlock.

Marios Pavlidis Principal Database Administrator