CA Tuesday Tip: DB Performance Reports - The AWR report in 5 Minutes

Discussion created by Shawn_Moore Employee on Jan 19, 2011
Latest reply on Jan 24, 2011 by Chris_Hackett
CA Clarity Tuesday Tip by Shawn Moore, Sr. Principal Support Engineer for 1/18/2011

Oracle AWR Part I - Quick Scan for Problems

** Disclaimer ** Always consult your DBA before making changes to or requesting changes for your database server.

In today's installment, I decided to go through a "quick scan" of the AWR report. If I had 5 minutes to look at an AWR report, what would I look for? This of course depends on the situation, but let's say we have a general performance issue -- Users report that Clarity is slow in certain areas.

Note: We typically look at a SQL Trace 1st in these situations, however, for the sake of this article, let's say we are getting a general report of slowness, but no one has really nailed down the specifics. The AWR can help you in this instance.

So if I had 5 minutes, what are the things I would look at? Here are 4 areas to take a quick scan at when looking at an AWR report.

1) Take a quick peek at the top of the report to see what version of Oracle is running and how long the snapshot is for. Make sure the version of Oracle you are running is supported in the version of Clarity you are running. We prefer snapshots that are at least an hour long snapshot, unless we are capturing a specific item that we know about(i.e. a slow performing query)

2) A quick peek at the top 5 Timed Events. These tend to be more lower level items like "CPU time" or "log file parallel write". Look for things that are significantly higher than the rest (i.e. a 99% Total Call Time percentage, compared to 4 other events that are under a percent) If you see something significantly higher than the rest, bring it up to your DBA for possible interpretations.

3) This is where it gets good. The "SQL ordered by..." tables are useful in finding poorly performing queries. I tend to focus on the following because problems in these areas can be due to product defects (but not always ;) ).

a) SQL ordered by Elapsed Time
b) SQL ordered by CPU Time
c) SQL ordered by Gets
d) SQL ordered by Reads

(note: For Clarity Product issues, I usually start with CPU time and Gets. If either of these 2 have high numbers (i.e. a CPU % over 50 or so for a single query or millions to billions of buffer gets for a query), then it is worth looking into closer. (We'll go over some examples next week...stay tuned))

The time based metrics will show long running queries, but the difference is that the CPU time is the amount of actual time the DB Server is using the CPU(s) to actually process the results of the query. The elapsed time is the amount of time the query takes to run. Any wait time due to row lock contention and perhaps slow disk IO will be time that the processors aren't actually working on the query, but will increase the elapsed time. Keep in mind the Executions column as the column shows how many times the query as most of the metrics are for the sum of all executions (except the metrics that say "per Exec (s)").

The Gets and Reads metric are also very useful. If an environment has powerful hardware, you might not see the query listed under the Elapsed Time or CPU time (it performs fast), however, it could be consuming large amounts of processing power or moving many records. The Gets (buffer gets) show how much data is being accessed from memory. The Reads show how much data is accessed from the physical disk. If buffer gets are low and reads are high, the system might have an issue with not enough memory being allocated to it. This leads us into the next section to look at: the advisory statistics.

4) Advisory Statistics. There are several advisories that show the current settings and projected improvements if more memory is allocated to each area. The key item to understand is that a size factor of 1 represents your current configuration.

In this example. 38912 MB are allocated to the SGA resulting in 2.6 million reads. If the amount of memory is doubled in the SGA (size factor of 2) then the estimated physical reads will go down to about 1.6 million. This results in some disk access savings which is always good. Adding memory may help in this situation.

SGA Target Size (M) SGA Size Factor Est DB Time (s) Est Physical Reads

9,728 0.25 1,348,798 5,081,894
19,456 0.50 1,345,703 2,621,739
29,184 0.75 1,345,703 2,618,858
38,912 1.00 1,345,702 2,618,858
77,824 2.00 845,726 1,606,287

Hope this is interesting and useful information. Next week I'd like to focus on the top query section and provide some examples of various situations.


Shawn Moore
CA Technologies