Customizing the AWR

Customizing the AWR

The AWR and its collections can be customized to some extent. You can customize AWR in the following areas:

• Change the period between collections (also known as snapshots), even to the extent of switching off collection.

• Change the period of retention of data.

• Create on-demand snapshots.

• Create baselines for comparison.

With the exception of the last bullet point, you will see how these can be done in this chapter. Before you customize the AWR however, you need to understand the dependencies.

AWR Dependencies

Most of the downstream self-managing, proactive, reporting features of Oracle Database 10g depend on the AWR data collected periodically. You need to keep this in mind if you reduce or change the AWR collection and storage parameters. For example, if AWR was changed to collect snapshots once every eight hours, then it is possible that the subsequent ADDM run that analyzes this pair of snapshots might not detect peak usage and attendant issues that occurred during the first hour. On the other hand, not having enough data can affect the validity and accuracy of other components and features such as the SQL Tuning Advisor, the Undo Advisor, and Segment Advisors.

Overheads for AWR

Note that persisting of AWR information involves inserts to tables, while purging results in DELETE operations. These operations result in some overhead, albeit minimal and offloaded to the MMNL and MMON background processes. As well, they will generate redo and undo information, whose rate depends on the rate of the collection. If your databases are in ARCHIVELOG mode, you will notice a minimal but constant stream of archive-log generation even if there is no activity on the database. Under certain circumstances, it is possible that you'll encounter library cache contention due to MMON slave processes. This is due to a bug that has been corrected in later patchsets. Please refer to MetaLink Note #296765.1 for details.

Suggested AWR Settings

At the end of the day, you will need to remember that taking a snapshot, whether automatic or manual, involves a minimal amount of overhead in terms of CPU time, I/O operations, and database space. Our suggestion is to not go above the one-hour period for the AWR snapshot interval. A larger period will provide very coarse reporting, and you may miss catching peak workload within the hour. If you experience high periods of activity only during fixed office hours, you might choose to increase the frequency of the snapshots during such peak times and minimize snapshots during off hours using scheduled scripts that will automatically reduce and increase the snapshot frequency as required.

As for retention, our suggestion is to capture at least one or two periods more than the full workload cycle. For example, if you experience monthly peaks of usage, then to make effective comparisons, you will need to retain at least one month plus a few days' data. Use awrinfo.sql to estimate space requirements.

In the previous chapter, we indicated that the STATISTICS_LEVEL should be set to either TYPICAL or ALL for performance statistics to be collected. If this were set to BASIC, AWR snapshots would not be collected by default. You would however, be able to manually perform snapshots and ADDM runs.

Emergency Purging

AWR purges its repository once a day, removing all snapshots that are past their retention period and that do not belong to a preserved set by value of being associated with a baseline. AWR, however, will automatically initiate an emergency purge if it determines that it is under space pressure. This "emergency purge" will not obey retention rules and it is possible that you will lose data that is not yet expired. In this case, an alert will be raised, and a record to this effect will be logged in the alert log. We suggest you monitor the alert log and add sufficient space in the SYSAUX tablespace when you see such messages in order to ensure that required data is not being inadvertently purged.

Using the OEM to Customize the AWR

You can use the OEM as well as SQL commands to view the contents of AWR as well as to customize it. To use AWR within OEM, navigate the following path:

• Database Control Home (page) -> Administration (breadcrumb menu) -> Automatic Workload Repository (Page)

This displays the screen shown in Figure 11.2.

Figure 11.2. AWR main page (notice the current settings).

You will notice that the current number of snapshots is shown, along with the number of preserved snapshot sets (also known as baselines). Clicking the number of snapshots displays the screen shown in Figure 11.1. Click the Edit button to display the Edit Settings screen, shown in Figure 11.3.

Figure 11.3. The Edit Settings screen.

Notice the Show SQL button on this page. You can click this button to preview the SQL that will be executed to change that setting. Notice also that you can choose to retain the snapshots forever as well as to turn off the snapshotting functionality. As well, the Collection Level parameter is set to the current value of STATISTICS_LEVEL. In this particular case, it is set to ALL. This value can be changed either in the OEM or using the SQL interface by setting the initialization parameters for STATISTICS LEVEL or using the ALTER SYSTEM SET STATISTICS_LEVEL command.

Using the SQL Interface to AWR

The SQL interface can also be used to customize the AWR using the DBMS_WORKLOAD_REPOSITORY built-in PL/SQL package. This package provides the following procedures and functions to enable manual interaction with the AWR:

• The AWR_REPORT_HTML function can be used to display the AWR report in HTML, and returns a pipelined row of 150 characters.

• The AWR_REPORT_TEXT function can be used to display the AWR report in text, and returns a pipelined row of 80 characters.

• The CREATE_BASELINE function and procedure create a single named baseline for a given range of snapshots.

• The CREATE_SNAPSHOT function creates a manual snapshot immediately.

• The DROP_BASELINE procedure drops a named baseline and, optionally, the set of snapshots associated with it.

• The DROP_SNAPSHOT_RANGE procedure drops a range of snapshots.

• The MODIFY_SNAPSHOT_SETTINGS procedure modifies the snapshot settings.

Although the AWR report can be produced using the AWR_REPORT_HTML and AWR_REPORT_TEXT functions, we suggest you use awrrpt.sql or awrrpti.sql as required. In fact, these reports ultimately call these functions. You can find details of the variables required and other usage in the Oracle Database 10g PL/SQL Packages and Types Reference Manual. You will see some examples for creating and managing snapshots and look at baselining in the next section.

A common parameter in most of these procedures and functions is the database ID, or DBID, which is unique to every created database. This parameter will default to the DBID of the database on which it is running. This implies, of course, that you could import the AWR data from another instance into a master instance for reporting purposes.

Creating an AWR Snapshot

You can call the CREATE_SNAPSHOT object within the DBMS_WORKLOAD_REPOSITORY package either as a procedure or as a function to create an on-demand snapshot using the commands shown in Listing 11.4.

Listing 11.4. Generating On-Demand Snapshots Using the CREATE_SNAPSHOT Procedure and Function

 SQL> begin   2          dbms_workload_repository.create_snapshot();   3  end;   4  / PL/SQL procedure successfully completed. SQL> select dbms_workload_repository.create_snapshot('ALL') from dual; CREATE_SNAPSHOT ---------------             603 

Note that when invoked as a function, CREATE_SNAPSHOT returns the current snapshot number as its Return_Value, and this can be displayed. You can view all currently available AWR snapshots using the DBA_HIST_SNAPSHOT view.

When snapshots are taken using this procedure, the default flush level is TYPICAL. Because the flush level was not specified in this case when using the procedure, you will be able to observe in the DBA_HIST_SNAPSHOT view that the corresponding SNAP_LEVEL column shows a value of 1 for this snapshot. When invoking the snapshot using the function, we specified a flush level of ALL; hence the SNAP_LEVEL for this snapshot will be set to 2.

Effect of STATISTICS_LEVEL on SNAP_LEVEL

When the STATISTICS_LEVEL initialization parameter is changed, the snapshots taken automatically by the database default to ALL or TYPICAL depending on the current value of the initialization parameter. When a snapshot is taken manually, however, it always defaults to TYPICAL. Just note this as one more inconsistency, and move on!

Deleting an AWR Snapshot

You can use the DROP_SNAPSHOT_RANGE procedure within the DBMS_WORKLOAD_REPOSITORY package to drop one or more previously recorded snapshots. To perform this action, you will need to know the starting and ending snapshot IDs. An example is shown in Listing 11.5.

Listing 11.5. Deleting One or More Snapshots Using the DROP_SNAPSHOT_RANGE Procedure

 SQL> execute dbms_workload_repository.drop_snapshot_range - > ( low_snap_id => 705, high snap_id => 706 ); PL/SQL procedure successfully completed. 

Modifying AWR Settings

You need to use the DBMS_WORKLOAD_REPOSITORY.MODIFY_SNAPSHOT_SETTINGS procedure to modify the AWR settings. This can be best explained using the example shown in Listing 11.6.

Listing 11.6. Modifying AWR Settings Using the DBMS_WORKLOAD_REPOSITORY Package

 SQL> execute dbms_workload_repository.modify_snapshot_settings - > ( retention => 46080, interval => 15 ); PL/SQL procedure successfully completed. 

Note that both the retention period and the default snapshot interval are specified in minutes. The specified retention value of 46,080 minutes equals 32 days. The retention value can be specified to a maximum of 100 years (52,560,000 minutes) and the snapshot interval can be a maximum of 525,600 minutes or one year. Specifying a special value of 0 minutes for the retention value will retain the AWR repository forever. In this case, a value of 40,150 days (equal to 110 years) is displayed in the OEM screen. The special value of 0 minutes for the interval will switch off automatic AWR collection. In this case, the OEM Workload Repository screen in Figure 11.3 will show a value of Not Collecting against the Interval parameter.

The SYS.WRM$WR_CONTROL table lists the retention, status, and purge details along with other AWR control details.