How to read AWR Report oracle DB

Oracle AWR Report Analysis: Key Sections for Performance Monitoring

The Automatic Workload Repository (AWR) report is a powerful diagnostic tool in Oracle Database that provides a snapshot of database performance over a specified time interval. Analyzing different sections of the AWR report helps DBAs identify performance bottlenecks and trends.

This document outlines several key sections of an AWR report and what information they provide.

Report Header

This is the introductory section of the AWR report.

• Content: Provides fundamental information about the database and the AWR snapshot interval, including:

  • Database Name and ID

  • Instance Name (if RAC)

  • Platform Information

  • Snap Interval (the duration of the snapshot)

Load Profile

The Load Profile section provides a high-level summary of database activity during the snapshot interval, normalized to per-second rates.

• DB time(s): The total amount of time Oracle has spent performing database user calls. This does not include background processes.

• DB CPU(s): The amount of CPU time spent on user calls. Similar to DB time, it does not include background processes. The value is typically in microseconds.

• Redo size: The average amount of redo data generated per second by transactions.

• Logical reads: Represents Consistent Gets + DB Block Gets. This is the total number of blocks read from the buffer cache (logical I/O).

• Block Changes: The number of data blocks modified during the sample interval.

• Physical reads: The number of block requests that caused a physical I/O operation (i.e., had to be read from disk).

• Physical writes: The number of physical writes performed to datafiles.

• User calls: The number of user queries/calls generated per second.

• Parses: The total number of parse operations (both hard and soft) per second.

• Hard Parses: The number of parses requiring a completely new parse of the SQL statement. These consume significant resources (latches and shared pool area).

• Soft Parses: (Derived by subtracting hard parses from total parses). A soft parse reuses a previously hard-parsed SQL statement, consuming significantly fewer resources.

• Sorts: The number of sort operations performed per second.

• Logons: The number of new user logons during the interval.

• Executes: The number of SQL executions per second.

• Transactions: The number of user transactions per second.

Shared Pool Statistics

This section provides insights into the utilization and efficiency of the Shared Pool, a critical SGA component.

• Begin/End Memory Usage %: Shows the percentage of the Shared Pool used at the beginning and end of the snapshot.

• SQL with executions > 1 %: Percentage of SQL statements in the Shared Pool that have been executed more than once.

• Memory for SQL w/exec > 1 %: Percentage of Shared Pool memory consumed by SQL statements executed more than once.

Interpretation:

• Ideally, memory usage should not be excessively high (e.g., consistently beyond 90%), as this could indicate contention in the shared pool and suggest it is too small.

• If the percentage is very low (e.g., around 50%), it might indicate that the shared pool is too large for the current workload, leading to wasted memory.

Instance Efficiency Percentages (Target 100%)

These percentages indicate how efficiently the database instance is operating. Values close to 100% are desirable.

• Buffer Nowait %: Percentage of buffer gets that did not have to wait.

• Redo NoWait %: Percentage of redo entries that did not have to wait for space in the redo buffer.

• Buffer Hit %: Percentage of logical reads that found the block already in the buffer cache (avoiding physical I/O).

• In-memory Sort %: Percentage of sort operations performed entirely in memory (not spilling to disk).

• Library Hit %: Percentage of parse calls that found the SQL statement already in the library cache (soft parse).

• Soft Parse %: Percentage of total parse calls that were soft parses.

• Execute to Parse %: Percentage of executions relative to parses. A high value indicates good SQL reuse.

• Latch Hit %: Percentage of latch gets that were successful on the first attempt.

• Parse CPU to Parse Elapsd %: Ratio of CPU time spent parsing to total elapsed time during parsing. A low value might indicate parsing contention.

• % Non-Parse CPU: Percentage of CPU time spent on actual execution rather than parsing.

Top 5 Timed Foreground Events

This crucial section identifies the top 5 events that foreground processes spent the most time waiting for. "Time" is a very important component here, as it directly points to where the database is spending its time waiting. Analyzing these events is often the starting point for performance tuning.

RAC Statistics

This section is present in AWR reports generated for Oracle Real Application Clusters (RAC) environments. It provides metrics specific to inter-instance communication and resource management, such as Global Cache (GC) related wait events (e.g., gc current block request, gc cr block request).

Time Model Statistics

This section breaks down the total DB time into various components, showing how much time was spent on different database activities (e.g., SQL execution, parsing, PL/SQL execution, Java execution). This provides a granular view of where the database time is actually being consumed.

SQL Statistics

This section provides detailed performance metrics for individual SQL statements. It typically includes sub-sections that order SQL statements by:

• SQL ordered by Elapsed Time: Top SQL statements consuming the most total time (CPU + Wait).

• SQL ordered by CPU Time: Top SQL statements consuming the most CPU time.

• SQL ordered by Gets: Top SQL statements performing the most logical reads (buffer gets).

• SQL ordered by Reads: Top SQL statements performing the most physical reads.

I/O Stats Section

This section provides detailed statistics about I/O operations.

• Av Rd(ms) (Average Read time in milliseconds): This metric is a key indicator of I/O latency. Ideally, this value should not cross beyond 10ms. A value consistently above this threshold is considered an I/O bottleneck, indicating that the storage subsystem is struggling to deliver data quickly.

Advisory Statistics

Advisory sections provide recommendations for optimizing various SGA and PGA memory components.

• Buffer Pool Advisory: Recommends optimal DB_CACHE_SIZE based on workload simulation.

• PGA Aggr Summary: Summary of PGA memory usage.

• PGA Aggr Target Stats: Statistics related to the PGA Aggregate Target.

• PGA Aggr Target Histogram: Distribution of PGA memory usage.

• PGA Memory Advisory: Recommends optimal PGA_AGGREGATE_TARGET based on workload.

• Shared Pool Advisory: Recommends optimal SHARED_POOL_SIZE.

• SGA Target Advisory: Recommends optimal SGA_TARGET (if using Automatic Shared Memory Management).

• Streams Pool Advisory: Recommends optimal STREAMS_POOL_SIZE (if using Oracle Streams).

• Java Pool Advisory: Recommends optimal JAVA_POOL_SIZE (if using Java in the database).

init.ora Parameters

This section lists the values of all initialization parameters that were set at the instance level during the AWR snapshot interval. This is useful for understanding the database's configuration.

By thoroughly reviewing these sections in an AWR report, DBAs can gain a comprehensive understanding of database performance, identify bottlenecks, and make informed decisions for tuning and optimization.

=====================================================================

Here's how you can derive DB IOPS (Input/Output Operations Per Second) and Throughput (data transferred per second) from the sections of an AWR report you've described:

To understand your database's I/O performance, you'll primarily look at the "Load Profile" and potentially infer from the "I/O Stats" section.

Deriving DB IOPS (Input/Output Operations Per Second)

IOPS represents the number of individual read or write operations performed per second.

1. Identify Key Metrics in "Load Profile":

   • Physical reads: This is the total number of data blocks that Oracle had to read from disk into memory during the AWR snapshot interval. Each of these counts as an I/O operation.
   • Physical writes: This is the total number of data blocks that Oracle wrote from memory to disk during the AWR snapshot interval. Each of these also counts as an I/O operation.

2. Find the AWR Snapshot Interval:

   • Look at the "Report Header" section. It will specify the start and end times of the AWR snapshot, from which you can calculate the elapsed_seconds.
     • AWR Interval (seconds) = END_TIME - START_TIME

3. Calculate IOPS:

   • Total IOPS = (Physical reads + Physical writes) / AWR Interval (seconds)

   Example: If Physical reads = 1,000,000, Physical writes = 500,000, and AWR Interval = 3600 seconds (1 hour): Total IOPS = (1,000,000 + 500,000) / 3600 = 1,500,000 / 3600 ≈ 416.67 IOPS

Deriving DB Throughput (Data Transferred per Second)

Throughput represents the total volume of data (in MB/GB) transferred to or from disk per second.

1. Identify Key Metrics in "Load Profile":

   • Again, use Physical reads and Physical writes.

2. Determine Database Block Size:

   • The AWR report itself usually lists the database block size (e.g., 8KB, 16KB) in the "Report Header" or "init.ora Parameters" section. If not, you can query show parameter db_block_size in SQL*Plus.
   • Convert the block size to bytes (e.g., 8KB = 8 * 1024 bytes = 8192 bytes).

3. Find the AWR Snapshot Interval:

   • As before, from the "Report Header".

4. Calculate Throughput:

   • Total Throughput (Bytes/sec) = ((Physical reads + Physical writes) * Database Block Size in Bytes) / AWR Interval (seconds)
   • To convert to Megabytes per second (MB/sec), divide the result by (1024 * 1024).
   • To convert to Gigabytes per second (GB/sec), divide the result by (1024 * 1024 * 1024).

   Example: Using the above IOPS example, with a Database Block Size = 8192 bytes: Total Throughput (Bytes/sec) = (1,500,000 * 8192) / 3600 = 12,288,000,000 / 3600 ≈ 3,413,333 Bytes/sec Total Throughput (MB/sec) = 3,413,333 / (1024 * 1024) ≈ 3.25 MB/sec

Using "I/O Stats" Section

The "I/O Stats" section, particularly the Av Rd(ms) (Average Read time in milliseconds), is an indicator of I/O latency, not directly IOPS or throughput.

• Av Rd(ms): This metric tells you how quickly your storage subsystem is responding to read requests.
  • A value consistently above 10ms (as you noted) indicates a potential I/O bottleneck, suggesting that your storage system might be struggling to keep up with the demand, which would directly impact your achievable IOPS and throughput. While it doesn't give you the raw numbers, it's a critical health check for your I/O subsystem.