How to List Oracle Statspack Snapshots with SQL Query (stats$snapshot)

calendar Sep 4, 2025 / · 3 min read · oracle statspack performance monitoring dba sql snapshots administration v$database  ·
Share on: linkedin copy

How to List Oracle Statspack Snapshots with SQL Query

This Oracle Database query provides a simple way to view all Statspack snapshots stored in your database. Statspack is Oracle's built-in performance monitoring tool that captures database statistics at regular intervals, helping database administrators analyze system performance over time.

Purpose of the Code

The primary purpose of this SQL script is to retrieve and display a comprehensive list of all Statspack snapshots with their essential details. This information helps database administrators track when performance snapshots were taken and plan their performance analysis activities accordingly.

Code Breakdown

Query: List snapshots with formatted date and time information from Oracle Statspack repository.

1col "Date/Time" format a30
2select snap_id
3,       snap_level
4,to_char(snap_time,'HH24:MI:SS DD-MM-YYYY') "Date/Time"
5from stats$snapshot
6,v$database
7order by snap_id
8/

Line-by-Line Explanation

Line 1: col "Date/Time" format a30

  • Sets the column width for the "Date/Time" output to 30 characters
  • Ensures proper formatting and alignment in the query results

Lines 2-4: The SELECT statement

  • snap_id: Returns the unique snapshot identifier number
  • snap_level: Shows the level of detail captured in each snapshot
  • to_char(snap_time,'HH24:MI:SS DD-MM-YYYY'): Converts the snapshot timestamp to a readable format showing hours, minutes, seconds, day, month, and year

Line 5: from stats$snapshot, v$database

  • Queries the main Statspack repository table that stores snapshot metadata
  • Includes v$database view for additional database context

Line 6: order by snap_id

  • Sorts results by snapshot ID in ascending order, showing oldest snapshots first

Key Points and Insights

Database Performance Monitoring

This query serves as a foundation for Statspack analysis by showing when performance data was collected. Regular snapshots are essential for identifying performance trends and troubleshooting database issues.

Snapshot Management

Understanding your snapshot history helps with:

  • Planning performance analysis timeframes
  • Identifying gaps in monitoring coverage
  • Managing repository space and retention policies
  • Correlating performance issues with specific time periods

Administrative Benefits

Database administrators use this information to:

  • Verify that automatic snapshot collection is working properly
  • Select appropriate baseline periods for performance comparisons
  • Plan maintenance windows based on historical performance data
  • Generate performance reports for specific date ranges

Practical Applications

Performance Troubleshooting

When investigating performance issues, this query helps identify which snapshots cover the problem period. You can then use these snapshot IDs in more detailed Statspack reports.

Capacity Planning

Historical snapshot data provides the foundation for understanding database growth patterns and planning future capacity requirements.

Compliance and Reporting

Many organizations require regular performance monitoring for compliance purposes. This query helps verify that monitoring requirements are being met consistently.

Best Practices

Regular Monitoring

Check your snapshot collection regularly to ensure continuous performance monitoring coverage without gaps that could hide important performance events.

Retention Management

Monitor the number of snapshots to prevent excessive growth of the Statspack repository, which can impact database performance and storage usage.

Documentation

Keep records of significant snapshots that correspond to known performance events or system changes for future reference and analysis.

References

Posts in this series