Introduction
For engineers who operate Oracle databases, it is very important to regularly analyze performance-related information obtained from AWR and Statspack and to detect early signs of trouble. In addition, the information from AWR and Statspack is also very useful in identifying the cause of trouble when it occurs. In this article, we have summarized the basic ways to view and analyze AWR reports for beginners, so please refer to it.
What is AWR report?
AWR is an abbreviation for “Automatic Workload Repository” and is called “Automatic Workload Repository” in Japanese. It is a report that outputs execution statistics on the workload of the entire Oracle instance, such as CPU usage, memory usage, and I/O wait. It is very useful for checking whether the DB is operating normally or performance is degraded, and for analyzing the cause when a problem occurs. However, please note that in order to use the AWR function, a license agreement for the Diagnostics Pack of the Enterprise Edition is required.
The mechanism for acquiring information using AWR is that the MMON (Manageability Monitor) process collects and filters various statistical information from the SGA and creates an AWR snapshot in the SYSAUX tablespace. By default, the snapshot creation interval is 60 minutes and the storage period is 8 days, but you can change the settings according to your requirements. In addition, repeated creation and deletion of AWR snapshots can cause memory fragmentation and make it impossible to reuse the space, so it is important to estimate the appropriate SYSAUX tablespace size.
How to get AWR report
We won’t go into detail here about how to obtain AWR reports, but AWR can output statistical information collected during the snapshot interval (default is 60 minutes) as a report. There are also several tools available for creating HTML format reports, so please use the appropriate one depending on your needs. I often output multiple AWR reports at once, so I often 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 have an overview of what kind of performance information is described. The AWR report is divided into three sections: Basic Information, Report Summary, and Main Report, so we will explain each of them.
To digress a little, even if you obtain an AWR report when a performance problem occurs, it is difficult to determine which values are abnormal from that alone. Obtaining a normal AWR report in advance and preparing a situation where you can compare it will lead to quicker cause investigation. Even if there are no problems, we recommend that you obtain an AWR report regularly.
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. |
| Elasped | 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.
引用：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
This is important information that summarizes information about database load. Please read it carefully as it may contain information that can help identify performance degradation.
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 there are many writes from the physical disk, performance may deteriorate. Please check that this value is not too large. |
| 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 parses include checking the syntax of the SQL statement, checking table and column definitions and permissions, and creating an execution plan. An increase in the rate of hard parses can affect performance. This varies depending on the type of CPU and number of cores, so it is difficult to generalize, but if hard parses are occurring more than 100 times per second, we recommend checking the SQL statements being executed. The number of soft parses is not directly displayed, but it can be calculated as the difference between “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 describes how efficiently the buffer cache and library cache are being used. If the cache hit rate or soft parse rate decreases, it can cause performance degradation, so please check it especially carefully when there are changes such as application releases or Oracle patch applications.
Reference: https://www.dbanet.co.za/Summary/awr_load_profile.html
| Main Item | Explanation | Notes |
|---|---|---|
| Buffer Nowait % | The percentage of processes that can access the buffer cache without waiting. | Generally, 95% or more is a normal value. |
| Buffer Hit % | Percentage of operations that hit the buffer cache | Generally, 90% or more is a normal value. |
| Library Hit% | The percentage of executable SQL that exists in the shared pool. | Generally, 95% or more is a normal value. If it is below 95%, consider expanding the shared pool. |
| Execute to Parse % | The percentage of parsing that was reused without re-executing it | |
| Parse CPU to Parse Elapsed % | The percentage of CPU time spent parsing SQL | |
| Redo NoWait % | The percentage of operations that had enough buffers to immediately use the redo log | Generally, 90% or more is a normal value. |
| In-memory Sort % | Percentage of sort operations performed in memory | Generally, 90% or more is a normal value. |
| Soft Parse % | Soft Parse Percentage | Generally, 90% or more is a normal value. |
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 | Events that the CPU is running on. Total Wait Time(sec) = Sum of CPU operation times for each CPU core | If DB CPU is high, check for CPU-intensive SQL or high CPU utilization. If a wait event is high, investigate the related resource, such as disk I/O or locks. Compare these values with normal periods to identify potential bottlenecks. |
| db file sequential read | Wait event that occurs when a single block read occurs | Waits occur when inefficient index scans, row chaining, or row migration cause wasted I/O. |
| db file scattered read | A wait event that occurs when a multiblock read occurs. | The cause of this wait event is the same as for db file sequential read. |
| 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.
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 where the database is spending its time, and then analyze the Top SQL to identify SQL statements consuming significant resources.
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, 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
コメント