Analyzing AWR (Automatic Workload Repository)

 

Reading an AWR (Automatic Workload Repository) report in Oracle can help DBAs analyze database performance over a specified time period. The AWR report captures system-level statistics, wait events, SQL performance, and more.

Here’s a step-by-step guide to reading an AWR report with examples:

---

🧾 1. Generate AWR Report

To generate an AWR report using SQL*Plus or SQL Developer:

@$ORACLE_HOME/rdbms/admin/awrrpt.sql

You'll be prompted to:

• Choose HTML or TEXT format.
• Enter the begin and end snapshot IDs.
• Specify the report name.

---

📘 2. Key Sections of the AWR Report

---

🔹 Header Information

Shows database name, instance, snapshot time range, DB version, etc.

📌 Example:

DB Name         DB Id    Instance     Inst Num Startup Time

------------ ----------- ------------ -------- ---------------

ORCL         1234567890  orcl1              1 10-Jun-25 06:00

---

🔹 Load Profile

This section gives a quick overview of the workload per second and per transaction.

📌 Example:

                             Per Second     Per Transaction

Redo size:                   45,000.00           5,000.00

Logical reads:              120,000.00          13,333.33

User calls:                   1,500.00             166.67

✅ Key metric:

• Logical reads indicate workload intensity.
• High redo size per transaction may indicate heavy DML activity.

---

🔹 Instance Efficiency Percentages

Shows how efficiently the instance is using its resources.

📌 Example:

Buffer Nowait %: 100.00    Redo NoWait %: 99.98

Buffer Hit %:    98.77     Library Hit %: 98.65

✅ Good numbers:

• Buffer Hit % > 95% is ideal.
• Library Hit % < 95% may indicate poor cursor sharing.

---

🔹 Top 5 Timed Events

This is one of the most critical sections. It lists the events consuming the most DB time.

📌 Example:

Event                                    Waits         Time(s)    Avg Wait(ms)

---------------------------          -----------    --------    ------------

DB file sequential read        1,200,000    3,200      2.67

log file sync                          300,000       2,000     6.67

CPU time                                                  1,800

✅ Interpretation:

• "DB file sequential read" usually means single block reads (index scans).
• "log file sync" suggests commits are waiting for redo to be flushed.
• High CPU time: Indicates CPU bottleneck.

---

🔹 SQL Statistics (SQL ordered by…)

You’ll find SQLs sorted by:

• Elapsed time
• CPU time
• Buffer gets
• Disk reads
• Executions

📌 Example (SQL ordered by Elapsed Time):

Elapsed Time (s) Executions  SQL Id

---------------         -----------   -------------

    12,000               3,000       2c4jx7a7b8fk2

 

SQL Text:

SELECT * FROM orders WHERE customer_id = :1;

✅ Look at SQLs with:

• High elapsed time and low executions: Likely inefficient.
• High buffer gets or disk reads: May need indexing or tuning.

---

🔹 IO Stats / Tablespace and File IO

Helps you identify I/O hotspots.

📌 Example:

Tablespace        Reads/s    Writes/s   Av Rd(ms)

----------------- --------   --------   ---------

USERS                 100         5         2.5

UNDOTBS1         10        25         1.2

✅ Look for:

• High latency (Avg Rd(ms) > 10 ms)
• Unexpected I/O activity in temp or undo tablespaces

---

🔹 Advisory Sections

• Buffer Cache Advisory: Suggests if increasing buffer cache helps.
• PGA Advisory: Indicates optimal PGA size for your workload.
• Shared Pool Advisory: Helps size shared pool to avoid parsing overhead.

---

✅ Summary for Performance Troubleshooting

Symptom	Section to Check	What to Look For
High DB time	Top Timed Events	Which waits dominate
Slow queries	SQL Ordered by Elapsed/Buffer Gets	Long-running or high-resource SQLs
High CPU	Instance Efficiency, Timed Events	CPU Time % vs Wait time
Disk I/O issues	File IO Stats	High latency or IO/sec
Memory issues	Advisory Sections	Recommendations for tuning SGA/PGA

---

📍 Example Use Case

Problem: Application is slow.

Approach using AWR:

1. Check Top Timed Events → High "db file scattered read".
2. Go to SQL ordered by Reads → Find query doing full table scans.
3. Review query plan → Missing index found.
4. Add index → Regenerate AWR → Performance improved.

---