Oracle DBA

A typical AWR report is structured into several sections. Here’s a breakdown of the most critical ones you should focus on.

Report Summary

This is the first section and provides a high-level overview. It includes:

• Report Info: The start and end times of the report, the duration of the snapshot, and the database and instance names.
• Host and DB Info: Details about the host server and the Oracle database version.
• Load Profile: This section is crucial. It shows key metrics per second and per transaction, such as database time, redo size, logical reads, physical reads, and user commits. These numbers help you gauge the overall activity level. A high “database time” per second indicates the database was very busy.

Wait Events Statistics

This is arguably the most important section. It identifies where the database spent most of its time waiting. Oracle categorizes these waits into different event classes. The report shows a list of the top 5 to 10 wait events, sorted by the total time spent.

• DB CPU: This isn’t a wait event but a category showing the amount of time the database spent actively using CPU.
• log file sync: The database waits for the log writer process to write a commit to the redo log file. High waits here often point to slow I/O on the redo log drives or a high number of commits.
• db file sequential read: This indicates the database is waiting for single block reads, typically from index lookups. High waits could mean inefficient queries or a high number of index scans.
• db file scattered read: This represents waits for multi-block reads, usually from full table scans.
• latch: cache buffers chains: This indicates contention for memory structures, often caused by a large number of concurrent sessions trying to access the same data block.

SQL Statistics

This section identifies the top SQL statements that consumed the most resources during the report’s snapshot. The report ranks them based on metrics like elapsed time, CPU time, physical reads, and logical reads.

• SQL ordered by Elapsed Time: Shows the SQL statements that took the longest to complete. These are often prime candidates for tuning.
• SQL ordered by CPU Time: Highlights queries that are CPU-intensive.
• SQL ordered by Gets (Logical Reads): Identifies queries that perform a high number of logical I/O operations, which can be a sign of inefficient data access.
• SQL ordered by Reads (Physical Reads): Points to queries that are performing a lot of I/O from disk, which is typically slow.

I/O and System Statistics

These sections provide a detailed look at the system’s I/O and resource usage.

• Tablespace I/O Stats: Breaks down I/O activity per tablespace, helping you identify which parts of your database are the busiest. High I/O on a specific tablespace might indicate a lot of reads or writes on the objects within it.
• Buffer Pool Statistics: Shows the hit ratio for the database buffer cache. While not as critical as it once was, a very low hit ratio can suggest that the buffer cache is too small.

---

Example Walkthrough

Let’s imagine you’re reviewing a report with the following key findings:

Scenario:

• Load Profile: The report shows a high “Database time” and “Execute to Parse” ratio.
• Wait Events: The top wait event is “log file sync”, accounting for 60% of the total wait time. The next highest is “DB CPU” at 20%.
• SQL Statistics: The top SQL statement by elapsed time is a simple INSERT statement.

Analysis:

The high “log file sync” wait time immediately points to a problem with the redo log writes. The fact that the top SQL statement is an INSERT statement further confirms this. Insert operations require a commit, which triggers a redo log write. The database is spending a significant amount of time waiting for these writes to complete. This could be due to:

1. Slow I/O: The disks where the redo logs are located are slow.
2. High Commit Rate: A large number of users are committing transactions frequently, causing a bottleneck at the log writer process.

Recommendations:

Based on this analysis, you would recommend the following actions:

• Investigate I/O performance: Check the disk subsystem where the redo logs reside. Consider moving them to faster storage like SSDs.
• Batch Commits: If possible, modify the application to batch multiple inserts into a single transaction with one commit, reducing the frequency of log writes.
• Review commit frequency: Identify if there are unnecessary commit statements within a loop in the application code.