Create AWR Snapshot (snap.sql)

Use at Your Own Risk

This script is for educational purposes only. Always test in development environments before production use. See our disclaimer for full terms.

What This Script Does

This script creates an immediate AWR (Automatic Workload Repository) snapshot using Oracle’s DBMS_WORKLOAD_REPOSITORY package. AWR snapshots capture point-in-time performance statistics that can be used for analysis, comparison, and troubleshooting. This simple utility is essential for capturing performance data during specific events, before/after changes, or when investigating performance issues that require immediate statistical capture.

The Script

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;

Key Features

• Immediate Snapshot Creation: Creates AWR snapshot instantly
• Performance Data Capture: Captures comprehensive database performance statistics
• Baseline Establishment: Enables before/after performance comparisons
• Troubleshooting Support: Provides data for performance issue investigation
• Simple Execution: Single command execution for ease of use
• Diagnostic Foundation: Creates foundation for AWR reporting and analysis

Usage

@snap.sql

No parameters required - creates snapshot immediately.

Required Privileges

EXECUTE on DBMS_WORKLOAD_REPOSITORY
-- Typically requires DBA role or explicit grant

Sample Output

PL/SQL procedure successfully completed.

Understanding AWR Snapshots

What AWR Captures

Performance Statistics

-- AWR snapshot includes:
-- SQL performance statistics
-- Wait event information
-- System-level metrics
-- Resource utilization data
-- Memory allocation statistics
-- I/O performance metrics
-- Time-based performance data

Snapshot Components

-- Snapshot data includes:
-- Database instance statistics
-- SQL statement statistics
-- Session and user activity
-- Object access statistics
-- System event information
-- Resource usage patterns

Snapshot Timing

Automatic vs. Manual Snapshots

-- Automatic snapshots:
-- Created based on INTERVAL setting (default 1 hour)
-- Managed by AWR automatic collection
-- Consistent baseline data collection
-- Long-term trend analysis support

-- Manual snapshots:
-- Created on-demand for specific analysis
-- Capture data during specific events
-- Support immediate troubleshooting needs
-- Enable precise before/after comparisons

Common Use Cases

1. Performance Troubleshooting

   • Capture data during performance issues
   • Create before/after snapshots for changes
   • Support incident investigation
   • Provide data for performance analysis

2. Change Management

   • Baseline performance before changes
   • Validate performance after changes
   • Support rollback decisions
   • Document change impact

3. Capacity Planning

   • Capture peak usage statistics
   • Document resource utilization patterns
   • Support infrastructure planning
   • Establish performance baselines

4. Performance Analysis

   • Create data points for AWR reports
   • Support SQL performance analysis
   • Enable detailed system analysis
   • Provide diagnostic information

Advanced Usage

Snapshot Management Strategy

Strategic Snapshot Creation

-- Create snapshots:
-- Before major system changes
-- During peak usage periods
-- When performance issues occur
-- Before and after maintenance windows
-- During capacity testing

Retention Considerations

-- Snapshot retention factors:
-- Default retention period (8 days)
-- Storage space requirements
-- Analysis timeframe needs
-- Compliance requirements

Integration with AWR Reporting

AWR Report Generation

-- Generate AWR reports using snapshots:
-- @$ORACLE_HOME/rdbms/admin/awrrpt.sql
-- Specify snapshot range for analysis
-- Compare multiple time periods
-- Analyze performance trends

Snapshot Identification

-- Find available snapshots:
SELECT snap_id, begin_interval_time, end_interval_time
FROM dba_hist_snapshot
WHERE trunc(begin_interval_time) = trunc(sysdate)
ORDER BY snap_id;

Best Practices

Snapshot Creation Strategy

Timing Considerations

-- Optimal timing for snapshots:
-- Capture representative workload periods
-- Avoid backup and maintenance windows
-- Include peak usage periods
-- Consider business cycle timing

Frequency Guidelines

-- Manual snapshot frequency:
-- Avoid excessive snapshot creation
-- Balance detail with storage consumption
-- Consider automatic collection adequacy
-- Focus on significant events

Performance Impact

Minimal Overhead

-- Snapshot creation impact:
-- Very low performance overhead
-- Quick execution time
-- Minimal resource consumption
-- Safe for production use

Resource Considerations

-- Resource usage:
-- Small amount of SGA memory
-- Minimal CPU utilization
-- Brief increase in I/O activity
-- Negligible user impact

Integration with Monitoring

Automated Monitoring

Scripted Snapshot Creation

-- Automated snapshot scripts:
-- Integration with monitoring tools
-- Event-driven snapshot creation
-- Scheduled performance captures
-- Alert-triggered data collection

Monitoring Tool Integration

-- Integration possibilities:
-- Enterprise Manager integration
-- Third-party monitoring tools
-- Custom monitoring scripts
-- Performance management systems

Alert-Driven Snapshots

Performance Alert Response

-- Create snapshots when:
-- Performance thresholds exceeded
-- Error conditions detected
-- Resource utilization spikes
-- User complaints received

Troubleshooting Applications

Snapshot Creation Issues

Common Problems

-- Potential issues:
-- Insufficient privileges
-- Disabled AWR collection
-- Storage space limitations
-- Database state issues

Resolution Steps

-- Troubleshooting steps:
-- Verify DBA privileges
-- Check AWR configuration
-- Confirm adequate storage
-- Validate database status

AWR Configuration

AWR Settings Verification

-- Check AWR configuration:
SELECT snap_interval, retention
FROM dba_hist_wr_control;

-- Verify AWR is enabled:
SELECT value FROM v$parameter
WHERE name = 'statistics_level';
-- Should be 'TYPICAL' or 'ALL'

Configuration Optimization

-- Optimize AWR settings:
-- Adjust snapshot interval if needed
-- Modify retention period appropriately
-- Configure baseline capture
-- Set appropriate collection level

Security Considerations

Access Control

Privilege Management

-- Limit access to authorized personnel:
-- DBA role typically required
-- Monitor snapshot creation activity
-- Audit AWR usage when necessary
-- Implement proper access controls

Data Sensitivity

-- AWR data considerations:
-- Contains performance-sensitive information
-- May include SQL text and bind variables
-- Requires appropriate data protection
-- Consider privacy implications

Storage and Maintenance

Storage Requirements

Space Planning

-- Storage considerations:
-- AWR repository space requirements
-- Snapshot size varies by activity
-- Plan for retention period storage
-- Monitor SYSAUX tablespace usage

Maintenance Tasks

-- Regular maintenance:
-- Monitor AWR repository size
-- Purge old snapshots if needed
-- Maintain adequate free space
-- Review retention settings periodically

Performance Repository Management

Repository Health

-- Monitor repository health:
-- Check SYSAUX tablespace space
-- Verify AWR collection status
-- Monitor snapshot creation success
-- Review error logs for issues

Related Scripts

• awrrpt.sql - Generate AWR reports
• addmrpt.sql - Generate ADDM reports
• ashrpt.sql - Generate ASH reports
• wr_find_last_snap.sql - Find recent snapshots

Enhanced Snapshot Management

Advanced Snapshot Operations

Baseline Creation

-- Create performance baseline:
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
  start_snap_id => 100,
  end_snap_id   => 120,
  baseline_name => 'PEAK_USAGE_BASELINE',
  dbid          => (SELECT dbid FROM v$database)
);

Snapshot Comparison

-- Compare snapshot periods:
-- Use AWR Compare Period Report
-- Analyze performance differences
-- Identify performance regressions
-- Support change impact analysis

Automation Examples

Event-Driven Snapshots

-- Create snapshot during high CPU:
IF (SELECT value FROM v$sysmetric
    WHERE metric_name = 'Host CPU Utilization (%)') > 80 THEN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT;
END IF;

Summary

This script is essential for:

1. Performance Troubleshooting - Immediate capture of performance data during issues
2. Change Management - Creating before/after snapshots to validate change impact
3. Capacity Planning - Capturing performance data during peak usage periods
4. Diagnostic Support - Providing foundation data for AWR reports and analysis
5. Performance Baseline - Establishing reference points for performance comparison