Introduction
When troubleshooting Oracle Database performance, an AWR report is one of the most useful places to start. It provides a detailed view of database workload, CPU usage, wait events, I/O activity, and SQL performance. In this article, we explain how to read and analyze an AWR report step by step, with a focus on the key sections that help you identify performance bottlenecks.
What is AWR report?
AWR stands for Automatic Workload Repository. It is a built-in Oracle Database repository that periodically collects and stores performance statistics such as CPU usage, wait events, I/O activity, and SQL execution statistics. An AWR report summarizes the statistics captured between two AWR snapshots. This makes it useful for understanding database workload, detecting performance changes, and narrowing down the cause of performance problems. Note that access to AWR functionality generally requires the Oracle Diagnostics Pack license, so make sure your environment is properly licensed before using it.
Oracle Database periodically collects performance statistics and stores them in the AWR repository in the SYSAUX tablespace. These statistics are stored as AWR snapshots at a configurable interval and retained for a configurable period. Because the default interval and retention period can differ depending on the Oracle Database version and configuration, check the actual settings in your environment rather than relying on fixed values. When planning capacity, consider the snapshot interval, retention period, database size, and workload because AWR data consumes space in the SYSAUX tablespace.
How to get AWR report
An AWR report is generated from statistics stored between two AWR snapshots. Oracle provides scripts for generating AWR reports in formats such as HTML or text. Choose the appropriate script depending on whether you want to analyze a single instance, a RAC environment, or multiple snapshot ranges. In my own work, I often generate multiple AWR reports at once, so I frequently use get_awr_base.sql.
AWR Report Analysis Points
We will explain the main items of the AWR report. You don’t need to remember everything, but it’s a good idea to understand how the major sections relate to each other during performance analysis.
AWR Analysis Flow
When analyzing an AWR report, it is important not to evaluate individual metrics in isolation. Start with the overall database workload, identify where DB Time is being spent, and then narrow down the SQL statements responsible for the resource consumption.
The basic AWR analysis flow is as follows:
For example, if DB CPU accounts for a large portion of DB Time, check SQL ordered by CPU Time to identify CPU-intensive SQL statements. If I/O-related wait events account for a large portion of DB Time, check SQL ordered by Reads and investigate the execution plans of SQL statements generating significant physical I/O.
The key point is to move from the overall workload to the bottleneck, then from the bottleneck to the SQL statement causing it. Comparing the results with an AWR report from a normal period can also help determine whether the workload, wait events, or SQL behavior has changed.
Even if you generate an AWR report during a performance problem, it can be difficult to determine whether a value is truly abnormal without a baseline. Keep AWR reports from representative normal periods so that you can compare DB Time, wait events, workload, and SQL resource usage. This comparison often makes it much easier to identify what changed when a problem occurred.
Basic Information
Basic information about the database and environment is described. You can check information such as the CPU, memory, and number of instances of the database server.
| Main items | Explanation | Notes |
|---|---|---|
| RAC | Whether Oracle RAC is applied | |
| CPUs | Number of CPUs | |
| Cores | Number of cores | |
| Memory (GB) | Memory capacity | |
| Begin Snap | Time when AWR snapshot acquisition started. The number of sessions at the start is recorded in Sessions. | |
| End Snap | Time when AWR snapshot acquisition ended. The number of sessions at the end is recorded in Sessions. | Check whether the number of sessions has not changed significantly between the start and end, in other words, whether the load was constant. |
| Elapsed | AWR report acquisition period. | The default is 60 minutes. |
| DB Time | DB Time represents the total time spent by foreground sessions actively working in the database. It includes both CPU time and non-idle wait time. Because multiple sessions can run simultaneously, DB Time can be greater than the elapsed time of the AWR report. Background process time is not included. DB Time = DB CPU + Non-Idle Wait Time | Check that DB Time is not extremely large compared to normal times. |
Report Summary
Top ADDM Findings by average Active Sessions
These are the top diagnostic results from ADDM (Automatic Database Diagnostic Monitor). Check that the top Task trends have not changed significantly from normal.
Reference:https://jazz.net/wiki/bin/view/Deployment/RequirementsManagement70Performance
| Main items | Explanation | Note |
|---|---|---|
| Avg active sessions of the task | Time spent on the task session | Check to see if your session times are increasing. |
| Percent active sessions of findings | Percentage of processes that account for a large proportion of DB time | Make sure that the tasks taking up DB time are not different from normal. |
Load Profile
The Load Profile summarizes the overall workload handled by the database. It is one of the most important sections for determining whether performance degradation is caused by increased workload, more expensive processing per transaction, or both.
Do not look only at total values. Metrics such as Per Second and Per Transaction help distinguish between an increase in workload volume and an increase in the amount of work performed for each transaction.
Reference: https://www.dbanet.co.za/Summary/awr_load_profile.html
| Main Item | Explanation | Notes |
|---|---|---|
| DB Time | The total CPU time and wait event time for the Oracle server process during the AWR report capture period. | |
| DB CPU | The CPU time of the Oracle server process during the AWR report acquisition period. In other words, it is the value obtained by subtracting the wait event time from the DB Time. | |
| Redo size | This shows the size of the redo logs generated by DML (INSERT, UPDATE, DELETE) execution. | If the value is larger than normal, check to see if unnecessary DML is being issued. |
| Logical read | The number of database blocks accessed from the buffer cache. | A large number of logical reads may indicate SQL statements that process more blocks than necessary. |
| Physical read | Number of database blocks read from physical storage. | Please check as performance may deteriorate if there are many reads from the physical disk. |
| Physical writes | Number of database blocks written to physical storage. | If this value increases significantly, identify which workload or SQL statements are generating the writes and check storage performance. |
| User calls | Number of calls from users | If you notice a sudden increase in value, check what is happening with the user’s actions. |
| Parses (SQL) | Number of hard and soft parses | Parsing means analyzing, and syntax check is performed on the SQL statement. |
| Hard Parses | Number of hard parses | Hard parsing includes semantic checks, object and privilege resolution, and execution plan generation. A significant increase can consume CPU and shared pool resources. Evaluate the rate relative to the normal baseline and workload rather than relying on a universal threshold. The approximate number of soft parses can be calculated as “Parses – Hard Parses”. |
| Executes | SQL Execution Count | This includes not only the SQL statements issued by users, but also the number of SQL statements executed by Oracle’s internal processes. |
| Transactions | Number of transactions |
Instance Efficiency Percentages (Target 100%)
This section contains ratios related to buffer access, parsing, library cache usage, redo allocation, and sort operations. These values can help identify changes in database behavior, especially after application releases or configuration changes. However, do not evaluate these ratios using fixed thresholds alone. Compare them with a normal baseline and interpret them together with DB Time, wait events, workload characteristics, and SQL statistics.
Reference: AWR Reports – Part I: 10 Most Important Bits
| Main Item | Explanation | Notes |
|---|---|---|
| Buffer Nowait % | Percentage of buffer accesses that did not require waiting for a buffer. | Use this as a trend indicator rather than a standalone pass/fail threshold. |
| Buffer Hit % | Percentage of logical reads satisfied without requiring a physical read. | A high value does not guarantee good performance. Evaluate it together with logical reads, DB CPU, and SQL statistics. |
| Library Hit % | Indicates how effectively objects and SQL-related information are being reused in the library cache. | Investigate significant changes together with shared pool activity and parsing statistics. |
| Execute to Parse % | Indicates how often SQL execution occurs without requiring a new parse. | A low value can indicate excessive parsing relative to executions. |
| Parse CPU to Parse Elapsed % | Shows the proportion of parse elapsed time spent on CPU. | A low value can indicate that parsing is spending time waiting for resources. |
| Redo NoWait % | Percentage of redo allocation requests that did not need to wait. | Investigate changes together with redo-related wait events. |
| In-memory Sort % | Percentage of sort operations performed in memory rather than on disk. | Interpret this together with temporary tablespace usage and workload characteristics. |
| Soft Parse % | Percentage of parses that were soft parses rather than hard parses. | A sudden change can indicate different SQL reuse or cursor behavior, but avoid using a single fixed threshold. |
For reference, the flow of SQL parsing process (hard parse, soft parse) is illustrated below.
Top 10 Foreground Events
This is information on events that occurred in the database, sorted in descending order of Total Wait Time. When a performance problem occurs, it may be possible to identify the event that is causing a bottleneck, so we recommend checking this information frequently.
We will list the top 10 events that are frequently seen, so please use it as a reference.
| Main Item | Explanation | Notes |
|---|---|---|
| DB CPU | CPU time consumed by foreground database sessions. DB CPU is not a wait event; it represents time actively spent executing on CPU. | If DB CPU accounts for a large portion of DB Time, identify CPU-intensive SQL and check host CPU utilization. |
| db file sequential read | A single-block read wait, commonly associated with index access and table access by ROWID. | If it accounts for a large portion of DB Time, investigate SQL efficiency, execution plans, storage latency, and excessive single-block reads. |
| db file scattered read | A multiblock read wait, commonly associated with full table scans and other multiblock I/O. | If it is significant, investigate SQL statements performing large scans, execution plans, segment size, and storage performance. |
| log file sync | A wait event that occurs when the LGWR process writes redo to the storage after a commit is requested. | This is mainly caused by excessive commits being issued or LGWR I/O processing. It can also be caused by frequent redo log file switches. |
| enq: SV – contention | A wait event to obtain the requested sequence number | |
| gc current/cr block busy | In a RAC configuration, this is a wait event that occurs when contention occurs during the transfer of a current block or a cr block. | The contention could occur because the requested block is in use by a local process or because redo logging has been delayed. |
Reference: https://www.dbanet.co.za/Summary/awr_top10_foreground_events.html
Analyzing Top SQL
After checking the Top 10 Foreground Events, the next step is to identify which SQL statements are consuming the most database resources.
The AWR report provides several SQL Statistics sections that rank SQL statements by different metrics.
The following sections are particularly useful for performance analysis:
| Section | What to Check |
|---|---|
| SQL ordered by Elapsed Time | SQL statements that consumed the most total execution time |
| SQL ordered by CPU Time | SQL statements that consumed the most CPU time |
| SQL ordered by Gets | SQL statements that performed the most logical reads |
| SQL ordered by Reads | SQL statements that performed the most physical reads |
| SQL ordered by Executions | SQL statements that were executed most frequently |
Which section you should check depends on the performance problem.
For example, if DB CPU accounts for a large percentage of DB Time, check SQL ordered by CPU Time to identify CPU-intensive SQL statements.
If disk I/O wait events such as db file sequential read or db file scattered read are high, check SQL ordered by Reads to identify SQL statements generating large amounts of physical I/O.
SQL ordered by Elapsed Time is also a useful starting point because it shows which SQL statements consumed the most total execution time during the AWR period.
Once you identify a suspicious SQL statement, check its SQL ID and investigate the execution plan, execution count, elapsed time per execution, CPU time, and I/O statistics in more detail.
It is also useful to compare these values with an AWR report from a normal period. A significant change in the resource usage of a particular SQL statement may help identify the cause of a performance problem.
Summary
In this article, we covered the basics of reading and analyzing an Oracle AWR report and showed how to use it to narrow down performance bottlenecks.
AWR provides detailed information about database performance, including workload, CPU usage, wait events, and SQL statistics.
When analyzing a performance problem, start by checking DB Time and the Load Profile to understand the overall workload. Next, review the Top Foreground Events to determine whether database time is being consumed by CPU or specific wait events. Then analyze the Top SQL to identify SQL statements that are consuming significant CPU, elapsed time, logical I/O, or physical I/O.
It is also important to compare the AWR report with a normal period. Changes in DB Time, wait events, or SQL resource usage can provide important clues when identifying the cause of a performance problem.
By following this process—DB Time → Load Profile → Top Foreground Events → Top SQL → Execution Plan → comparison with a normal baseline—you can use AWR to efficiently narrow down Oracle Database performance bottlenecks and determine what should be investigated next.
Reference
Performance Tuning Basics 15 : AWR Report Analysis – Expert Oracle
English 
Japanese
コメント