How to make AWR Report through Oracle Enterprise Manager

Managing AWR with Oracle Enterprise Manager

  To manage AWR from OEM, find the administration page. Under the statistics management section you will find a link titled Automatic Workload Repository. Click on this link to go to the AWR administration screen. Here is a screen print of the administration page of OEM. AWR option is circled:


Upon clicking the link to the Advanced Workload Repository we find ourselves on the AWR management page. That is seen in the following screen shot:


This AWR page provides a summary of the current AWR settings and gives you an option to modify them by clicking on the edit link.
This page also gives us the ability to manage existing snapshots, or we can manage preserved snapshots (also known as baselines). In the next sections we will first look 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, as shown in this screen shot taken after the show SQL button was pushed:


Note in this example we were increasing the retention of the snapshot data to 10 days.

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. Here is a screen print of the snapshot details page (note this is just a partial page, since there are a number of details that you could scroll down to see):

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.
Baseline snapshots can be used in a number of ways. If you look at the actions pulldown list on the top right of the Preserved Snapshot screen you will find options such as:

• Creation of a SQL Tuning set
   
• View the AWR report
   
• Run ADDM against the preserved snapshot
   
• Delete the preserved snapshot
   
• Compare two preserved snapshots

AWR provides data to be used for monitoring and tuning of and Oracle database. One of the tools that uses this AWR data for automated tuning and monitoring is ADDM.