1. 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.
2. 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.
3. 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.
4. 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.
4.1 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 | The total CPU time and wait event time of the Oracle server process during the AWR report acquisition period. If the number of CPU cores is 2 or more, DB Time is added for each CPU, so Elapsed x CPUs is the maximum load possible time. However, information on background processes is not included. Calculation formula: DB Time = DB CPU + non idle wait time | Check that DB Time is not extremely large compared to normal times. |
4.2 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 | Number of blocks successfully written | If the data remains in the SGA buffer cache, it will be read to reduce reads from the physical disk. |
| Physical read | Number of blocks read directly from the physical disk | Please check as performance may deteriorate if there are many reads from the physical disk. |
| Physical writes | Number of blocks written directly from the physical disk | 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 | Explantion | 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 at the top, it is likely working without any problems, but if an event other than DB CPU is at the top, there may be a bottleneck, so please check. |
| 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
5. Conclusion
This concludes the explanation of Oracle AWR reports. Although we have provided general information, the resource usage and the contents of the Top 10 events will differ depending on the characteristics of each system, so the points you should pay attention to will also change. Please be sure to regularly obtain information and check whether the system is operating stably.
6. Reference
Performance Tuning Basics 15 : AWR Report Analysis – Expert Oracle
English 
Japanese
コメント