Analyzing an Automatic Workload Repository (AWR) report is one of the most critical skills an Oracle Database Administrator or Performance Engineer can master. An AWR report provides a comprehensive snapshot of system health, database throughput, resource bottlenecks, and top-consuming SQL queries over a specified time window.
However, interpreting an AWR report effectively requires more than just reading numbers off a page. It demands a systematic approach to identifying root causes, understanding database internals, and strictly complying with Oracle's licensing framework.
1. Oracle Licensing Requirements & Compliance Pitfalls
Before executing your first @awrrpt.sql script, you must understand the licensing implications of generating and analyzing AWR data.
┌────────────────────────────────────────┐
│ Oracle Enterprise Edition (EE) Only │
└──────────────────┬─────────────────────┘
│
┌──────────┴──────────┐
▼ ▼
┌──────────────────────────────┐ ┌──────────────────────────────┐
│ Oracle Diagnostics Pack │ │ Oracle Tuning Pack │
│ (AWR, ASH, ADDM, Performance│ │ (SQL Tuning Advisor, SQL │
│ Hub) │ │ Access Advisor, SPA) │
└──────────────────────────────┘ └──────────────────────────────┘
Required Licenses
Oracle Diagnostics Pack: This pack is mandatory to legally use AWR (generation of reports, querying
DBA_HIST_*views, accessing Active Session History (ASH), and using Automatic Database Diagnostic Monitor (ADDM)).Oracle Tuning Pack: Required if you plan to use automated tuning tools on the SQL IDs identified in your AWR report—such as the SQL Tuning Advisor, SQL Access Advisor, or SQL Performance Analyzer (SPA). Note that the Tuning Pack requires the Diagnostics Pack as a prerequisite.
Edition Limitations
Enterprise Edition (EE) Only: The Oracle Diagnostics Pack and Tuning Pack are available exclusively as add-on options for Oracle Database Enterprise Edition.
Standard Edition 2 (SE2) Restrictions: If you are running Standard Edition 2 (SE2), you are not licensed to use AWR, regardless of whether the packages or scripts appear to work technically.
Audit Risks & The "Diagnostic Trap"
Oracle builds performance features directly into the database engine binaries. This means that running scripts like @awrrpt.sql or querying historical DBA_HIST_* tables will work out-of-the-box on Enterprise Edition—even if you haven't paid for the Diagnostics Pack.
However, Oracle tracks every invocation of these tools in internal tracking views (such as DBA_FEATURE_USAGE_STATISTICS). During an Oracle License Management Services (LMS) audit, if usage of the Diagnostics Pack is detected on an unlicensed system, Oracle will retroactively bill you for the Diagnostics Pack across every physical processor core on that database server.
2. Verifying License Availability & Management Pack Status
To ensure compliance, verify the licensing state of your database before collecting AWR snapshots or generating reports.
Step 1: Check the Management Pack Access Parameter
Oracle controls access to AWR and related tools using the CONTROL_MANAGEMENT_PACK_ACCESS initialization parameter.
Run the following command in SQL*Plus or SQL Developer:
SHOW PARAMETER control_management_pack_access;
Alternatively, query V$PARAMETER:
SELECT name, value
FROM v$parameter
WHERE name = 'control_management_pack_access';
Understanding the Values:
DIAGNOSTIC+TUNING: Both Diagnostics Pack and Tuning Pack features are fully enabled.DIAGNOSTIC: Only Diagnostics Pack features (AWR, ASH, ADDM) are enabled.NONE: All management pack features are disabled. AWR snapshots will not be captured, and access to AWR scripts/views is restricted.
Step 2: Query Feature Usage Statistics
To check if AWR or Diagnostics Pack features have been logged as "used" in your environment, run:
SELECT
name,
detected_usages,
currently_used,
first_usage_date,
last_usage_date
FROM dba_feature_usage_statistics
WHERE name IN ('AWR Report', 'Diagnostic Pack', 'Tuning Pack', 'ADDM')
OR name LIKE '%AWR%'
ORDER BY name;
If detected_usages is greater than 0, the database has registered usage of these licensed features.
How to Disable AWR Access on Unlicensed Databases
If you do not own a Diagnostics Pack license, set the parameter to NONE immediately to prevent accidental usage and audit penalties:
ALTER SYSTEM SET control_management_pack_access='NONE' SCOPE=BOTH;
The Free Alternative: Oracle Statspack
If you are on Standard Edition 2 or do not hold a Diagnostics Pack license, you can use Oracle Statspack. Statspack is a free performance-monitoring utility included with all Oracle Database editions.
Installation: Run
@?$ORACLE_HOME/rdbms/admin/spinstall.sqlunder a dedicatedPERFSTATschema.Snapshot Collection: Schedule snapshots using
DBMS_SCHEDULERorspauto.sql.Report Generation: Run
@?$ORACLE_HOME/rdbms/admin/spreport.sqlto generate performance reports structurally similar to AWR.
3. Dissecting the Critical Portions of an AWR Report
When opening an AWR report, avoid reading it linearly from top to bottom. Focus first on macro-level metrics to establish a baseline, then drill down into specific resource bottlenecks.
┌─────────────────────────────────────────────────────────────────────────┐
│ AWR ANALYSIS WORKFLOW │
└────────────────────────────────────┬────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ 1. Header & Snapshot Info ──► Verify Elapsed vs. DB Time & AAS │
└────────────────────────────────────┬────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ 2. Load Profile ──► Measure Workload Intensity & Parse Ratio │
└────────────────────────────────────┬────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ 3. Top 10 Wait Events ──► Pinpoint Primary Resource Bottleneck │
└────────────────────────────────────┬────────────────────────────────────┘
│
▼
┌─────────────────────────────────────────────────────────────────────────┐
│ 4. SQL Statistics ──► Identify Top SQL_IDs Driving Events │
└─────────────────────────────────────────────────────────────────────────┘
A. Header & Snapshot Information
The header section establishes the scope and timeframe of your analysis.
Elapsed Time: The actual clock time between the starting and ending snapshots. For effective tuning, target a 30-to-60-minute window during a peak load or incident period.
DB Time (Database Time): The total time spent by foreground sessions either actively executing on CPU or waiting on non-idle wait events.
$$\text{DB Time} = \text{CPU Time} + \text{Foreground Wait Time}$$Average Active Sessions (AAS): Calculated as:
$$\text{AAS} = \frac{\text{DB Time}}{\text{Elapsed Time}}$$If $\text{AAS}$ exceeds the total number of physical CPU cores allocated to the instance, your system is experiencing thread contention and resource exhaustion.
B. Load Profile
The Load Profile displays database activity rates per second and per transaction.
Redo size (bytes) per second: Indicates DML activity (INSERT, UPDATE, DELETE). Sudden spikes suggest heavy batch processing or unindexed bulk operations.
Logical Reads per second: Measures total block requests satisfied from either the Buffer Cache or Disk. Very high logical reads per second burn significant CPU.
Physical Reads per second: Measures block reads forced to go to storage disk/flash. High physical reads indicate poor indexing, full table scans, or undersized buffer caches.
Hard Parses per second: Hard parsing occurs when Oracle must fully evaluate, optimize, and generate an execution plan for a SQL statement. A hard parse rate consistently above 20-50 per second signals that the application is failing to use bind variables.
C. Top 10 Foreground Wait Events
This section is the most critical part of the entire report. It reveals what database sessions were waiting on when they were not processing on CPU.
| Wait Event | Primary Cause | Recommended Action / Focus |
| DB CPU | Sessions are actively executing code on CPU. | High DB CPU is usually driven by SQL statements doing excessive logical reads (gets). Focus on tuning high-get SQLs. |
| db file sequential read | Single-block index lookups or table fetches by ROWID from storage. | Normal for OLTP, but high wait times point to slow storage latency, fragmented indexes, or nested loop joins hitting stale indexes. |
| db file scattered read | Multi-block physical reads (Full Table Scans or Fast Full Index Scans). | Check for missing indexes, out-of-date statistics forcing full table scans, or unindexed foreign keys. |
| log file sync | User commits/rollbacks waiting for Log Writer (LGWR) to flush redo buffers to disk. | Look for applications committing inside row-by-row loops, slow redo log disk storage, or undersized Redo Log files. |
| direct path read / write | Asynchronous I/O bypassing the Buffer Cache (Smart Scans, Parallel Processing, Direct Path Inserts). | Expected in Data Warehousing. In OLTP, check for unintended parallel queries or large temporary space usage in temp tablespaces. |
| enq: TX - row lock contention | Sessions waiting for an uncommitted transaction to release a lock on a specific table row. | Identify the blocking session/SQL. Review application logic for long-running uncommitted transactions or row-level lock contention. |
D. Oracle RAC Specific Wait Events
In an Oracle Real Application Clusters (RAC) environment, inter-node communication across the private interconnect introduces specialized Global Cache ($gc$) wait events:
gc buffer busy acquire/gc buffer busy release: Occurs when a session on one instance requests a data block from another instance, but that block is already pinned or locked by a local process. This indicates severe block-level contention across RAC nodes.gc cr block receive: Measures the time taken to receive a Consistent Read (CR) block over the interconnect. High latency here points to interconnect network congestion, dropped packets, or poor network interface card (NIC) configuration.gc current block receive: Measures latency when transferring a current (modifiable) block across nodes for DML operations.
E. Memory & Advisory Statistics
Buffer Cache Advisory: Simulates how physical I/O would change if you expanded or shrank the
DB_CACHE_SIZE. Look for the row with anEstd Physical Read Factornear $1.0$; expanding beyond this point yields diminishing returns.PGA Target Advisory: Evaluates whether increasing
PGA_AGGREGATE_TARGETwill reduce disk-based sorting and hash-join spilling to theTEMPtablespace.
4. Identifying SQL Statements That Require Tuning
After identifying your primary wait event from the Top 10 list, move to the SQL Statistics section to locate the specific SQL_ID values responsible for that workload.
1. SQL ordered by Elapsed Time
Statements with the highest total elapsed execution time. This includes both CPU processing time and wait times. Start your tuning here for the most significant immediate system impact.
2. SQL ordered by CPU Time
Queries consuming the highest volume of CPU processing cycles. These are often CPU-bound queries executing complex mathematical functions, large string manipulations, or millions of logical buffer reads.
3. SQL ordered by Gets (Logical Reads)
SQLs pulling the most blocks out of the Buffer Cache. A query with millions of buffer gets per execution is thrashing CPU and memory, often due to an inefficient index path or Nested Loop join.
4. SQL ordered by Reads (Physical Reads)
Queries driving physical disk reads. Focus on this section if db file scattered read or db file sequential read dominates your top wait events.
5. SQL ordered by Executions
High-frequency queries. A lightweight query executing 100 times per second can accumulate more overall system overhead than a slow batch query that runs once a night.
6. SQL ordered by Cluster Wait Time (RAC)
Identifies the exact SQL statements causing block transfers over the RAC interconnect. Tuning these statements to reduce block visits directly resolves global cache bottlenecking.
7. SQL ordered by Parse Calls
Highlights queries being parsed repeatedly. High parse counts combined with low execution counts per parse point directly to un-bound literal SQL.
5. Diagnostic Checklist: How to Determine What & How to Tune
Finding a problematic SQL_ID is only the first step. Use this analytical framework to evaluate the query metrics and determine the correct corrective action.
┌────────────────────────────────────────┐
│ Analyze Per-Execution Metrics │
└──────────────────┬─────────────────────┘
│
┌────────────────────────────────┼────────────────────────────────┐
▼ ▼ ▼
┌───────────────────────────┐ ┌───────────────────────────┐ ┌───────────────────────────┐
│ High Gets / Execution │ │ High Exec, Low Time/Exec │ │ High Reads vs. Low Gets │
├───────────────────────────┤ ├───────────────────────────┤ ├───────────────────────────┤
│ Inefficient Execution │ │ Application N+1 Problem │ │ Cold Cache or Full Table │
│ Plan / Missing Index │ │ Refactor to Bulk Operations│ │ Scan / Unindexed Join │
└───────────────────────────┘ └───────────────────────────┘ └───────────────────────────┘
1. Calculate Per-Execution Metrics
Always normalize AWR totals by dividing by the Executions count:
High Gets per Execution ($>10,000$ gets for a 5-row return): The execution plan is visiting far too many blocks. The optimizer is likely picking a sub-optimal index or performing an inefficient full scan.
Low Time per Exec, High Executions ($0.001\text{s}$ over $1,000,000$ execs): The SQL execution plan is optimal, but the application is inefficiently calling the database in a row-by-row loop (the "N+1 query problem"). Solution: Refactor the application code to use array processing (
FORALL/ PL/SQL bulk collection).
2. Extract the Historical Execution Plan
To view the execution plan stored in AWR for a specific SQL_ID, run:
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('YOUR_SQL_ID_HERE'));
3. Check for Cardinality Mismatches
Compare the Estimated Rows (E-Rows) predicted by the cost-based optimizer against the Actual Rows (A-Rows)returned at runtime.
If
E-Rows = 1butA-Rows = 2,500,000, the optimizer severely underestimated the row set and likely selected an inappropriate Nested Loop join instead of a Hash Join.Solution: Refresh stale object statistics using
DBMS_STATS.GATHER_TABLE_STATSor gather column histogram data.
4. Check for Bind Variable Misuse (Hard Parsing)
Check V$SQL or DBA_HIST_SQLTEXT for queries that differ only by literal values in their WHERE predicates:
-- Example of literal SQL anti-pattern:
SELECT * FROM orders WHERE order_id = 1001;
SELECT * FROM orders WHERE order_id = 1002;
Impact: Every distinct statement forces a hard parse, flooding the Shared Pool and causing
latch: shared poolandlibrary cache lockwaits.Solution: Rewrite application code to use bind variables (e.g.,
WHERE order_id = :b1). As a temporary system-wide stopgap, evaluate settingCURSOR_SHARING = FORCE.