spreport.sql: Generate Oracle Statspack Performance Reports

Oracle Database Statspack spreport.sql Script: Complete Performance Tuning Guide

Statspack is the free, lightweight cousin of AWR, and @?/rdbms/admin/spreport.sql is the script that turns its stored snapshots into a readable report. It calculates instance activity between two snapshots, which helps you find bottlenecks and tuning targets.

Overview and Purpose

Oracle Statspack is a performance diagnosis tool that uses SQL, PL/SQL, and SQL*Plus scripts to collect, automate, store, and view performance data. The spreport.sql script specifically generates detailed reports by comparing performance statistics between two snapshot points, similar to the older UTLBSTAT/UTLESTAT scripts. Oracle's documentation says Statspack differs from those scripts: it collects more data, including high-resource SQL, and it stores results in permanent tables owned by PERFSTAT, so you can compare historical data.

Use the report to establish performance baselines, find performance issues, and base tuning decisions on measured data.

Code Breakdown: Generate a Statspack Report

Query

1@?/rdbms/admin/spreport.sql

The command consists of several key components:

  • @ - the SQL*Plus command that runs a script file
  • ? - SQL*Plus replaces the question mark with the value of the ORACLE_HOME environment variable
  • /rdbms/admin/ - Standard Oracle directory containing database administration scripts
  • spreport.sql - The main Statspack reporting script file

Script Execution Process

The spreport.sql script follows a structured execution process when run from the PERFSTAT user account:

  1. Database Connection: Connect to the PERFSTAT user account that owns the Statspack repository
  2. Snapshot Selection: Display available snapshots with their IDs and timestamps
  3. Parameter Input: Prompt for beginning snapshot ID, ending snapshot ID, and report name
  4. Report Generation: Process statistics between the selected snapshots and generate the report

Key Components and Features

Snapshot Management

The script requires at least two snapshots to generate meaningful reports. The instance must not have been shut down between the begin and end snapshots. Snapshots capture point-in-time performance metrics and can be taken manually using EXEC STATSPACK.snap or automated through job scheduling. The flexibility of snapshot-based reporting allows analysis of any time period between captured data points.

Report Content Areas

Statspack reports generated by spreport.sql cover these areas:

  • Instance Statistics: Overall database performance metrics and ratios
  • Wait Events Analysis: Identification of performance bottlenecks and resource contention
  • SQL Performance: Resource-intensive SQL statements and execution statistics
  • Memory Analysis: Buffer cache efficiency and memory utilization patterns
  • I/O Statistics: Disk read/write patterns and storage performance metrics

Interactive Execution

The script provides an interactive interface that prompts you for three values:

 1SQL> connect perfstat/perfstat
 2SQL> @?/rdbms/admin/spreport
 3
 4Specify the Begin and End Snapshot Ids
 5~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
 6Enter value for begin_snap: 1
 7Begin Snapshot Id specified: 1
 8
 9Enter value for end_snap: 2
10End Snapshot Id specified: 2
11
12Specify the Report Name
13~~~~~~~~~~~~~~~~~~~~~~~
14The default report file name is sp_1_2
15Enter value for report_name: <press return or enter a new name>

Performance Analysis Insights

Baseline Establishment

The most effective approach to database tuning with Statspack involves establishing performance baselines during normal operation periods. These baselines serve as comparison points when performance issues arise, enabling administrators to identify deviations from normal behavior patterns.

Proactive Monitoring Strategy

Database administrators should implement proactive snapshot collection strategies, typically capturing data hourly during peak periods. This approach ensures adequate historical data for trend analysis and performance troubleshooting.

Memory Optimization Analysis

Statspack reports include memory statistics that help you judge whether memory allocation fits the workload.

Best Practices and Implementation

Automated Collection Setup

For production environments, automate snapshot collection using Oracle's job scheduling capabilities. The spauto.sql script enables hourly snapshot automation, while sppurge.sql manages historical data retention.

Report Analysis Workflow

Work through a Statspack report in this order:

  1. Initial Assessment: Review instance-wide statistics and performance ratios
  2. Wait Event Analysis: Identify top wait events and resource bottlenecks
  3. SQL Analysis: Use sprepsql.sql for detailed analysis of specific SQL statements
  4. Trend Comparison: Compare current performance against established baselines

Configuration Considerations

Set timed_statistics parameter to true for accurate timing data collection. Consider adjusting Statspack collection levels using STATSPACK.MODIFY_STATSPACK_PARAMETER to capture appropriate detail levels for analysis requirements.

Multi-Instance Environments

In Oracle Real Application Clusters environments, connect to specific instances for targeted performance analysis. This approach enables identification of instance-specific performance characteristics and load distribution patterns.

Advanced Usage Scenarios

Performance Troubleshooting

When performance issues arise, administrators can select appropriate snapshot ranges covering the problematic time periods. The flexibility of post-collection analysis allows for focused investigation of specific performance incidents.

Capacity Planning Support

Regular Statspack snapshots give you historical performance trends and resource utilization patterns. Compare reports from different periods to see how workload changes over time.

References

Posts in this series