Problem
You have generated an AWR report, and want to quickly interpret key portions of the report to determine if there are performance issues for your database.
Solution
The AWR report, like its predecessors Statspack and UTLBSTAT/UTLESTAT for earlier versions of Oracle, has a multitude of statistics to help you determine how your database is functioning and
performing. There are many sections of the report. The first three places on the report to gauge how your database is performing are as follows:
- DB Time
- Instance Efficiency
- Top 5 Timed Events
The first section displayed on the report shows a summary of the snapshot window for your report, as well as a brief look at the elapsed time, which represents the snapshot window, and the DB time, which represents activity on your database. If the DB time exceeds the elapsed time, it denotes a busy database. If it is a lot higher than the elapsed time, it may mean that some sessions are waiting for resources. While not specific, it can give you a quick view to see if your overall database is busy and possibly overtaxed. We can see from the following example of this section that this is a very busy database by comparing the elapsed time to the DB time:
Snap Id Snap Time Sessions Curs/Sess
Begin Snap: 18033 11-Jun-11 00:00:43 59 2.3
End Snap: 18039 11-Jun-11 06:00:22 69 2.4
Elapsed: 359.66 (mins)
DB Time: 7,713.90 (mins)
The instance efficiency section gives you a very quick view to determine if things are running adequately on your database. Generally, most percentages within this section should be above 90%. The Parse CPU to Parse Elapsd metric shows how much time the CPU is spending parsing SQL statements.
The lower this metric is, the better. In the following example, it is about 2%, which is very low. If this metric ever gets to 5%, it may mean investigation is warranted to determine why the CPU is spending
this much time simply parsing SQL statements.
Instance Efficiency Percentages (Target 100%)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Buffer Nowait %: 99.64 Redo NoWait %: 99.99
Buffer Hit %: 91.88 In-memory Sort %: 99.87
Library Hit %: 98.92 Soft Parse %: 94.30
Execute to Parse %: 93.70 Latch Hit %: 99.89
Parse CPU to Parse Elapsd %: 2.10 % Non-Parse CPU: 99.75
The third place to get a quick glance at your database performance is the Top 5 Timed Events section. This section gives you a quick look at exactly where the highest amount of resources are being consumed within your database for the snapshot period. Based on these results, it may show you that there is an inordinate amount of time spent performing full-table scans, or getting data across a network database link. The following example shows that the highest amount of resources is being used performing index scans (noted by “db file sequential read”). We can see there is significant time on “local write wait”, “enq: CF – contention”, and “free buffer waits”, which gives us a quick view of what possible contention and wait events are for our database, and gives us immediate direction for investigation and analysis.
Top 5 Timed Foreground Events Avg
~~~~~~~~~~~~~~~~~~~~~~~~~ wait % DB
Event Waits Time (s) (ms) Time Wait Class
db file sequential read 3,653,606 96,468 26 20.8 User I/O
local write wait 94,358 67,996 721 14.7 User I/O
enq: CF - contention 18,621 46,944 2521 10.1 Other
free buffer waits 3,627,548 38,249 11 8.3 Configurat
db file scattered read 2,677,267 32,400 12 7.0 User I/O
How It Works
After looking at the DB Time, Instance Efficiency, and Top 5 Timed Events sections, if you want to look in more detail at the sections of a given AWR report, refer to Recipe 7-17 in Chapter 7 for more information. Because the sheer volume of information in the AWR report is so daunting, it is strongly recommended to create baselines that represent a normal processing window. Then, AWR snapshots can be compared to the baselines, and metrics that may just look like a number on a given AWR report will stand out when a particular metric is significantly above or below a normal range.
No comments:
Post a Comment