Analyzing data using Automatic Workload Repository (AWR)

With Oracle Database 10g, Automatic Workload Repository (AWR) was introduced. It is a tool that extends the key concepts of Statspack.

In this recipe, we will create a manual snapshot, a baseline, and some reports.

Getting ready

To use AWR, the STATISTICS_LEVEL parameter of the init.ora file must be set to the value TYPICAL or ALL.

Note

With the default setting TYPICAL, all the statistics needed for self-management functionalities are collected, providing best overall performance. Using the parameter ALL the database will collect all the statistics included in the TYPICAL settings, as well as timed operating system statistics and row source execution statistics.

We can change the parameter online with the following statement without shutting down the database:

ALTER SYSTEM SET STATISTICS_LEVEL = TYPICAL;

How to do it...

The following steps demonstrate use of AWR:

1. To make a manual snapshot using AWR, we use the following stored procedure:

   EXEC DBMS_WORKLOAD_REPOSITORY.create_snapshot();
   

   With the default settings in place, AWR creates a snapshot every hour, and the data collected are stored for seven days.

2. To modify the interval or the grace period of the snapshots, we can use the modify_snapshot_settings procedure, as shown:

   EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(interval => 30);
   EXEC DBMS_WORKLOAD_REPOSITORY.modify_snapshot_settings(retention => 21600);
   

3. In AWR, we can also create a baseline to compare performances. A baseline is a set of snapshots which will be held to compare with the same kind of data in the future.

   We could have, for example, a baseline for the daily transactional work and a baseline for a batch job or a peak (quarter end). We can define a baseline indicating the start and end snapshots to be used, and we can name it:

   EXEC DBMS_WORKLOAD_REPOSITORY.create_baseline(Start_snap_id => 1, end_snap_id => 11, baseline_name => 'Friday off-peak');
   

4. To generate a report, we will use the awrrpt.sql script, located in the $ORACLE_HOME/rdbms/admin folder. The script will ask to choose the output format (text or HTML) and the number of days to use to filter the snapshots.

5. Then they will be presented the list of the snapshots, according to the parameter chosen in the previous step, and we are asked for the first and the last snapshot to be used. The last question is about the name of the file to generate the output to. The report generated is very similar to the Statspack report.

How it works...

As with Statspack, even AWR collects data and statistics from the database and stores them in tables. With AWR the concept of baseline is introduced.

The baselines can be fixed, moving window, or templates. The baseline we have defined in the previous example is fixed, because it corresponds to a specific time period in the past. The moving windows baseline corresponds to the AWR data within the entire retention period, and it's useful when used with adaptive thresholds. The baseline templates, instead, are created for a future time period, and can be single or repeating.

In the first statement of step 2, we have set the interval between snapshots to 30 minutes; in the second statement the retention period of the snapshots collected is set to 21600 minutes, which corresponds to 15 days.

The adaptive thresholds just mentioned consent to adapt the thresholds of a performance metric according to the workload of the system, eliminating false alerts. From Oracle 11g, adaptive thresholds are adjusted based on different workload patterns (for example, a system used for OLTP in daytime and for batch jobs at night) automatically recognized by the database.

We have created a report in the previous example by using the awrrpt.sql script. There are other reports available, generated by a corresponding script in the same folder; for example, awrrpti.sql is the same as awrrpt.sql, but for a specific database instance. awrsqrpt.sql generates a report for a particular SQL statement, like the script sprepsql.sql for Statspack. The corresponding script awrsqrpti.sql prepares the same report for a specific database instance.

There are also compare period reports, which allow us to compare not two snapshots but two AWR reports. If we have a database which performs well in a certain period, and we experiment a lack of performance in another period, we can elaborate two reports for the first and the latter period, and then compare the reports among them, to point out the differences and try to identify the issue.

For example, in step 4, we have created a baseline based on the snapshots with IDs from 1 to 11, and we name it "Friday off-peak".

The timespan of the two reports we are comparing isn't important, because AWR normalizes the data according to the different timeframe.

Compare period reports can be launched from Oracle Enterprise Manager or using the script awrddrpt.sql (the script awrddrpti.sql to concentrate the result on a single instance).

There's more...

We can specify the adaptive thresholds as a percentage of the maximum value observed in the moving window baseline, or as a statistical percentile, ranging from 0.95 to 0.9999—from five observations expected to exceed the value in 100 to 1 observation in 10,000.