:

Automatic Workload Repository (AWR)

Oracle AWR Report

In this post I would like to go over a really useful tool for anyone who is looking to performance tune their database, which is the Automatic Workload Repository (AWR). I’m going to cover:

  • What the AWR is
  • Why you would use it
  • When you would use it
  • How to generate AWR reports
  • How to read AWR reports

Before I start I’d like to mention the predecessor to the AWR, which is STATSPACK. The AWR came about in Oracle 10g and before that there was the STATSPACK reporting schema which contained many similar functions to the AWR but it was much more basic and did not have nearly as many functions as the AWR has, especially in terms of integration with other functionality in the database. So, let’s make a start at understanding a little more about the AWR.

Note: If you are familiar with the configuration and runing of an AWR report, skip down the page to the section on “Why and When Should I use AWR reports?”

What is the AWR?

The Oracle database has many counters and views which allow you, the DBA, to analyse how it is performing. The V$ views, also known as dynamic performance views, gather statistics about the state of the database as it works but are lost after a restart and sometimes are lost after a period of time, such as the statistics about the active session history (ASH). The idea behind the AWR is to take a snapshot at a point in time of these statistics so that they can be stored and used for comparisons in the future.

The AWR is all about monitoring performance so that you can spot any performance issues before they become too much of a problem, or also quite likely is that there is a bad performance problem which needs to be investigated immediately! Fortunately the reports which you can run allow you to look back historically and compare a poorly performing time with a well performing one. The AWR maintains statistics about the following components:

  • System
  • Sessions
  • Individual SQL statements
  • Segments
  • Services

The database collects statistics for certain areas of the system and then writes them out to the SYSAUX tablespace at a specified time which you can control, also known as the snapshot interval period. The default is every hour. The snapshots are kept for a specified period of time which you can also control, all of this we will cover later on in the post.

Many statistics are collected which help you to identify bottlenecks and poorly performing areas within your system. For example, the report shows you a list of the most resource intensive SQL statements on a CPU, I/O and Elapsed Time, among others. This allows you to target these statements to reduce the times spent processing data and alleviate contention for resource, be it CPU or disk.

AWR report SQL Elapsed Time

The AWR report shows the top SQL elapsed time, among other events

It’s also possible to save certain pairs or ranges of snapshots which you can define as AWR baselines. This allows you to keep them even when they fall outside of the snapshot retention policy. The idea behind the AWR baselines is that you keep known good and bad performing periods of time so that you can use them for comparison later on as needed for analysis.

How to Configure AWR

The good news is that it is enabled by default, providing you have one important initialisation parameter set. The parameter is called STATISTICS_LEVEL and it needs to be set to TYPICAL or ALL for the AWR to function automatically. A setting of BASIC is also possible but this disables many of the automatic capturing features and you can only run it manually. This is not recommended so just leave it to TYPICAL if you are not sure. You should check out the Oracle documentation about this parameter to decide which is the most suitable setting for your system.

statistics level AWR

Statistics_level parameter used for the AWR

Once you have configured your database to have a STATISTICS_LEVEL to TYPICAL or ALL the snapshots should start to be created automatically every hour, by default. Next I’d like to show you how to change some of the parameters to configure the settings used for the AWR.

The DBMS_WORKLOAD_REPOSITORY Package

The first task is to see what your current settings for the AWR are set to which you can do quite easily using the SQL below.

   SELECT *
   FROM DBA_HIST_WR_CONTROL;
Oracle AWR Configuration

Values from dba_hist_wr_control view

The settings which you can control are:

Retention – How long you want to keep the snapshots, specified in minutes. Check out my article on the size of the SYSAUX tablespace for some considerations
– 0 will retain forever
– NULL will result in the parameter not being changed
– if you get the error ORA-13541: system moving window baseline size greater than retention, please have a read through that link which details how to fix the issue

Interval – How frequently snapshots will be taken, in minutes
– Minimum time is 10 mins, maximum time is 1 year
– If 0 is specified automatic and manual snapshots will be deleted
– If NULL is specified then the current value will persist

topnsql – The number of SQL statements captured from the cache and added to the repository
– You can specify the number of SQL statements, from 30 to 50,000
– You can also put MAXIMUM and DEFAULT, maximum being all SQL statements in the cache
– DEFAULT is 30 when STATISTICS_LEVEL is set to TYPICAL and 100 when STATISTICS_LEVEL is set to ALL

dbid – the database identifier for which to modify the snapshot settings, NULL will use the local DB ID

To modify the snapshot settings you use the MODIFY_SNAPSHOT_SETTINGS procedure within the DBMS_WORKLOAD_REPOSITORY package. Here is an example of how to modify the snapshot settings:

EXEC DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS(interval  =>  30, retention =>  44640);

This statement will set up the AWR snapshots to run automatically at 30 minute intervals and store the information for 31 days. As the data becomes older than 31 days it is automatically deleted from the repository.

NOTE: The more historical data that you want to store the more space it is going to use. As such, you should keep an eye on the SYSAUX tablespace to ensure that it doesn’t grow too large. You can always manually delete snapshots or change the retention period, snapshot interval and how many SQL statements are kept at any time. The docs state that a system with 10 concurrent active sessions storing data for 8 days will consume around 200-300MB.

Creating an AWR Snapshot

One of the most common tasks you will do is to create a new snapshot. Usually this is before and after you have completed some kind of load testing on a development environment. It’s very easy to create a snapshot, here is how you do it:

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();

The snapshot taken will be using the default statistics level which is TYPICAL. You can also specify ALL if you want a more in-depth snapshot, containing more SQL statements than that of the TYPICAL one.

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT('ALL');

Configure Top N SQL on AWR Report

As standard in your AWR report you will display the top 10 SQL statements for each category, such as top 10 SQL statements by elapsed time, CPU usage, etc. It’s pretty common to want to see more queries returned on the AWR report, however. To do this you just need to change one of the report threshold setting options, like so:

exec dbms_workload_repository.awr_set_report_thresholds(top_n_sql=>30);

Change the Top SQL on AWR report

Changing the Top N SQL

The data to display the top 100 SQL statements will already be stored in the workload repository if you have the STATISTICS_LEVEL set to ALL, or the top 30 if it is set to TYPICAL. Then, next time you create an AWR report you will see the top 30 SQL statements instead of 10 in each section.

How to Create a baseline in the AWR

This next stage is something you will probably do less often. Maybe only after a new release has gone into your database and you want to get a new baseline during your peak load hours when you know things are running well, for example.

You can see what AWR baselines already exist by querying the DBA_HIST_BASELINE view. To create a new AWR baseline use the following syntax:

EXEC DBMS_WORKLOAD_REPOSITORY.CREATE_BASELINE(start_snap_id =>100,end_snap_id =>102,baseline_name =>'oltp_release1_bl');

This will create a baseline using snap ids 100 and 102 and call is oltp_release1_bl. There are 2 additional parameters which you can add to this which are the dbid, which defaults to the local db id, and expiration, which defaults to never so it will always be kept.

You may decide to drop a baseline because it is no longer needed and that can easily be done using the DROP_BASELINE procedure as follows:

EXEC DBMS_WORKLOAD_REPOSITORY.DROP_BASELINE(baseline_name =>'oltp_release1_bl',cascade =>TRUE);

This drop the oltp_release1_bl baseline and its associated snapshots.

Although there are many other procedures which are part of the DBMS_WORKLOAD_REPOSITORY package, the ones which I have covered above are most commonly used and the remainder can be found in the Oracle documentation should you need to use them at any stage.

Now that you are familiar with the basic commands we can have a look at why and when you would use the AWR reports.

Why and When Should I use AWR reports?

There are generally three different occasions on which you would want to check an AWR report, those being:

  1. In response to a performance problem
  2. To proactively monitor performance
  3. Prior to making a change

Now let’s cover each of the reasons in a little more detail.

1. In response to a Performance Problem

This is probably the most common reason for running an AWR report, yet the worst! The most common because more often than not it will be a performance problem on your production environment which prompts you to investigate. It’s the worst because you need to deal with it quickly and because it means that whatever is causing it was not picked up anywhere else before it got through to production, or something has gone wrong. It’s in these cases where you really need to know how to read the AWR report and spot what is going wrong. We’ll cover that in the next section on how to read the AWR report.

2. To Proactively Monitor Performance

This is what you should all be doing on a regular basis. It involves getting to know your database, what wait events are normal and acceptable to you during peak and non-peak load times and understanding what to look out for, such as changes from the norm. This is a prime example of when you should be making good use of your AWR baseline. Perhaps weekly or twice monthly you should be comparing the load to your baseline and checking if there are any differences.

It’s important to remember that there might be different types of load on your system at different times in the day. For example, one of our systems has a normal OLTP load during the day (6am – 10pm) and then some overnight processing jobs which run in the early hours of the morning. These times should be looked at separately and treated as such for your performance tuning efforts. You should also ensure that you have baselines for both of the different workloads.

3. Prior to Making a Change

Before you roll any change, such as an initialisation parameter change, to your production environment you need to have carried out sufficient testing. One of the tests I usually perform includes looking through an AWR report from DEV and QA. It can be very difficult to ensure that there are no performance problems when looking at a DEV AWR report because it’s hard to control what is being run on the environment but it is possible to check that there are no obvious signs of a change for the worse, especially if you have a baseline from which to work.

A good idea is to get some load run through the system out of hours one evening before you make the change, and make sure that you have AWR snapshots for this time period. Then, make the change and run the same load at the same time so as to avoid contention with other processes. You can then compare the periods and see how it looks.

How to Generate an AWR Report

The general idea when generating an AWR report is that you take one snapshot (a point in time) and compare that to another snapshot (another point in time) and you look at the differences (the delta) between the two. By looking at the differences you are able to see which areas of the system have the most waits or processing time associated with them and then you know where your performance issue is. A great feature of the AWR is that you can run a comparison report (AWRDDRPT.sql, explained later on) which can compare two periods against one another. Let me explain. In a normal AWR report using the AWRRPT.sql script you specify a begin time and an end time and you get to see the results. In the comparison report (AWRDDRPT.sql) you choose two sets of begin and end times and the report will show you how they compare. It’s an amazingly useful utility.

Here are some other useful scripts with a short explanation as to what information they will show you. The best way to learn about them is to run them and become familiar with the results so that you know when to use them appropriately.

  • @$ORACLE_HOME/rdbms/admin/awrgrpt.sql – RAC normal AWR report, so you can specify a particular database instance to run the report against
  • @$ORACLE_HOME/rdbms/admin/awrrpti.sql – This script is used to specify which database instance you want to use, particularly useful when you have imported AWR data into a different database
  • @$ORACLE_HOME/rdbms/admin/awrsqrpt.sql – This is a nice script to get information out about a specific SQL statement

AWR and the Enterprise Manager

If you are using the Oracle Enterprise Manager (EM) you can use that to create the AWR report quite easily. Creating a report using the EM is very intuitive so I won’t cover it in too much detail here. What I would like to go over is how to create the report manually, as you may not always have the EM available to use but you will always have a command line available to run the AWR report manually. So, I’ll cover the different scripts you may want to use and what they are used for.

When looking at a problem you may get a question from a user similar to the following “The database performance was slow between 9am and 9:05am. Do you know if there were any problems at that time?” If your default snapshot settings were set to 1 hour this takes up only a small amount of that time period so any issue may be averaged out when you looked through the AWR reports. If this is the case then you would be better off looking at the Active Session History Reports. You can drill down in these reports using various dimensions, such as sql id, module, session and action.

You would use the script @$ORACLE_HOME/rdbms/admin/ashrpt.sql to generate an active session history report.

How to Read AWR Reports

The basic idea when reading an AWR report is to think about the system as a whole and look to see how it is running. You should look over the information about the sizes of the caches, how many transactions are going through the system, how much parsing there is, when the snapshots were taken, etc. This will give you some background information and help to give context to the situation. For example, you may see a really long running, resource consuming query on its own in the SQL statements section but if the time was during an overnight window when there were batch jobs running this might not be a problem. If it was in the middle of the day on an OLTP system then it would be more of a cause for concern. Every system is different and therefore there is not always a black and white argument as to what the best process should be so bear that in mind when reading through the report.

Having said that, here is how I would read most AWR reports.

Ask Some Questions

  1. When you first look at an AWR report you should double check the configuration of the database and ask questions such as:
  2. Over what time period was this report taken, and is this a peak load or overnight batch job window
  3. How large are the caches at the beginning and ends of the report
  4. How much activity is there, such as REDO generation, physical reads, block changes, sorts, etc
  5. How does this period compare to a time of known good performance

Summary and Load Profile

AWR Report Summary

summary AWR report information

This first step is where the summary information at the top of the report comes in, as shown above. This information helps you build up a feel for the database.

Next, using the “Load Profile” section of the report will give you some more information about the activity within the database.

AWR Load Profile

The Load Profile section of the AWR is very revealing

I rarely look at the section titled “Instance Efficiency Percentages (Target 100%)”, at least not in isolation. I have a quick browse.

Top 5 Timed Events

This section is really the first part that gives you a good idea about where the time in your database is being spent. It’s a high level overview of what the database is doing the most of. This is the key section for you to look at and decide which areas you should look at next.

Top 5 Timed Events AWR

The best place to start is the top 5 timed events seciton on the AWR

For example, if you see a lot of waits on disk based activity, such as db file sequential read and db file scattered read, look at what the average response times are. If they are high, above 10ms, then maybe your disks are overloaded. Or, maybe the queries which are running can be made more efficient. Remember that something always has to be the highest wait event, so maybe the database is running well.

There are usually many reasons why a particular wait event could be the highest, so there is no definite answer like “you see X, so do Y”. It just doesn’t work like that. If it did there would be automatic rules in place to correct the events as they happened.

Performance tuning within the Oracle database is an on-going, iterative process and the same applies to tuning with the AWR reports. You should first start by identifying the worst performing areas of the system which will give you the biggest performance gains if you fix them. So, you may find that on your first look at the AWR report there is a query which has a large number of disk reads which can be done in a much more efficient way. You should deal with that query first and then see how the report looks after that. Then see what looks like the next biggest win and so on, repeating the process.

Note: DB Time is a statistic which crops up quite often when looking at performance of the database. This is a relatively new approach which aims to give you a value for how much work the database is doing. In basic terms the DB time is the sum of all active database sessions. Let’s say that you were looking at a 1 hour period and during that period there were 4 sessions which were using CPU for 100% of that time. The DB time would be 4 hours, because you have 4 sessions * 1 hour. Your overall aim of performance tuning is to reduce the DB time as much as possible, and therefore reduce the amount of time the sessions spend waiting/working in the database.

I’ve also written another article on when you see the CPU event in the top 5 timed events on the system so that’s worth a read for more details. And, I would highly recommend checking out this little presentation I found which I thought was rather neat on reading the AWR report.

Migrating AWR Data

You may want to move some AWR data from one database to another one so that you are able to compare information. This is quite a simple process, which I will outline here and provide you with an example of when I used this method and came across an error.

  1. As the SYS user run the @$ORACLE_HOME/rdbms/admin/awrextr.sql script and answer all of the prompts
    You will need to have a directory created in the database to write out to by data pump
  2. As the SYS user run the @$ORACLE_HOME/rdbms/admin/awrload.sql script in the database into which you want to import the data
    The file must be in the directory that you specify, either the same one as the export or copy it to a new one and create a new directory
    I always use the AWR_STAGE schema as is the default for a temporary store for the AWR data, and it is dropped automatically afterwards

That’s it! Pretty simple really, but you may come across the ORA-20105: unable to move AWR data to SYS error message which I have written about in the previous post and is easy to remedy.

Hopefully this has helped shed some light on the subject of the Automatic Workload Repository.

Like it, share it...

Category: Articles


Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *