If you're running Oracle 19c Standard Edition, you don't get access to AWR, ADDM, or ASH — those are part of the Diagnostics and Tuning Packs, which are licensed Enterprise Edition add-ons. Querying DBA_HIST_* views or calling dbms_workload_repository on an SE database isn't just unsupported, it's a license compliance risk that auditors will flag immediately.
The good news: Statspack has shipped with every Oracle release since 8i, it's fully included in SE at no extra cost, and it still works perfectly well on 19c. It captures point-in-time snapshots of V$ performance views — SQL execution stats, wait events, latches, segment stats — into a dedicated PERFSTAT schema, so you can diff two snapshots and get a report that does the same job as an AWR report.
This post walks through a production-ready setup for a common requirement: a snapshot every 15 minutes, with a 7-day rolling retention window. At that cadence you're looking at 96 snapshots a day, or roughly 672 snapshots in the repository once it reaches steady state — so sizing and automated purging matter from day one.
How Statspack Actually Works
Three moving parts:
The repository — a dedicated tablespace and schema (PERFSTAT) holding around 70 STATS$* tables, including STATS$SNAPSHOT, STATS$SQL_SUMMARY, STATS$SYSTEM_EVENT, and STATS$SEG_STAT.
Snapshot levels — these control how much data gets captured per snap:
- Level 0: basic performance data only
- Level 5 (the default, and the right choice for 15-minute intervals): adds SQL statements above resource thresholds
- Level 7: adds segment-level statistics — useful when actively chasing hot blocks or I/O contention, but heavier
- Level 10: adds child latch statistics — rarely needed, and real I/O overhead on busy systems
Scheduling and purging — older guides point you to dbms_job via spauto.sql, but on 19c you should use DBMS_SCHEDULER instead. It's more robust, has built-in retry logic, and it's already the standard mechanism for every other piece of database automation you're running.
Given a 15-minute interval, start at Level 5. Level 7 at that frequency on a busy OLTP system — especially something financial or healthcare-related — can generate noticeable shared-pool churn. Bump to Level 7 temporarily during an active investigation, then drop back down.
Step-by-Step Installation
1. Pre-checks
-- Confirm job queue processes are enabled (required for DBMS_SCHEDULER)
SHOW PARAMETER job_queue_processes;
-- Confirm Statspack isn't already installed
SELECT username FROM dba_users WHERE username = 'PERFSTAT';
2. Create a dedicated tablespace
Keep PERFSTAT out of SYSAUX or USERS. Isolating it makes space management and eventual removal far easier.
CREATE TABLESPACE perfstat
DATAFILE '/u02/oradata/ORCL/perfstat01.dbf' SIZE 500M
AUTOEXTEND ON NEXT 100M MAXSIZE 4G
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
At 15-minute intervals with 7-day retention, expect the repository to land somewhere in the low hundreds of MB up to 1–2GB depending on SQL diversity on the instance. The 500M start / 4G ceiling above is a safe, conservative starting point — check actual growth after week one and adjust.
3. Run the install script as SYS
sqlplus / as sysdba
SQL> @?/rdbms/admin/spcreate.sql
It will interactively prompt for the PERFSTAT password (use something strong — this account is never meant to be logged into interactively), the default tablespace (perfstat), and a temporary tablespace (your existing TEMP).
Check the spool files — spcpkg.lis, spctab.lis, spcusr.lis — for any ORA- errors. A clean install ends with the package compiling without issue.
4. Grant CREATE JOB and verify
GRANT CREATE JOB TO perfstat;
-- Sanity check: should be roughly 70 tables
SELECT COUNT(*) FROM dba_tables WHERE owner = 'PERFSTAT';
5. Take a manual baseline snapshot
CONNECT perfstat/<password>
EXEC statspack.snap(i_snap_level => 5);
SELECT snap_id, snap_time FROM stats$snapshot ORDER BY snap_id;
Confirm this runs cleanly before automating anything.
6. Schedule snapshots every 15 minutes
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_SNAPSHOT',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN statspack.snap(i_snap_level => 5); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=MINUTELY;INTERVAL=15',
enabled => TRUE,
comments => 'Statspack snapshot every 15 minutes - level 5'
);
END;
/
7. Schedule the purge job to enforce 7-day retention
BEGIN
DBMS_SCHEDULER.CREATE_JOB (
job_name => 'STATSPACK_PURGE',
job_type => 'PLSQL_BLOCK',
job_action => 'BEGIN statspack.purge(i_purge_before_date => SYSDATE - 7, i_extended_purge => TRUE); END;',
start_date => SYSTIMESTAMP,
repeat_interval => 'FREQ=DAILY;BYHOUR=2;BYMINUTE=30',
enabled => TRUE,
comments => 'Purge statspack snapshots older than 7 days'
);
END;
/
i_extended_purge => TRUE matters here — it cleans up the SQL text and segment rows tied to purged snapshots, not just the STATS$SNAPSHOT headers. Skip it and you'll end up with orphaned rows in tables like STATS$SQL_SUMMARY, and the repository won't shrink the way you'd expect. Schedule the purge for a genuinely quiet window — 2:30 AM above is just an example; pick whatever fits your environment's actual lull after batch processing.
Validating the Setup
-- Confirm both jobs exist and are scheduled correctly
SELECT job_name, state, repeat_interval, next_run_date
FROM dba_scheduler_jobs
WHERE owner = 'PERFSTAT';
-- Confirm jobs are actually firing (check after 30-60 minutes)
SELECT job_name, status, actual_start_date, run_duration
FROM dba_scheduler_job_run_details
WHERE owner = 'PERFSTAT'
ORDER BY actual_start_date DESC;
-- Confirm snapshot cadence is roughly 15 minutes apart
SELECT snap_id, snap_time
FROM stats$snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;
-- Confirm retention is holding at 7 days
SELECT MIN(snap_time) AS oldest_snap, MAX(snap_time) AS newest_snap,
COUNT(*) AS total_snapshots
FROM stats$snapshot;
-- Track tablespace growth weekly
SELECT tablespace_name,
ROUND(SUM(bytes)/1024/1024,1) AS allocated_mb
FROM dba_data_files
WHERE tablespace_name = 'PERFSTAT'
GROUP BY tablespace_name;
Once the purge job has run a few cycles, total_snapshots should settle around 672, and oldest_snap should never drift past 7 days back.
Generating a report
sqlplus perfstat/<password>
SQL> @?/rdbms/admin/spreport.sql
It prompts for begin and end snap IDs and produces a text report covering top SQL by buffer gets and elapsed time, wait events, load profile, and instance efficiency ratios — functionally the SE equivalent of an AWR report.
Risks and Things to Watch
Overhead at this frequency is generally low — sub-second, mostly recursive SQL against V$ views — but on a heavily loaded system you may see brief library-cache/shared-pool activity during each snap. Validate this in non-prod first.
Threshold tuning matters on high-transaction systems. Statspack only captures SQL exceeding thresholds defined in STATS$STATSPACK_PARAMETER. On a busy financial workload, consider raising executions_th so the repository doesn't fill up with noise from trivial, high-frequency statements.
Repository growth can surprise you. A runaway job or unusual reporting query can spike SQL diversity and balloon snapshot size fast. Use the weekly tablespace query above as an early warning — don't rely on AUTOEXTEND MAXSIZE alone to "handle it," since that just delays a disk-full event rather than preventing it.
Retention vs. compliance. A hard 7-day purge can conflict with audit retention requirements in regulated environments. If there's any chance you'll need performance evidence beyond 7 days for an incident review, consider spooling spreport.sql output to an archive nightly, rather than treating the live repository as your only record.
Account hygiene. PERFSTAT should never be used for interactive login. Use a strong password, keep it out of the DBA role, and document it as a service account with a rotation policy in your CMDB.
After patching or upgrades, confirm the scheduler jobs are still in a healthy state:
SELECT job_name, state FROM dba_scheduler_jobs WHERE owner = 'PERFSTAT';
Rolling Back
To pause without losing data — useful during a change freeze:
EXEC DBMS_SCHEDULER.DISABLE('PERFSTAT.STATSPACK_SNAPSHOT');
EXEC DBMS_SCHEDULER.DISABLE('PERFSTAT.STATSPACK_PURGE');
To remove Statspack entirely:
sqlplus / as sysdba
SQL> @?/rdbms/admin/spdrop.sql
-- Only after confirming nothing else relies on this tablespace
DROP TABLESPACE perfstat INCLUDING CONTENTS AND DATAFILES;
Final Thoughts
Statspack isn't as polished as AWR, but it's a fully licensed, zero-cost, battle-tested tool that does the job well on Standard Edition. The setup above — Level 5 snapshots every 15 minutes with extended purging at 7 days — gives you solid visibility into SQL and wait-event trends without the licensing risk. Run it through a full 7-day cycle in non-production first to confirm growth rate and purge behavior before promoting it to a mission-critical production instance.
Have you run Statspack on a 19c Standard Edition environment? Drop your tuning thresholds or retention strategy in the comments — always curious how other teams are handling this on regulated, mission-critical systems.
No comments:
Post a Comment