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 theORACLE_HOMEenvironment variable/rdbms/admin/- Standard Oracle directory containing database administration scriptsspreport.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:
- Database Connection: Connect to the PERFSTAT user account that owns the Statspack repository
- Snapshot Selection: Display available snapshots with their IDs and timestamps
- Parameter Input: Prompt for beginning snapshot ID, ending snapshot ID, and report name
- 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:
- Initial Assessment: Review instance-wide statistics and performance ratios
- Wait Event Analysis: Identify top wait events and resource bottlenecks
- SQL Analysis: Use
sprepsql.sqlfor detailed analysis of specific SQL statements - 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
- Oracle Statspack Documentation - Oracle9i Database Tuning Guide Statspack chapter, reproduced in a WebLogic Integration appendix, with spreport usage examples
- Oracle Database Using Statspack Guide - Official Oracle documentation for Statspack implementation and reporting procedures
- Oracle Performance Tuning with Statspack - Oracle technical whitepaper on performance tuning methodologies using Statspack
- Statspack Installation and Configuration Guide - Detailed guide for Oracle Statspack installation, configuration, and report generation
- Amazon RDS Oracle Statspack Implementation - Amazon Web Services documentation for Oracle Statspack implementation in cloud environments
- Running Oracle Statspack Performance Reports - Practical guide for executing and interpreting Oracle Statspack performance reports
- Oracle AWR and Statspack Report Analysis - Professional analysis techniques for Oracle performance reports and system assessment
Posts in this series
- Oracle Statspack Snapshot Levels: statspack.snap Guide
- Oracle Statspack sppurge: Snapshot Purge Script Guide
- spreport.sql: Generate Oracle Statspack Performance Reports
- Oracle Statspack: List Snapshots with stats$snapshot
- Oracle Statspack Installation Guide: spcreate Setup
- Oracle: Uninstall Statspack Using the spdrop Script
- Oracle Statspack: Automate Snapshots with spauto.sql