A map to AWR report

10 Sep

 

Introduction

An average 11g AWR report spans 40 screens broken into approximately 50 sections. That’s a lot, especially for someone who’s not very well familiar with AWR reports, so I decided to make a some sort of a map.  The purpose is to show that this report has a certain structure (which may not be obvious at first sight), and knowing this structure can help extract the most essential information in the fastest way possible.

Types of sections

For simplicity, I break AWR report sections into following categories:

1) basic (key information)

2) detalization (provides details on a specific topic briefly covered in the basic section, such as latches, enqueues etc.)

3) advisories (helps find optimal values of parameters)

4) advanced (stuff that is not generally needed, but can be useful on certain occasions — basically, everything not covered in 1-3).

Basic sections

Basic sections contain information that is most essential to understanding what the database is going through performance-wise. In most cases, they need to be read and analyzed in their entirety.

Here is a list:

1) Header (information about the instance, the host, beginning and end snapshots found on the top of the report)

2) Load profile

3) Waits (“top 5 timed foreground events”)

4) instance CPU

Detalization sections

By far the most important of these is “top SQL ordered by executions/elapsed time/CPU time/reads/gets/parse calls/shared memory/versions” which can be considered as a detalization of information in “load profile” and “top timed events” sections. For example, if  the load profile is showing unusually high number of executions (e.g. much higher than the number of user calls), SQL ordered by executions will tell which SQL exactly is responsible for that. If top timed events is showing high disk I/O, then SQL ordered by reads may give some answers, etc.

Another useful detalization section is “Background Wait Events”. If one of the top foreground events suggests a problem with a background process (e.g. log buffer space waits indicate a problem with LGWR) then it makes sense to study background waits that may be relevant.

Other detalization sections:

  • event histograms (detailed distribution by time for timed events)
  • latch activity (details for latch-related waits)
  • segment stats (details for I/O related waits) etc.

Advanced sections

These include sections that are rarely needed: in case of special configuration (shared server sections) or special options (java pool) etc.

Advisories

These sections are very different from everything else on the AWR report — they don’t tell about any existing or potential problems, rather, they tell how certain statistics would change if certain parameters (mostly sizes of various memory pool) are changed either way. Nowadays undersized memory pools are not as common as they used to be in 9i and earlier, so these sections are not needed very often. Go there only if you have strong reasons to believe that changing these parameters is necessary to resolve an existing problem.

Navigating from section to section

Generally, it’s advisable to read the report in its natural order (from top down):

1) header (RAC or standalone, duration of the snapshot, Oracle version, platform, number of CPUs memory) — just read it to understand what you’re dealing with. Obviously, if you’re looking at an AWR of a familiar database then you won’t need it.

2) load profile (average active sessions, DB CPU, logical and physical reads, user calls, executions, parses, hard parses, logons, rollbacks, transactions) — check if the numbers are consistent with each other and with general database profile (OLTP/DWH/mixed)

3)  events — see where the database spends most of the time. This section, combined with the load profile, essentially determines what you’ll be looking for in the rest of the report

4) if CPU time shows up in the top 5 events with a significant percentage, then make sure to look at host CPU usage to see if there is a risk of CPU starvation (see here for details)

5) go to top SQL to identify top resource consumers (pay special attention to the resource which is likely to be scarce or the major source of delays — e.g. if there are symptoms of CPU starvation, start with SQL ordered by CPU, if most of DB time falls on disk I/O wait event then go to SQL ordered by reads etc.)

6) depending on your findings so far, go to one of the detalization sections, if necessary

7) if you have to (and if you know how to interpret your findings), look for any additional information available in advanced sections

8) if in previous steps you have found hard evidence that tuning one of memory parameters would resolve a performance problem, then go to the appropriate advisor section.

Other posts on AWR

http://savvinov.com/awr/

About these ads

3 Responses to “A map to AWR report”

  1. sandy September 10, 2012 at 6:14 am #

    hi nikolay….

    great post as usual…your posts regarding AWR report are the real helpful for the newbies like me…

    i have one question regarding the resource consumption by a proc.
    How do I know that one proc is taking huge amount of resources to execute? Can I get the conclusion by seeing the AWR report?
    I have asked this question because very few times I have saw my senior DBA rejecting the procs written by the developers saying that their procs are taking huge amount of resources.

    • savvinov September 10, 2012 at 3:03 pm #

      Hi Sandy,

      thanks for your interest in my blog. I’m not quite sure what you mean by a “proc” – let me assume that you mean a stored procedure.

      AWR is not very suitable for performance testing, especially when it comes to unit testing. Autotrace, profiler or an extended SQL trace would be a much better choice.

      • Sandy September 24, 2012 at 9:26 am #

        Thanks Nikolay for answering me.
        Yes, by “proc” I meant to say stored procedures only.

        Ok..so I got it now. I need to execute the dbms_profiler package for this.

        Waiting for your next article on performance tuning and analysis.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Follow

Get every new post delivered to your Inbox.

Join 413 other followers

%d bloggers like this: