Display a List of Snapshots in Oracle AWR with dba_hist_snapshot

calendar Sep 22, 2025 / · 6 min read · Oracle Database AWR Performance Tuning SQL Scripts Database Monitoring DBA Tasks Snapshot Management Performance Analysis dba_hist_snapshot  ·
Share on: linkedin copy

Understanding Oracle AWR Snapshot Display

The Oracle Automatic Workload Repository (AWR) is a performance diagnostic tool that collects, processes, and stores database statistics automatically. Displaying a list of available snapshots is essential for database administrators who need to generate AWR reports, analyze performance trends, and troubleshoot database issues. This script provides a quick and efficient way to view all available snapshots in the system with their snapshot IDs, levels, and start times.

Breakdown of the Code

Query Used:

1set lines 100 pages 999
2select snap_id
3,snap_level
4,to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') begin
5from dba_hist_snapshot 
6order by 1
7/

Command Explanation

SQL*Plus Formatting Commands: The script begins with formatting commands that control the output display. The set lines 100 command sets the line width to 100 characters, making the output more readable on standard terminal screens. The pages 999 command sets the page size to 999 lines, which prevents page breaks and headers from appearing repeatedly in long result sets.

SELECT Statement Components: The query retrieves three key columns from the DBA_HIST_SNAPSHOT view. The snap_id column contains a unique identifier for each snapshot, which is used to reference specific snapshots when generating AWR reports. The snap_level column indicates the level of statistics captured in the snapshot, with level 1 being the standard default level. The to_char(begin_interval_time, 'dd/mm/yy hh24:mi:ss') function converts the snapshot's beginning timestamp into a readable format showing day, month, year, and 24-hour time.

Data Source: The dba_hist_snapshot view is the primary repository for snapshot metadata in Oracle Database. This view stores information about all snapshots captured by AWR, including snapshot IDs, start and end times, database restart information, and snapshot levels. By default, Oracle generates snapshots every hour and retains them for eight days, though these settings can be modified.

Ordering Results: The order by 1 clause sorts the results by the first column (snap_id) in ascending order. This ensures that snapshots are displayed chronologically from oldest to newest, making it easier to identify specific time periods.

Key Points

Snapshot Identification: Each snapshot has a unique SNAP_ID that serves as the primary identifier for all AWR operations. When generating AWR reports, administrators must specify a beginning and ending snapshot ID to define the analysis period. The snapshot ID appears in all DBA_HIST_* tables and can be joined with the DBA_HIST_SNAPSHOT view to retrieve time interval information.

Snapshot Levels: The snap_level column typically shows a value of 1, which represents the standard snapshot level. Different snapshot levels can capture varying amounts of statistical information, though level 1 is sufficient for most performance analysis tasks.

Time Interval Management: Understanding the begin_interval_time is crucial for selecting the correct snapshots for analysis. Each snapshot captures statistics from its begin_interval_time to its end_interval_time, typically spanning one hour by default. When generating AWR reports, the ending snapshot ID's begin_interval_time marks the start of the analysis period.

Default Configuration: Oracle Database automatically creates snapshots every 60 minutes and maintains them for 8 days (192 hours). These default settings balance the need for historical data with storage space requirements. Administrators can modify these settings using the DBMS_WORKLOAD_REPOSITORY package or through Oracle Enterprise Manager.

Insights and Practical Applications

Performance Troubleshooting: Database administrators use this script as the first step in performance investigation. By listing all available snapshots, they can identify the snapshot IDs that correspond to periods of poor performance. This information is essential for generating targeted AWR reports that compare baseline performance with problematic periods.

Report Generation Workflow: Before generating an AWR report, administrators must determine which snapshots bracket the time period of interest. This script provides the snapshot IDs and timestamps needed to make informed selections. For example, if a performance issue occurred between 10:00 AM and 2:00 PM, the administrator would identify the snapshot IDs that encompass this timeframe.

Capacity Planning: Regular review of snapshot availability helps administrators understand their historical data retention. If the oldest snapshot is only three days old instead of the default eight days, this might indicate storage issues or configuration problems. Administrators can use this information to adjust retention policies for long-term trend analysis.

Database Restart Detection: When reviewing snapshots, administrators can identify database restarts by looking for gaps in the snapshot sequence or changes in the startup time. This information is critical because AWR reports cannot span database restarts, as the statistics are reset when the instance restarts.

Additional Considerations

Storage Requirements: The AWR repository consumes space in the SYSAUX tablespace. Longer retention periods and more frequent snapshots increase storage requirements proportionally. Organizations with storage constraints may need to reduce retention periods or snapshot frequency.

Baseline Creation: Snapshots can be preserved indefinitely by creating AWR baselines. Baselines are useful for preserving performance data from important time periods, such as year-end processing or peak business cycles. This allows administrators to compare current performance against historical baselines even after the normal retention period expires.

Manual Snapshot Creation: While Oracle creates snapshots automatically, administrators can also create manual snapshots using the DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT procedure. Manual snapshots are useful for capturing statistics before and after specific events, such as application deployments or configuration changes.

Common Use Cases

Daily Health Checks: Many database administrators run this script daily as part of routine health checks. Verifying that snapshots are being created at regular intervals ensures that the AWR system is functioning properly. Missing or irregular snapshots may indicate underlying issues with the database or the AWR system.

Performance Analysis Preparation: When performance issues are reported, this script provides the foundation for detailed analysis. By quickly identifying available snapshot ranges, administrators can proceed directly to generating relevant AWR reports without guessing snapshot IDs.

Documentation and Auditing: Organizations may require documentation of database performance over specific periods for compliance or auditing purposes. This script provides a simple way to document which snapshots are available for a given timeframe.

References

Posts in this series