AWR (Automatic Workload Repository) Evaluate

Evaluating an AWR (Automatic Workload Repository) report in Oracle 19c involves analyzing the database's performance data over a specific period. The report captures metrics that provide insights into the database's activity and resource consumption, helping DBAs identify performance issues such as CPU bottlenecks, wait events, SQL inefficiencies, or I/O problems.

Here is a step-by-step guide on how to evaluate an AWR report:

1. Report Header: Basic Information

This section contains important details about the report, including:

• DB Name and Instance: The name of the database and instance.

• Snapshot Period: Start and end time of the snapshot.

• Elapsed Time: Duration of the report, which should be long enough to capture meaningful performance data (e.g., 30 minutes to 1 hour).

• DB Time: The total time spent by all active database sessions during the report period. A high DB Time compared to Elapsed Time indicates heavy workload or high resource consumption.

2. Load Profile

This section summarizes the workload on the system, showing how many resources are being consumed per second and per transaction.

Key metrics to focus on:

• DB Time per second: Indicates how much time database sessions are spending in active work per second. A high number suggests high load.

• DB CPU per second: Shows how much CPU is consumed per second. Compare with DB Time to determine if the database is CPU-bound.

• Logical Reads: High values indicate a high number of buffer cache reads.

• Physical Reads/Writes: High values can indicate I/O bottlenecks.

• Redo Size: Tracks the amount of redo log generated, indicating transaction activity.

3. Instance Efficiency Percentages

This section provides a snapshot of how efficiently Oracle is using its resources, focusing on cache and buffer utilization.

• Buffer Cache Hit Ratio: Should be above 95%. If low, consider tuning the buffer cache.

• Library Cache Hit Ratio: A good ratio is above 95%. If lower, it indicates SQL or PL/SQL parsing issues.

• Redo NoWait Ratio: Indicates the percentage of redo requests that did not have to wait for redo log space.

• Latch Hit %: Should generally be over 99%. Low values indicate contention for latches (a type of lock).

4. Top 5 Timed Foreground Events

This section highlights the main performance bottlenecks in the database, listing the top events that contributed to DB Time.

Focus on:

• Wait Events: Check for common events like db file sequential read, db file scattered read, and log file sync. High wait times indicate I/O, concurrency, or CPU problems.

• % DB Time: Shows the percentage of time spent on each event. High percentages on specific events often indicate performance bottlenecks.

• Wait Time (sec): Look at the total time spent waiting on certain events. Focus on events with high wait times to understand where tuning is needed.

5. Time Model Statistics

This section breaks down where the database spends its time.

• DB CPU: Shows the total CPU time used. A high percentage compared to DB Time indicates a CPU-bound workload.

• SQL Execute Elapsed Time: High values show the time spent executing SQL statements. If this is a significant portion of the time, SQL tuning might be necessary.

• PL/SQL Execution Time: Indicates time spent in PL/SQL execution. High values may suggest inefficient PL/SQL code.

6. SQL Statistics (SQL ordered by ...)

Several sections provide performance details about individual SQL statements.

Focus on:

• SQL ordered by Elapsed Time: Shows SQLs that consumed the most time. Look for SQLs with high elapsed time and consider optimizing them.

• SQL ordered by CPU Time: Shows SQLs that consumed the most CPU. High CPU SQLs may benefit from better indexing or query optimization.

• SQL ordered by Gets: Indicates SQLs with the most logical I/O operations (buffer gets). High buffer gets could mean inefficient query plans.

• SQL ordered by Reads: Indicates SQLs with the most physical I/O operations. High physical reads can indicate inefficient SQL that’s causing excessive disk reads.

7. Instance Activity Stats

This section gives detailed statistics about database operations such as I/O, transactions, and sessions.

• User Calls: A high number of user calls might indicate inefficient application logic.

• DB Block Changes: A high value indicates a high volume of updates. Check if the updates are necessary or could be optimized.

• Physical Reads/Writes: High values point to I/O-intensive operations. Consider tuning queries to reduce disk access or adding indexes to reduce table scans.

8. Operating System Statistics

This section shows how the underlying operating system is performing, which can be useful for diagnosing system-level bottlenecks.

Key metrics:

• %Busy CPU: High values indicate CPU bottlenecks.

• %Idle CPU: A low idle percentage means the system is under heavy load.

• Load Average: Compare load averages to CPU cores. If the load average exceeds the number of cores, it may indicate CPU pressure.

• Swap Usage: High swap usage indicates memory pressure and may affect performance.

9. Wait Events (Detailed Section)

This section expands on the "Top 5 Timed Foreground Events" by providing a more detailed view of the wait events that occurred during the AWR snapshot.

• db file sequential read: Indicates single block reads, often due to index lookups. High wait times could mean I/O performance problems.

• log file sync: Indicates waits on redo log writes, often caused by slow disk I/O or inefficient commit strategies.

• buffer busy waits: Indicates contention for buffers in the buffer cache. High values could mean tuning the buffer cache or application design is necessary.

10. I/O Statistics

This section provides a breakdown of the I/O load and performance for different tablespaces and data files.

• Avg Read Time and Write Time: High read or write times could indicate I/O subsystem bottlenecks.

• Tablespace I/O: Shows the I/O activity per tablespace. If certain tablespaces have much higher I/O than others, consider tuning SQL related to objects in those tablespaces.

11. Recommendations

Based on the findings in the AWR report, you can make recommendations such as:

• SQL Tuning: Focus on SQL statements with high elapsed time or resource consumption.

• Wait Event Tuning: Address the top wait events, whether through hardware improvements, adjusting configurations, or optimizing application logic.

• Resource Allocation: Increase CPU or memory resources if the system is CPU- or memory-bound.

• I/O Tuning: If high I/O wait times are detected, consider hardware upgrades or optimizing SQL to reduce disk reads and writes.

Conclusion

Evaluating an AWR report involves focusing on key performance indicators such as CPU utilization, wait events, SQL performance, and I/O statistics. By systematically going through these sections, DBAs can identify bottlenecks and determine where tuning efforts should be focused to improve Oracle database performance.