How to Manually Take an AWR Snapshot in Oracle Database with dbms_workload_repository.create_snapshot

calendar Sep 27, 2025 / · 5 min read · Oracle Database AWR Performance Tuning Database Administration PL/SQL Monitoring Troubleshooting Database Optimization dbms_workload_repository  ·
Share on: linkedin copy

How to Manually Take an AWR Snapshot in Oracle Database with dbms_workload_repository.create_snapshot

What is Oracle AWR Snapshot Creation?

Creating a manual AWR snapshot in Oracle Database allows database administrators to capture performance statistics at specific points in time, providing precise data for troubleshooting and performance analysis. The DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT procedure creates an on-demand snapshot outside the automatic hourly collection schedule.

Purpose of Manual AWR Snapshots

Manual AWR snapshot creation serves critical purposes for Oracle database administrators and performance engineers. The primary purpose is to capture database performance metrics during specific time windows that require detailed analysis. Database administrators use manual snapshots when investigating performance issues that occur at irregular intervals or when testing changes before and after implementation. This allows for precise comparison of database behavior during shorter intervals than the default one-hour automatic snapshot period.

Breakdown of the Code

Query:

1exec dbms_workload_repository.create_snapshot

The code consists of a single PL/SQL execution statement that invokes the Oracle built-in procedure for manual snapshot creation. The exec command is a SQL*Plus shorthand for EXECUTE, which runs the PL/SQL procedure immediately. The procedure dbms_workload_repository.create_snapshot belongs to the DBMS_WORKLOAD_REPOSITORY package, which manages all Automatic Workload Repository operations in Oracle Database.

Command Components

The command structure uses the default parameter values when no arguments are specified. The CREATE_SNAPSHOT procedure accepts three optional parameters: flush_level, dbid, and source_name. When executed without parameters, the procedure uses the default flush_level value of 'TYPICAL' for non-container databases and CDB root, or 'LITE' for pluggable databases. The procedure returns a snapshot ID number when called as a function, but when executed as shown, it simply creates the snapshot without returning a value.

Key Points

Manual AWR snapshots provide flexibility beyond the automatic hourly collection schedule. Database administrators can create snapshots immediately before and after critical operations like batch jobs, system changes, or performance tests. The snapshots capture comprehensive performance data including wait events, time model statistics, Active Session History data, system statistics, object usage, and resource-intensive SQL statements. Each snapshot receives a unique identifier that can be queried from the DBA_HIST_SNAPSHOT view after creation.

Flush Levels Explained

Oracle Database supports four flush levels for snapshot creation: BESTFIT, LITE, TYPICAL, and ALL. The BESTFIT level automatically selects the appropriate collection level based on the database type. LITE mode collects only the most important statistics and is the default for pluggable databases and application containers. TYPICAL mode captures most statistics and serves as the default for container database root and non-container databases. ALL mode collects every possible statistic but consumes considerable disk space and takes longer to create.

Use Cases and Best Practices

Manual snapshots are particularly valuable when analyzing performance during non-standard time windows. Database administrators should create manual snapshots when troubleshooting specific issues that occur outside the regular hourly intervals. Creating snapshots at the start and end of critical operations enables precise performance analysis of specific database activities. For detailed troubleshooting, administrators can create snapshots at 15-minute intervals to capture more granular performance data.

Verification After Creation

After executing the manual snapshot command, administrators should verify successful creation by querying the DBA_HIST_SNAPSHOT view. The query displays snapshot ID, begin interval time, and end interval time for all captured snapshots. Verification ensures the snapshot was created successfully and provides the snapshot ID needed for generating AWR reports. Database administrators can then use the begin and end snapshot IDs to generate detailed performance reports for the specific time period.

Integration with Performance Analysis

Manual snapshots work together with other Oracle performance tools like ADDM and ASH reports. After creating snapshots, the Automatic Database Diagnostic Monitor processes the snapshot data to identify performance bottlenecks and recommend solutions. Database administrators can generate AWR reports using the snapshot IDs to compare performance across different time periods. The combination of manual snapshots and AWR reports provides comprehensive insight into database performance patterns and issues.

Retention and Storage Considerations

AWR snapshots are retained for eight days by default, but administrators can modify this retention period to meet organizational requirements. Oracle recommends adjusting the retention period to at least one month for better historical analysis. For critical time periods, administrators can create AWR baselines that preserve snapshots indefinitely beyond the normal retention period. Longer retention periods consume more disk space in the system tablespace where AWR data is stored.

Syntax Variations

The CREATE_SNAPSHOT procedure can be called with explicit parameters for advanced scenarios. Administrators can specify the flush level explicitly using the syntax EXECUTE DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL') to collect all available statistics. When working with pluggable databases or remote databases, the dbid and source_name parameters allow snapshot creation for specific database instances. The function version returns the snapshot ID directly for use in subsequent operations: DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT RETURN NUMBER.

Common Scenarios

Database administrators commonly create manual snapshots before major system changes to establish a performance baseline. During performance testing, creating snapshots at test boundaries enables accurate measurement of workload impact. When investigating intermittent performance problems, administrators create snapshots when issues occur to capture relevant statistics for later analysis. For batch job monitoring, snapshots taken at job start and completion provide detailed performance metrics for optimization.

References

Posts in this series