Using AWR Reports

Generating an AWR Report

You’d like to generate an AWR report to analyze performance problems in your database.

The database automatically takes an AWR snapshot every hour, and saves the statistics in the AWR for eight days. An AWR report contains data captured between two snapshots, which need not be consecutive. Thus, an AWR report lets you examine instance performance between two points in time. You can generate an AWR report through Oracle Enterprise Manager. However, we show you how to create an AWR report using Oracle-provided scripts.
To generate an AWR report for a single instance database, execute the awrrpt.sql script or you can use Enterprise Manager or Toad Interface to generate report.

You can generate an AWR report to analyze the performance of a single SQL statement by executing the awrsqrpt.sql script.

You can run dbms_workload_repository package to generate a snapshot of AWR

SQL> exec dbms_workload_repository.create_snapshot();

Once you create the snapshot, you can run the awrrpt.sql script. Then select the previous two snapshots to generate an up-to-date AWR report.

Analyzing an AWR Report

You’ve generated an AWR report that covers a period when the database was exhibiting performance problems. You want to analyze the report.

An AWR report summarizes its performance-related statistics under various sections. The following is a quick summary of the most important sections in an AWR report.

Session Information
You can find out the number of sessions from the section at the very top of the AWR report, as shown here:

Snap Id Snap Time Sessions Curs/Sess
--------- ------------------- -------- ---------
Begin Snap: 1878 17-Apr-11 07:47:33 38 1.7
End Snap: 1879 17-Apr-11 09:00:48 34 3.7
Elapsed: 73.25 (mins)
DB Time: 33.87 (mins)
Be sure to check the Begin Snap and End Snap times, to confirm that the period encompasses the time when the performance problem occurred. If you notice a very high number of sessions, you can investigate if shadow processes are being created—for example, if the number of sessions goes up by 200 between the Begin Snap and End Snap times when you expect the number of sessions to be the same at both times, the most likely cause is an application startup issue, which is spawning all those sessions.

Load Profile
The load profile section shows the per-second and per-transaction statistics for various indicators of database load such as hard parses and the number of transactions.
The Load Profile section is one of the most important parts of an AWR report. Of particular significance are the physical I/O rates and hard parses. In an efficiently performing database, you should see mostly soft parses and very few hard parses. A high hard parse rate usually is a result of not using bind variables. If you see a high per second value for logons, it usually means that your applications aren’t using persistent connections. A high number of logons or an unusually high number of transactions tells you something unusual is happening in your database. However, the only way you’ll know the numbers are unusual is if you regularly check the AWR reports and know what the various statistics look like in a normally functioning database, at various times of the day!

Instance Efficiency Percentages
The instance efficiency section shows several hit ratios as well as the “execute to parse” and “latch hit” percentages.
The execute to parse ratio should be very high in a well-running instance. A low value for the % SQL with exec>1 statistic means that the database is not re-using shared SQL statements, usually because the SQL is not using bind variables.

Top 5 Foreground Events
The Top 5 Timed Foreground Events section shows the events that were responsible for the most waits during the time spanned by the AWR report.

The Top 5 Timed Foreground Events section is where you can usually spot the problem, by showing you why the sessions are “waiting.” The Top 5 Events information shows the total waits for all sessions, but usually one or two sessions are responsible for most of the waits. Make sure to analyze the total waits and average waits (ms) separately, in order to determine if the waits are significant. Merely looking at the total number of waits or the total wait time for a wait event could give you a misleading idea about its importance. You must pay close attention to the average wait times for an event as well. In a nicely performing database, you should see CPU and I/O as the top wait events, as is the case here. If any wait events from the concurrent wait class such as latches show up at the top, investigate those waits further. For example, if you see events such as enq: TX - row lock contention, gc_buffer_busy (RAC), or latch free, it usually indicates contention in the database. If you see an average wait of more than 2 ms for the log file sync event, investigate the wait event further (Chapter 5 shows how to analyze various wait events). If you see a high amount of waits due to the db file sequential read or the db file scattered read wait events, there are heavy indexed reads (this is normal) or full table scans going on. You can find out the SQL statement and the tables involved in these read events in the AWR report.

Time Model Statistics
Time model statistics give you an idea about how the database has spent its time, including the time it spent on executing SQL statements as against parsing statements. If parsing time is very high, or if hard parsing is significant, you must investigate further.

Top SQL Statements
This section of the AWR report lets you quickly identify the most expensive SQL statements.You can generate an explain plan for the expensive SQL statements using the SQL ID from this part of the report.

PGA Histogram
The PGA Aggregate Target Histogram shows how well the database is executing the sort and hash
operations—for example:

In this example, the database is performing all sorts and hashes optimally, in the PGA. If you see a high number of one-pass executions and even a few large multi-pass executions, that’s an indication that the PGA is too small and you should consider increasing it.

How It Works
Analyzing an AWR report should be your first step when troubleshooting database performance issues such as a slow-running query. An AWR report lets you quickly find out things such as the number of connections, transactions per second, cache-hit rates, wait event information, and the SQL statements that are using the most CPU and I/O. It shows you which of your SQL statements are using the most resources, and which wait events are slowing down the database. Most importantly, probably, the report tells you if the database performance is unusually different from its typical performance during a given time of the day (or night). The AWR report sections summarized in the “Solution” section are only a small part of the AWR report. Here are some other key sections of the report that you must review when troubleshooting performance issues:

• Foreground Wait Events
• SQL Ordered by Gets
• SQL Ordered by Reads
• SQL Ordered by Physical Reads
• Instance Activity Stats
• Log Switches
• Enqueue Activity
• Reads by Tablespace, Datafile, and SQL Statement
• Segments by Table Scans
• Segments by Row Lock Waits
• Undo Segment Summary

Depending on the nature of the performance issue you’re investigating, several of these sections in the report may turn out to be useful. In addition to the performance and wait statistics, the AWR report also offers advisories for both the PGA and the SGA. The AWR report is truly your best friend when you are troubleshooting just about any database performance issue. In a matter of minutes, you can usually find the underlying cause of the issue and figure out a potential fix. AWR does most of the work for you— you just need to know what to look for!