Oracle AWR (Automatic Workload Repository) - Complete Performance Guide

Oracle AWR (Automatic Workload Repository) - Complete Performance Guide

Oracle Automatic Workload Repository (AWR) is Oracle’s comprehensive performance monitoring and analysis framework, available from Oracle 10g onwards. AWR automatically collects, processes, and maintains performance statistics for database problem detection and self-tuning purposes.

🎯 Overview

What is Oracle AWR?

Oracle AWR provides:

• Automatic collection of database performance statistics
• Historical performance data storage and management
• Comprehensive reports for performance analysis
• Baseline management for comparison and trending
• Performance trending and anomaly detection
• Self-tuning recommendations through ADDM integration

AWR Architecture Components

Core Components

-- Check AWR configuration
SELECT * FROM dba_hist_wr_control;

-- View AWR snapshots
SELECT snap_id, begin_interval_time, end_interval_time,
       startup_time, instance_number
FROM dba_hist_snapshot
ORDER BY snap_id DESC
FETCH FIRST 10 ROWS ONLY;

-- AWR retention settings
SELECT
  extract(day from retention) as retention_days,
  extract(day from topnsql) as topnsql_days,
  snap_interval_num as snap_interval_minutes
FROM dba_hist_wr_control;

⚙️ AWR Configuration

Basic AWR Setup

Configuring Snapshot Collection

-- Modify AWR snapshot interval (as SYSDBA)
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    retention => 43200,    -- 30 days (in minutes)
    interval  => 60        -- 1 hour snapshots (in minutes)
  );
END;
/

-- Check current settings
SELECT
  snap_interval,
  retention,
  topnsql
FROM dba_hist_wr_control;

-- Manual snapshot creation
EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

-- Get latest snapshot ID
SELECT MAX(snap_id) FROM dba_hist_snapshot;

Statistics Level Configuration

-- Check current statistics level
SHOW PARAMETER statistics_level;

-- Set statistics level for AWR (requires TYPICAL or ALL)
ALTER SYSTEM SET statistics_level = 'TYPICAL';

-- Verify AWR is enabled
SELECT
  status,
  snap_interval,
  retention
FROM dba_hist_wr_control;

Advanced AWR Configuration

Baseline Management

-- Create fixed baseline
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => 1000,
    end_snap_id   => 1050,
    baseline_name => 'MONTH_END_PROCESSING_BASELINE',
    dbid          => (SELECT dbid FROM v$database),
    expiration    => 365  -- Days to keep baseline
  );
END;
/

-- Create repeating baseline template
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    start_time      => '08:00:00 MONDAY',
    end_time        => '18:00:00 FRIDAY',
    baseline_name   => 'BUSINESS_HOURS_TEMPLATE',
    template_name   => 'BUSINESS_HOURS_TEMPLATE',
    expiration      => 90
  );
END;
/

-- View existing baselines
SELECT baseline_name, baseline_type, start_snap_id, end_snap_id,
       creation_time, expiration
FROM dba_hist_baseline
ORDER BY creation_time DESC;

Snapshot Management

-- Drop specific snapshots
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
    low_snap_id  => 950,
    high_snap_id => 1000,
    dbid         => (SELECT dbid FROM v$database)
  );
END;
/

-- Create snapshot with custom flush level
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT(
    flush_level => 'TYPICAL'
  );
END;
/

📊 AWR Reports

Standard AWR Reports

HTML AWR Report Generation

-- Generate AWR report between snapshots
@$ORACLE_HOME/rdbms/admin/awrrpt.sql

-- Or using SQL
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid      => 1000,
    l_eid      => 1010
  )
);

-- Text format AWR report
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_TEXT(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid      => 1000,
    l_eid      => 1010
  )
);

AWR Global (RAC) Reports

-- Generate global AWR report for RAC
@$ORACLE_HOME/rdbms/admin/awrgrpt.sql

-- Using SQL for global report
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_GLOBAL_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => '1,2,3,4',  -- All RAC instances
    l_bid      => 1000,
    l_eid      => 1010
  )
);

Specialized AWR Reports

AWR SQL Report

-- Generate report for specific SQL statement
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_SQL_REPORT_HTML(
    l_dbid     => (SELECT dbid FROM v$database),
    l_inst_num => (SELECT instance_number FROM v$instance),
    l_bid      => 1000,
    l_eid      => 1010,
    l_sqlid    => '7x2kkv8407k1d'
  )
);

-- Find top SQL IDs for report generation
SELECT sql_id, executions_delta, elapsed_time_delta
FROM dba_hist_sqlstat
WHERE snap_id BETWEEN 1000 AND 1010
ORDER BY elapsed_time_delta DESC
FETCH FIRST 10 ROWS ONLY;

AWR Diff Reports

-- Compare two time periods
SELECT output FROM TABLE(
  DBMS_WORKLOAD_REPOSITORY.AWR_DIFF_REPORT_HTML(
    l_dbid1    => (SELECT dbid FROM v$database),
    l_inst_num1=> (SELECT instance_number FROM v$instance),
    l_bid1     => 1000,
    l_eid1     => 1010,
    l_dbid2    => (SELECT dbid FROM v$database),
    l_inst_num2=> (SELECT instance_number FROM v$instance),
    l_bid2     => 1100,
    l_eid2     => 1110
  )
);

📈 Performance Analysis with AWR

Key Performance Metrics

Database Load Analysis

-- Top wait events from AWR
SELECT
  event_name,
  total_waits,
  total_timeouts,
  time_waited_micro / 1000000 as time_waited_sec,
  average_wait_time_micro / 1000 as avg_wait_ms
FROM dba_hist_system_event
WHERE snap_id BETWEEN 1000 AND 1010
  AND event_name NOT LIKE 'SQL*Net%'
  AND event_name NOT LIKE '%idle%'
ORDER BY time_waited_micro DESC
FETCH FIRST 15 ROWS ONLY;

-- Time model statistics
SELECT
  stat_name,
  value / 1000000 as time_seconds,
  ROUND(value / SUM(value) OVER() * 100, 2) as pct_total
FROM dba_hist_sys_time_model s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
  AND s.snap_id = 1010  -- End snapshot
  AND stat_name IN ('DB time', 'DB CPU', 'sql execute elapsed time',
                    'parse time elapsed', 'hard parse elapsed time')
ORDER BY value DESC;

SQL Performance Analysis

-- Top SQL by elapsed time
SELECT
  sql_id,
  plan_hash_value,
  executions_delta as executions,
  elapsed_time_delta / 1000000 as elapsed_sec,
  cpu_time_delta / 1000000 as cpu_sec,
  iowait_delta / 1000000 as io_wait_sec,
  buffer_gets_delta as buffer_gets,
  disk_reads_delta as disk_reads,
  rows_processed_delta as rows_processed
FROM dba_hist_sqlstat
WHERE snap_id = 1010  -- End snapshot
  AND executions_delta > 0
ORDER BY elapsed_time_delta DESC
FETCH FIRST 20 ROWS ONLY;

-- SQL execution plan changes
SELECT
  s.sql_id,
  s.plan_hash_value,
  COUNT(*) as executions,
  AVG(s.elapsed_time_delta / s.executions_delta) as avg_elapsed_per_exec,
  MIN(sn.begin_interval_time) as first_seen,
  MAX(sn.end_interval_time) as last_seen
FROM dba_hist_sqlstat s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
  AND s.sql_id = '7x2kkv8407k1d'
  AND s.executions_delta > 0
GROUP BY s.sql_id, s.plan_hash_value
ORDER BY first_seen;

System Resource Analysis

-- Memory usage trends
SELECT
  snap_id,
  begin_interval_time,
  pool,
  name,
  bytes / 1024 / 1024 as size_mb
FROM dba_hist_sgastat s,
     dba_hist_snapshot sn
WHERE s.snap_id = sn.snap_id
  AND s.snap_id BETWEEN 1000 AND 1010
  AND pool IS NOT NULL
  AND name IN ('free memory', 'db_block_buffers', 'shared pool')
ORDER BY snap_id, pool, name;

-- I/O statistics by file
SELECT
  filename,
  SUM(phyrds) as physical_reads,
  SUM(phywrts) as physical_writes,
  SUM(phyblkrd) as blocks_read,
  SUM(phyblkwrt) as blocks_written,
  SUM(readtim) as read_time_cs,
  SUM(writetim) as write_time_cs
FROM dba_hist_filestatxs f,
     dba_hist_snapshot sn
WHERE f.snap_id = sn.snap_id
  AND f.snap_id BETWEEN 1000 AND 1010
GROUP BY filename
ORDER BY physical_reads + physical_writes DESC;

Historical Trending

Load Profile Trending

-- Database load trends over time
SELECT
  TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24') as hour,
  AVG(db_time / elapsed_time) as avg_db_time_ratio,
  AVG(cpu_time / elapsed_time) as avg_cpu_ratio,
  AVG(user_calls / elapsed_time) as calls_per_sec,
  AVG(physical_reads / elapsed_time) as reads_per_sec,
  AVG(redo_size / elapsed_time) as redo_bytes_per_sec
FROM (
  SELECT
    sn.begin_interval_time,
    sn.end_interval_time,
    EXTRACT(EPOCH FROM (sn.end_interval_time - sn.begin_interval_time)) as elapsed_time,
    st.value as stat_value,
    st.stat_name,
    LAG(st.value) OVER (PARTITION BY st.stat_name ORDER BY sn.snap_id) as prev_value,
    st.value - LAG(st.value) OVER (PARTITION BY st.stat_name ORDER BY sn.snap_id) as delta_value
  FROM dba_hist_sysstat st,
       dba_hist_snapshot sn
  WHERE st.snap_id = sn.snap_id
    AND st.stat_name IN ('DB time', 'CPU used by this session', 'user calls',
                         'physical reads', 'redo size')
    AND sn.begin_interval_time > SYSDATE - 7
)
PIVOT (
  MAX(delta_value) FOR stat_name IN (
    'DB time' as db_time,
    'CPU used by this session' as cpu_time,
    'user calls' as user_calls,
    'physical reads' as physical_reads,
    'redo size' as redo_size
  )
)
WHERE db_time IS NOT NULL
GROUP BY TO_CHAR(begin_interval_time, 'YYYY-MM-DD HH24')
ORDER BY hour;

🎯 Baseline Management

Creating and Managing Baselines

Performance Baselines

-- Create baseline for good performance period
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(
    start_snap_id => 1200,
    end_snap_id   => 1250,
    baseline_name => 'GOOD_PERFORMANCE_BASELINE',
    dbid          => (SELECT dbid FROM v$database)
  );
END;
/

-- Create adaptive baseline
BEGIN
  DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE_TEMPLATE(
    day_of_week     => 'MONDAY',
    hour_in_day     => 9,
    duration        => 8,  -- 8 hours
    expiration      => 30, -- 30 days
    baseline_name   => 'MONDAY_BUSINESS_HOURS',
    template_name   => 'WEEKDAY_TEMPLATE'
  );
END;
/

-- Compare current performance to baseline
SELECT
  metric_name,
  baseline_avg,
  current_value,
  ROUND((current_value - baseline_avg) / baseline_avg * 100, 2) as pct_change
FROM (
  -- Baseline metrics
  SELECT
    'DB CPU per second' as metric_name,
    AVG(value) as baseline_avg
  FROM dba_hist_sysmetric_summary s,
       dba_hist_baseline b
  WHERE s.snap_id BETWEEN b.start_snap_id AND b.end_snap_id
    AND b.baseline_name = 'GOOD_PERFORMANCE_BASELINE'
    AND s.metric_name = 'CPU Usage Per Sec'
) baseline
CROSS JOIN (
  -- Current metrics
  SELECT AVG(value) as current_value
  FROM dba_hist_sysmetric_summary
  WHERE snap_id BETWEEN 1300 AND 1310
    AND metric_name = 'CPU Usage Per Sec'
) current_perf;

Baseline Analysis

-- View all baselines and their metrics
SELECT
  b.baseline_name,
  b.baseline_type,
  COUNT(s.snap_id) as snapshots,
  MIN(sn.begin_interval_time) as start_time,
  MAX(sn.end_interval_time) as end_time,
  AVG(s.value) as avg_metric_value
FROM dba_hist_baseline b,
     dba_hist_sysmetric_summary s,
     dba_hist_snapshot sn
WHERE s.snap_id BETWEEN b.start_snap_id AND b.end_snap_id
  AND s.snap_id = sn.snap_id
  AND s.metric_name = 'CPU Usage Per Sec'
GROUP BY b.baseline_name, b.baseline_type
ORDER BY b.creation_time;

-- Drop baseline
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(
    baseline_name => 'OLD_BASELINE',
    cascade       => TRUE
  );
END;
/

🔍 Advanced AWR Analysis

Custom AWR Queries

Session-Level Analysis

-- Historical session activity
SELECT
  sn.begin_interval_time,
  ash.session_id,
  ash.user_id,
  ash.program,
  ash.module,
  COUNT(*) as sample_count,
  ash.event,
  ash.sql_id
FROM dba_hist_active_sess_history ash,
     dba_hist_snapshot sn
WHERE ash.snap_id = sn.snap_id
  AND sn.begin_interval_time > SYSDATE - 1
  AND ash.session_type = 'FOREGROUND'
GROUP BY sn.begin_interval_time, ash.session_id, ash.user_id,
         ash.program, ash.module, ash.event, ash.sql_id
HAVING COUNT(*) > 10
ORDER BY sample_count DESC;

-- Lock contention analysis
SELECT
  blocking_session,
  sql_id,
  event,
  p1text,
  p1,
  p2text,
  p2,
  COUNT(*) as wait_count
FROM dba_hist_active_sess_history
WHERE sample_time > SYSDATE - 1
  AND blocking_session IS NOT NULL
GROUP BY blocking_session, sql_id, event, p1text, p1, p2text, p2
ORDER BY wait_count DESC;

Object-Level I/O Analysis

-- Table/Index I/O patterns
SELECT
  o.owner,
  o.object_name,
  o.object_type,
  SUM(s.logical_reads_delta) as logical_reads,
  SUM(s.physical_reads_delta) as physical_reads,
  SUM(s.physical_writes_delta) as physical_writes,
  ROUND(SUM(s.physical_reads_delta) / NULLIF(SUM(s.logical_reads_delta), 0) * 100, 2) as cache_miss_pct
FROM dba_hist_seg_stat s,
     dba_objects o
WHERE s.obj# = o.object_id
  AND s.snap_id BETWEEN 1000 AND 1010
  AND o.owner NOT IN ('SYS', 'SYSTEM')
GROUP BY o.owner, o.object_name, o.object_type
ORDER BY logical_reads DESC
FETCH FIRST 20 ROWS ONLY;

-- Tablespace I/O trends
SELECT
  ts.tablespace_name,
  SUM(fs.phyrds) as physical_reads,
  SUM(fs.phywrts) as physical_writes,
  SUM(fs.readtim) / 100 as read_time_sec,
  SUM(fs.writetim) / 100 as write_time_sec,
  ROUND(SUM(fs.readtim) / NULLIF(SUM(fs.phyrds), 0) * 10, 2) as avg_read_time_ms
FROM dba_hist_filestatxs fs,
     dba_data_files df,
     dba_tablespaces ts
WHERE fs.file# = df.file_id
  AND df.tablespace_name = ts.tablespace_name
  AND fs.snap_id BETWEEN 1000 AND 1010
GROUP BY ts.tablespace_name
ORDER BY physical_reads DESC;

Performance Regression Analysis

-- SQL performance regression detection
WITH baseline_period AS (
  SELECT sql_id,
         AVG(elapsed_time_delta / executions_delta) as avg_elapsed
  FROM dba_hist_sqlstat
  WHERE snap_id BETWEEN 1000 AND 1050  -- Baseline period
    AND executions_delta > 0
  GROUP BY sql_id
),
current_period AS (
  SELECT sql_id,
         AVG(elapsed_time_delta / executions_delta) as avg_elapsed
  FROM dba_hist_sqlstat
  WHERE snap_id BETWEEN 1200 AND 1250  -- Current period
    AND executions_delta > 0
  GROUP BY sql_id
)
SELECT
  c.sql_id,
  b.avg_elapsed as baseline_elapsed,
  c.avg_elapsed as current_elapsed,
  ROUND((c.avg_elapsed - b.avg_elapsed) / b.avg_elapsed * 100, 2) as pct_change,
  st.sql_text
FROM baseline_period b,
     current_period c,
     dba_hist_sqltext st
WHERE b.sql_id = c.sql_id
  AND c.sql_id = st.sql_id
  AND c.avg_elapsed > b.avg_elapsed * 1.5  -- 50% slower
ORDER BY pct_change DESC;

🚀 AWR Automation

Automated Reporting

Scheduled AWR Reports

-- Create program for AWR report generation
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM(
    program_name   => 'GENERATE_AWR_REPORT',
    program_type   => 'PLSQL_BLOCK',
    program_action => '
    DECLARE
      l_snapid_start NUMBER;
      l_snapid_end   NUMBER;
      l_report       CLOB;
      l_file         UTL_FILE.FILE_TYPE;
    BEGIN
      -- Get last 2 snapshots
      SELECT snap_id INTO l_snapid_end
      FROM dba_hist_snapshot
      WHERE rownum = 1 ORDER BY snap_id DESC;

      SELECT snap_id INTO l_snapid_start
      FROM dba_hist_snapshot
      WHERE snap_id < l_snapid_end
        AND rownum = 1 ORDER BY snap_id DESC;

      -- Generate report
      SELECT output
      INTO l_report
      FROM TABLE(DBMS_WORKLOAD_REPOSITORY.AWR_REPORT_HTML(
        (SELECT dbid FROM v$database),
        (SELECT instance_number FROM v$instance),
        l_snapid_start,
        l_snapid_end
      ));

      -- Save to file
      l_file := UTL_FILE.FOPEN(''AWR_REPORTS'',
                ''awr_'' || TO_CHAR(SYSDATE, ''YYYYMMDDHH24MI'') || ''.html'',
                ''W'', 32767);
      UTL_FILE.PUT_LINE(l_file, l_report);
      UTL_FILE.FCLOSE(l_file);
    END;',
    enabled        => TRUE
  );
END;
/

-- Schedule daily AWR report
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name      => 'DAILY_AWR_REPORT',
    program_name  => 'GENERATE_AWR_REPORT',
    start_date    => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=8',
    enabled       => TRUE
  );
END;
/

Performance Alert System

-- Create performance monitoring job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name   => 'PERFORMANCE_MONITOR',
    job_type   => 'PLSQL_BLOCK',
    job_action => '
    DECLARE
      l_cpu_usage NUMBER;
      l_wait_time NUMBER;
    BEGIN
      -- Check current CPU usage
      SELECT value INTO l_cpu_usage
      FROM v$sysmetric
      WHERE metric_name = ''CPU Usage Per Sec''
        AND group_id = 2;

      -- Check average wait time
      SELECT value INTO l_wait_time
      FROM v$sysmetric
      WHERE metric_name = ''User Transaction Per Sec''
        AND group_id = 2;

      -- Send alert if thresholds exceeded
      IF l_cpu_usage > 80 THEN
        DBMS_SCHEDULER.CREATE_JOB(
          job_name => ''ALERT_HIGH_CPU_'' || TO_CHAR(SYSDATE, ''YYYYMMDDHH24MI''),
          job_type => ''PLSQL_BLOCK'',
          job_action => ''BEGIN send_alert(''''High CPU Usage: '''' || :cpu || ''''%''''); END;'',
          auto_drop => TRUE,
          enabled => TRUE
        );
        DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
          ''ALERT_HIGH_CPU_'' || TO_CHAR(SYSDATE, ''YYYYMMDDHH24MI''),
          1, l_cpu_usage
        );
      END IF;
    END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=MINUTELY; INTERVAL=5',
    enabled => TRUE
  );
END;
/

🛠️ AWR Maintenance

Space Management

AWR Repository Maintenance

-- Check AWR space usage
SELECT
  occupant_name,
  space_usage_kbytes / 1024 as space_mb,
  space_usage_kbytes / 1024 / 1024 as space_gb
FROM v$sysaux_occupants
WHERE occupant_name LIKE '%AWR%'
ORDER BY space_usage_kbytes DESC;

-- Purge old AWR data
BEGIN
  DBMS_WORKLOAD_REPOSITORY.DROP_SNAPSHOT_RANGE(
    low_snap_id  => 1,
    high_snap_id => 500,
    dbid         => (SELECT dbid FROM v$database)
  );
END;
/

-- Move AWR data to different tablespace
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    tablespace_name => 'AWR_DATA'
  );
END;
/

Performance Optimization

-- Check AWR collection overhead
SELECT
  snap_id,
  begin_interval_time,
  end_interval_time,
  flush_elapsed / 100 as flush_time_sec,
  snap_flag
FROM dba_hist_snapshot
WHERE begin_interval_time > SYSDATE - 1
ORDER BY flush_elapsed DESC;

-- Optimize AWR for specific workloads
BEGIN
  DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(
    topnsql => 50,        -- Reduce top SQL captured
    interval => 30        -- More frequent snapshots
  );
END;
/

📖 Best Practices

Configuration Guidelines

1. Snapshot Frequency

   • Normal workloads: 60 minutes
   • High-change workloads: 30 minutes
   • Low-activity systems: 2-4 hours

2. Retention Period

   • Minimum: 7 days
   • Recommended: 30 days
   • High-compliance: 90+ days

3. Storage Planning

   • Plan 100-500 MB per day
   • Consider compression for long retention
   • Monitor SYSAUX tablespace growth

Analysis Workflow

1. Regular Health Checks

   • Review daily AWR reports
   • Monitor key performance metrics
   • Identify trending issues

2. Performance Investigation

   • Start with AWR report overview
   • Focus on top wait events
   • Analyze top SQL statements
   • Review resource usage patterns

3. Capacity Planning

   • Use historical trends
   • Create performance baselines
   • Monitor growth patterns
   • Plan for peak periods

Oracle AWR provides the foundation for proactive database performance management. Regular analysis of AWR data enables early problem detection, capacity planning, and performance optimization initiatives.

🔗 Related Topics

• ADDM (Automatic Database Diagnostic Monitor) - Automated performance analysis
• ASH (Active Session History) - Real-time session monitoring
• Oracle Scheduler - Automate AWR report generation
• Performance Tuning Scripts - Additional monitoring utilities