Oracle DBA

Oracle Database performance tuning is a dance of insight and intuition, a symphony of numbers and narratives.
In this blog, we unravel the Automatic Workload Repository (AWR) report, one of the DBA’s most powerful instruments for diagnosing bottlenecks, isolating pain points, and transforming sluggish systems into seamless symphonies.

1. What Is the AWR Report?

The Automatic Workload Repository (AWR) is Oracle’s internal historian—a repository of system statistics, session activity, wait events, and more. Born in Oracle 10g, maturing with every release, it has become a cornerstone for proactive and reactive tuning.
AWR snapshots are collected by the MMON background process, sampling system state at regular intervals and storing it in the SYSAUX tablespace.

Key Takeaways:

• AWR is auto-generated at scheduled intervals (default: every hour).
• AWR collects vital stats: User I/O, wait events, system ops, workload metrics.
• Privileges needed: SELECT ANY DICTIONARY and execute on DBMS_WORKLOAD_REPOSITORY.

2. How to Generate AWR Reports

Generating AWR is effortless if you have the right privileges:

• Enable the DBA tab in Oracle SQL Developer.
• Connect with a user granted the necessary privileges.
• Use the AWR viewer to select the snapshot range.

Tip: Always request from your DBA the required access if you’re a developer investigating performance. This fosters collaboration and rapid root cause analysis.

3. Anatomy of an AWR Report

Let’s wander through the essential corridors of the AWR report:

a. Header Section

• Database, DBID, Instance, Release: Context for the report.
• Begin/End Snap: The time window you are analyzing.
• Elapsed Time: Wall-clock duration of the report.
• DB Time: Total time spent by all sessions doing work—an aggregate indicator of database load.

Why Does ‘DB Time’ Matter?

Divide DB Time by Elapsed Time to get Average Active Sessions (AAS)—a critical indicator of system workload.

AAS = DB Time / Elapsed Time
If AAS exceeds the number of CPU cores, your system is overburdened. A single CPU servicing 16–32 active sessions is a red flag—investigate urgently!

b. Load Profile & Instance Efficiency

• Redo Size: Volume of change generated—higher means more DML activity.
• Logical Reads: Memory block reads; high values could signal efficiency, but beware of latch contention.
• Physical Reads/Writes: Actual disk activity.
• Hard/Soft Parses, Sorts, Transactions: Parse counts inform about SQL reuse or lack thereof.

Instance Efficiency Ratios:
Legacy DBAs watch Buffer Hit % or Library Hit %, but modern tuning relies more on wait event analysis.
Buffer Hit Ratio ≠ Good Performance—the era of simple ratios is over.

c. The Heartbeat: Top 5 Timed Events

This is the pulse of performance—the bottleneck’s stage.

• Wait Events: Are sessions waiting on DB file sequential read, enq: TX - row lock contention, or something else?
• Time Spent & % of DB Time: High percentages mean these events dominate performance.
  • CPU Time topping the chart is healthy; wait events dominating is not.
  • Average Wait (ms): Averages above 1,000ms (1 second) on critical waits should ring alarm bells.

Example:
If enq: TM – contention consumes 53% of DB time, there’s a locking crisis. If DB file sequential read is high, tune your indexes.

d. Weight Classes & Application Impact

Oracle groups waits by class—Application, User I/O, System I/O, Commit, Network, etc.

• Application Waits: Usually due to lock contention, bad SQL, or application design.
• User I/O: Disk bottlenecks—may need storage tuning or SQL rewrites.

Insight: Frequent enq: TX - row lock contention or enq: TM - contention points to application issues, not just database configuration.

e. SQL Statistics – The Smoking Gun

AWR highlights top SQLs by:

• Elapsed Time: Which queries are time hogs?
• CPU Time: Which burn the most CPU?
• Buffer Gets/Physical Reads: Which stress memory or storage?
• Executions: Frequency matters—sometimes a lightweight query executed millions of times is the culprit.

Focus on “per execution” time, not just totals.
A single SQL taking 20 minutes per execution needs urgent attention, even if it only ran a few times.

f. Foreground & Background Events

• Foreground: User-driven actions—where end-user experience is shaped.
• Background: Internal Oracle processes (e.g., Log Writer’s log file parallel write).
  • High background waits may indicate I/O subsystem issues or suboptimal configuration.

4. Comparative Analysis & Trend Spotting

A single AWR report is a snapshot—a story in a moment. True diagnosis lies in trend analysis:

• Generate AWRs for the same interval over multiple days.
• Compare AAS, top events, and SQL profiles.
• Spot deviations from baseline—identify new bottlenecks.

5. AWR vs ASH: When Every Minute Counts

AWR cannot see spikes shorter than its interval (minimum 10 minutes).
Active Session History (ASH), however, lets you zoom in—analyzing session activity over much smaller windows, even seconds.

In 12c and above, ASH is embedded within the AWR report, and ADDM provides actionable recommendations.

6. Interpreting Recommendations—With Caution

The ADDM and AWR recommendations are guides, not gospel.

• Don’t blindly increase CPU or SGA based on a single report.
• Correlate spikes with workload trends, and only recommend infrastructure changes if patterns persist.
• Rely on your expertise—let the numbers guide, but not dictate.

7. Best Practices: AWR for Modern DBAs

• Set AWR retention appropriately: 8–30 days is typical; longer for unstable or business-critical systems.
• Reduce snapshot interval (to 10–15 min) for busy systems with frequent issues.
• Manually create snapshots before/after key changes or tests.
• Always correlate AWR insights with application behavior and business events.

Conclusion: Mastering the Art of Database Storytelling

An AWR report is more than a technical artifact—it’s a living narrative, a chronicle of what your database endured and overcame.
Approach each page as a seasoned detective, letting metrics whisper their secrets.
Performance tuning is less about chasing ratios and more about interpreting patterns, validating hypotheses, and—above all—listening to what the database is trying to say.

“In the orchestra of Oracle, AWR is your score, and you are the conductor. Read deeply, act wisely, and let harmony prevail.”

If you found this guide insightful, share it with your DBA circle or development team. Let’s elevate the craft—one AWR at a time.