Oracle Statspack - Install, Configure & Generate Reports

Oracle Statspack - Install, Configure & Generate Reports

Statspack is Oracle’s free, pre-AWR performance repository, available in all Oracle editions including Standard Edition where AWR requires a Diagnostics Pack license. It captures the same broad categories of performance data as AWR using a set of STATS$ tables in the database and provides comparable reports via SQL*Plus scripts.

Overview

Statspack vs AWR

Feature	Statspack	AWR
License required	None (free)	Diagnostics Pack
Available editions	All editions	Enterprise only
Data storage	STATS$ tables in PERFSTAT schema	SYSAUX tablespace (WRH$ tables)
Report script	spreport.sql	awrrpt.sql
SQL detail	Top N SQL	Top N SQL + full plan
ASH equivalent	None	V$ACTIVE_SESSION_HISTORY
Minimum version	Oracle 8.1.6	Oracle 10g

Use Statspack when AWR is unavailable (Standard Edition, older databases, no Diagnostics Pack license).

---

Installation

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

Prerequisites

-- Statspack requires a dedicated tablespace
-- Create one before running spcreate.sql
CREATE TABLESPACE PERFSTAT
  DATAFILE '/oradata/perfstat01.dbf'
  SIZE 500M
  AUTOEXTEND ON NEXT 100M
  MAXSIZE 5G
  EXTENT MANAGEMENT LOCAL
  SEGMENT SPACE MANAGEMENT AUTO;

-- Confirm you are connected as SYSDBA
SELECT * FROM v$pwfile_users WHERE sysdba = 'TRUE';

Run the Creation Script

-- Connect as SYSDBA in SQL*Plus, then:
@$ORACLE_HOME/rdbms/admin/spcreate.sql
-- Prompts for:
--   PERFSTAT password
--   Default tablespace for PERFSTAT (use PERFSTAT tablespace above)
--   Temporary tablespace for PERFSTAT (use TEMP)

Verify Installation

-- Confirm the PERFSTAT user and objects exist
SELECT
  object_type,
  COUNT(*) AS object_count
FROM dba_objects
WHERE owner = 'PERFSTAT'
GROUP BY object_type
ORDER BY object_count DESC;

-- Should show: TABLE, INDEX, SEQUENCE, PROCEDURE, PACKAGE, VIEW, SYNONYM

-- Check core tables are present
SELECT table_name
FROM dba_tables
WHERE owner = 'PERFSTAT'
  AND table_name IN (
    'STATS$SNAPSHOT',
    'STATS$SQL_SUMMARY',
    'STATS$SYSSTAT',
    'STATS$SYSTEM_EVENT',
    'STATS$SGASTAT',
    'STATS$PARAMETER',
    'STATS$FILESTATXS'
  )
ORDER BY table_name;

---

Snapshot Configuration

Manual Snapshots

-- Connect as PERFSTAT or SYSDBA, then take a snapshot
EXECUTE STATSPACK.SNAP;

-- Snapshot with explicit level (default is level 5)
EXECUTE STATSPACK.SNAP(i_snap_level => 7);

-- Snapshot with custom SQL threshold
EXECUTE STATSPACK.SNAP(
  i_snap_level        => 6,
  i_executions_th     => 100,   -- Only capture SQL with 100+ executions
  i_disk_reads_th     => 1000,  -- Only capture SQL with 1000+ disk reads
  i_parse_calls_th    => 1000,  -- Only capture SQL with 1000+ parse calls
  i_buffer_gets_th    => 10000  -- Only capture SQL with 10000+ buffer gets
);

Snapshot Levels

Level	Data Captured
0	General instance statistics only
5	Level 0 + SQL statements (default)
6	Level 5 + SQL execution plans
7	Level 6 + segment-level statistics
10	Level 7 + latching statistics (high overhead)

-- Check current default snap level
SELECT snap_level, num_sql, executions_th, disk_reads_th, buffer_gets_th
FROM stats$statspack_parameter;

-- Change the default level
EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER(i_snap_level => 7);

-- Change SQL capture thresholds
EXECUTE STATSPACK.MODIFY_STATSPACK_PARAMETER(
  i_snap_level     => 7,
  i_executions_th  => 50,
  i_disk_reads_th  => 500,
  i_buffer_gets_th => 5000
);

Automatic Snapshots with spauto.sql

-- spauto.sql creates a DBMS_JOB to take snapshots every hour
-- Run as PERFSTAT or SYSDBA:
@$ORACLE_HOME/rdbms/admin/spauto.sql

-- Or create the job manually for more control
VARIABLE jobno NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job        => :jobno,
    what       => 'STATSPACK.SNAP;',
    next_date  => TRUNC(SYSDATE + 1/24, 'HH'),
    interval   => 'TRUNC(SYSDATE + 1/24, ''HH'')'  -- Every hour on the hour
  );
  COMMIT;
END;
/

PRINT jobno;

-- Verify the job
SELECT
  job,
  what,
  TO_CHAR(next_date, 'DD-MON-YY HH24:MI:SS') AS next_run,
  interval,
  broken,
  failures
FROM dba_jobs
WHERE what LIKE '%STATSPACK%';

---

Generating Reports

Standard Statspack Report

-- Interactive report generation in SQL*Plus
-- Connect as PERFSTAT or SYSDBA:
@$ORACLE_HOME/rdbms/admin/spreport.sql
-- Prompts:
--   DB ID (usually just press Enter for current DB)
--   Instance number
--   Begin snapshot ID
--   End snapshot ID
--   Report filename

Finding Snapshot IDs

-- List available snapshots for report selection
SELECT
  snap_id,
  TO_CHAR(snap_time, 'DD-MON-YY HH24:MI:SS') AS snap_time,
  snap_level,
  startup_time
FROM stats$snapshot
ORDER BY snap_id DESC
FETCH FIRST 50 ROWS ONLY;

-- Snapshots in a specific date range
SELECT
  snap_id,
  TO_CHAR(snap_time, 'DD-MON-YY HH24:MI') AS snap_time,
  snap_level
FROM stats$snapshot
WHERE snap_time BETWEEN TO_DATE('2025-03-01 06:00', 'YYYY-MM-DD HH24:MI')
                    AND TO_DATE('2025-03-01 10:00', 'YYYY-MM-DD HH24:MI')
ORDER BY snap_id;

Non-Interactive Report Generation

-- Generate a text report programmatically (no prompts)
-- Run as PERFSTAT:
DEFINE begin_snap = 1000
DEFINE end_snap   = 1010
DEFINE report_name = '/tmp/statspack_report.txt'

@$ORACLE_HOME/rdbms/admin/spreport.sql

-- Or use sprepins.sql for a specific instance (RAC)
@$ORACLE_HOME/rdbms/admin/sprepins.sql

---

Querying STATS$ Tables Directly

Top SQL by Elapsed Time

-- Top 20 SQL statements between two snapshots (sorted by elapsed time)
SELECT *
FROM (
  SELECT
    sql.sql_text,
    sql.executions,
    ROUND(sql.elapsed_time / 1000000, 2)                    AS elapsed_secs,
    ROUND(sql.elapsed_time / NULLIF(sql.executions, 0) / 1000000, 4) AS elapsed_per_exec,
    sql.buffer_gets,
    ROUND(sql.buffer_gets / NULLIF(sql.executions, 0))      AS gets_per_exec,
    sql.disk_reads,
    sql.parse_calls,
    sql.rows_processed,
    sql.module,
    sql.hash_value
  FROM stats$sql_summary sql
  WHERE sql.snap_id = &end_snap_id
    AND sql.dbid   = (SELECT dbid FROM v$database)
    AND sql.instance_number = (SELECT instance_number FROM v$instance)
    AND sql.executions > 0
  ORDER BY sql.elapsed_time DESC
)
WHERE ROWNUM <= 20;

Top SQL by Buffer Gets (Logical Reads)

SELECT *
FROM (
  SELECT
    SUBSTR(sql.sql_text, 1, 80)                             AS sql_text,
    sql.buffer_gets,
    sql.executions,
    ROUND(sql.buffer_gets / NULLIF(sql.executions, 0))      AS gets_per_exec,
    sql.disk_reads,
    sql.elapsed_time / 1000000                              AS elapsed_sec,
    sql.hash_value
  FROM stats$sql_summary sql
  WHERE sql.snap_id = &end_snap_id
    AND sql.dbid    = (SELECT dbid FROM v$database)
    AND sql.instance_number = (SELECT instance_number FROM v$instance)
  ORDER BY sql.buffer_gets DESC
)
WHERE ROWNUM <= 20;

System Statistics (STATS$SYSSTAT)

-- Delta statistics between two snapshots
SELECT
  e.name                              AS statistic,
  e.value - NVL(b.value, 0)          AS delta_value,
  ROUND((e.value - NVL(b.value, 0)) /
        NULLIF(
          (SELECT ROUND((s2.snap_time - s1.snap_time) * 86400)
           FROM stats$snapshot s1, stats$snapshot s2
           WHERE s1.snap_id = &begin_snap_id
             AND s2.snap_id = &end_snap_id), 0), 2) AS per_second
FROM stats$sysstat e
LEFT JOIN stats$sysstat b
  ON b.statistic# = e.statistic#
 AND b.snap_id    = &begin_snap_id
 AND b.dbid       = e.dbid
 AND b.instance_number = e.instance_number
WHERE e.snap_id         = &end_snap_id
  AND e.dbid            = (SELECT dbid FROM v$database)
  AND e.instance_number = (SELECT instance_number FROM v$instance)
  AND e.name IN (
    'execute count',
    'parse count (hard)',
    'parse count (total)',
    'physical reads',
    'physical writes',
    'redo size',
    'sorts (disk)',
    'sorts (memory)',
    'table fetch by rowid',
    'table scans (long tables)',
    'user commits',
    'user rollbacks'
  )
ORDER BY delta_value DESC;

Wait Events (STATS$SYSTEM_EVENT)

-- Top wait events between two snapshots
SELECT *
FROM (
  SELECT
    e.event,
    e.total_waits    - NVL(b.total_waits, 0)     AS waits,
    e.total_timeouts - NVL(b.total_timeouts, 0)   AS timeouts,
    ROUND((e.time_waited_micro - NVL(b.time_waited_micro, 0)) / 1000000, 2) AS secs_waited,
    ROUND((e.time_waited_micro - NVL(b.time_waited_micro, 0)) /
          NULLIF((e.total_waits - NVL(b.total_waits, 0)), 0) / 1000, 2)     AS avg_wait_ms
  FROM stats$system_event e
  LEFT JOIN stats$system_event b
    ON b.event              = e.event
   AND b.snap_id            = &begin_snap_id
   AND b.dbid               = e.dbid
   AND b.instance_number    = e.instance_number
  WHERE e.snap_id           = &end_snap_id
    AND e.dbid              = (SELECT dbid FROM v$database)
    AND e.instance_number   = (SELECT instance_number FROM v$instance)
    AND e.event NOT IN ('SQL*Net message from client',
                        'SQL*Net message to client',
                        'SQL*Net more data to client')
    AND e.total_waits - NVL(b.total_waits, 0) > 0
  ORDER BY secs_waited DESC
)
WHERE ROWNUM <= 15;

Shared Pool and SGA Memory (STATS$SGASTAT)

-- SGA pool sizes at a given snapshot
SELECT
  pool,
  name,
  ROUND(bytes / 1024 / 1024, 2) AS size_mb
FROM stats$sgastat
WHERE snap_id = &end_snap_id
  AND dbid    = (SELECT dbid FROM v$database)
  AND instance_number = (SELECT instance_number FROM v$instance)
  AND pool IS NOT NULL
ORDER BY pool, bytes DESC;

-- Free memory trend across snapshots
SELECT
  sn.snap_id,
  TO_CHAR(sn.snap_time, 'DD-MON HH24:MI')       AS snap_time,
  ROUND(sg.bytes / 1024 / 1024, 2)               AS free_shared_pool_mb
FROM stats$sgastat sg
JOIN stats$snapshot sn
  ON sg.snap_id         = sn.snap_id
 AND sg.dbid            = sn.dbid
 AND sg.instance_number = sn.instance_number
WHERE sg.name       = 'free memory'
  AND sg.pool       = 'shared pool'
  AND sn.snap_time  > SYSDATE - 7
  AND sn.dbid       = (SELECT dbid FROM v$database)
ORDER BY sn.snap_id;

---

Snapshot Management

Purge Old Snapshots

-- Delete snapshots older than 30 days
EXECUTE STATSPACK.PURGE(i_extended_purge => TRUE);

-- Or use sppurge.sql interactively (prompts for range)
@$ORACLE_HOME/rdbms/admin/sppurge.sql

-- Manual purge of a specific range
DELETE FROM stats$snapshot
WHERE snap_id BETWEEN &low_snap AND &high_snap
  AND dbid = (SELECT dbid FROM v$database);
COMMIT;

Monitor PERFSTAT Tablespace Usage

-- PERFSTAT tablespace usage
SELECT
  df.tablespace_name,
  ROUND(df.bytes / 1024 / 1024)          AS total_mb,
  ROUND(NVL(fs.bytes, 0) / 1024 / 1024) AS free_mb,
  ROUND((df.bytes - NVL(fs.bytes, 0)) / df.bytes * 100, 1) AS pct_used
FROM (
  SELECT tablespace_name, SUM(bytes) AS bytes
  FROM dba_data_files
  WHERE tablespace_name = 'PERFSTAT'
  GROUP BY tablespace_name
) df
LEFT JOIN (
  SELECT tablespace_name, SUM(bytes) AS bytes
  FROM dba_free_space
  WHERE tablespace_name = 'PERFSTAT'
  GROUP BY tablespace_name
) fs
  ON df.tablespace_name = fs.tablespace_name;

-- Row counts per STATS$ table
SELECT
  table_name,
  num_rows,
  TO_CHAR(last_analyzed, 'DD-MON-YY') AS last_analyzed
FROM dba_tables
WHERE owner = 'PERFSTAT'
  AND table_name LIKE 'STATS$%'
ORDER BY num_rows DESC;

---

Uninstalling Statspack

-- Remove all PERFSTAT objects and the user (run as SYSDBA)
@$ORACLE_HOME/rdbms/admin/spdrop.sql

-- Or drop manually
DROP USER PERFSTAT CASCADE;
DROP TABLESPACE PERFSTAT INCLUDING CONTENTS AND DATAFILES;

---

Best Practices

1. Use level 6 or 7 for useful diagnostics - The default level 5 captures SQL text but not execution plans. Level 6 adds plans, level 7 adds segment stats.
2. Keep at least 7 days of snapshots - Trend analysis requires history. 14-30 days is recommended.
3. Set SQL thresholds appropriately - Default thresholds capture too much on busy databases, and too little on quiet ones. Tune based on your workload.
4. Schedule hourly snapshots - One snapshot per hour provides sufficient resolution without filling the PERFSTAT tablespace quickly.
5. Monitor the PERFSTAT tablespace - A full tablespace causes snapshot failures. Autoextend with a cap is the safest approach.
6. Purge regularly - Run STATSPACK.PURGE as a scheduled job to keep the repository manageable.
7. On Standard Edition - Statspack is your primary licensed performance repository. Treat it as a production component, not an optional add-on.

Related Topics

• Oracle AWR Report Generation - Enterprise edition equivalent with additional features
• Oracle DBMS_SCHEDULER Guide - Automate snapshot scheduling
• Performance Analysis Scripts - Complementary V$ view queries