Managing AWR with Oracle Enterprise Manager Database Control

Read prior <==

While we have demonstrated how to use the dbms_workload_repository package to manage the AWR repository, Oracle also provides the ability to manage AWR from the Oracle Enterprise Manager Database Control (OEMDBC). OEMDBC provides a nice interface into the management of AWR. From the OEMDBC home page, go to the administration page as seen in this screen print:

At the bottom of the page, under "Workload," notice the entry for the Automatic Workload Repository. Selecting this option takes you to the AWR page that is seen in this screen shot:

This AWR page provides a summary of the current AWR settings and gives you an option to modify them. You can also look at details about the snapshots in the AWR and create baseline AWR snapshots (called preserved snapshot sets in OEMDBC).

Let's look first at the AWR edit settings page, and then we will look at managing snapshots.

The AWR edit settings page
Press the EDIT button on the AWR page, and you get the AWR edit settings page. The AWR edit settings page allows you to:

• Determine snapshot retention
• Determine how frequently snapshots are collected (or if collection is turned off)
• Determine the current database statistics collection level.

Here is a screen print of the AWR Edit Settings page:

Simply click on the appropriate radio button and/or change the appropriate value, and press OK to commit the change. Also note the show SQL button. This button shows you the SQL that Oracle will execute to complete your change.

Snapshot details
You can also get snapshot details by clicking on the snapshots link on the AWR page which takes you to the snapshots page seen in this screen print:

The snapshots page displays the last several snapshots in the AWR, and allows you to review older snapshots if you wish. You can click on a specific snapshot number if you want detail information on that snapshot or if you want a printable report based on the snapshot you selected.

Preserved snapshots
If you wish to create or manage preserved snapshots sets (also known as baselines), then at the AWR page, click on the preserved snapshot sets link. You will then find yourself at the preserved snapshot sets page as seen in this example:

You can click on the Create Preserved Snapshot Set button at the top right of the screen to create a new snapshot set. Oracle will then prompt you for the beginning and ending snapshots to assign to the preserved snapshot set. Once you have created snapshots, you can use the actions pull-down box to perform many actions such as creating SQL tuning sets (which we will talk about in later articles), create reports much like statspack reports from earlier versions of Oracle, and create an ADDM task that will analyze the snapshot set and produce an analysis report. You can also use the pull-down box to delete preserved snapshot sets, and you can compare two sets of snapshot pairs. Comparing snapshots allows you to determine if differences exist between a baseline snapshot and a recent set of snapshots. Using the report generated from this action, you can determine if the current system performance is diverging from the baseline performance in some way.

What's to Come — Introducing the Oracle Advisors

The AWR is the base structure for host of new advisors introduced in Oracle Database 10g to ease database administration. AWR Along with the Automatic Database Diagnostic Monitor (ADDM), which we will cover in the next article, forms the basic infrastructure for other database advisors. In coming articles we will look at these advisors in more detail.