How to generate AWR report?

There are few scripts that can be used to generate AWR HTML or text reports.
@?/rdbms/admin/awrrpt.sql – It is an interactive script that can be used to generate HTML or text reports. The script prompts for type of report requested and prompts for number of days of snapshots to choose from so based on that it displays snap ids to choose the start and end snapshot it.


796 27 Sep 2009 17:00 1
797 27 Sep 2009 18:00 1
798 27 Sep 2009 19:00 1
799 27 Sep 2009 20:00 1
800 27 Sep 2009 21:00 1

Specify the Begin and End Snapshot Ids
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Enter value for begin_snap: 795
Begin Snapshot Id specified: 795

Enter value for end_snap: 800
End Snapshot Id specified: 800

Specify the Report Name
~~~~~~~~~~~~~~~~~~~~~~~
The default report file name is awrrpt_1_795_800.html. To use this name,
press to continue, otherwise enter an alternative.
..

Another way of generating a report is one can query dba_hist_snapshot to find the snap id for the interval you are looking for.

SQL> column begin_interval_time format a25
SQL> column end_interval_time format a25
SQL> SELECT dbid, snap_id, begin_interval_time, end_interval_time from dba_hist_snapshot order by begin_interval_time;
DBID SNAP_ID BEGIN_INTERVAL_TIME END_INTERVAL_TIME
———- ———- ————————- ————————-

1992878807 798 27-SEP-09 06.00.13.120 PM 27-SEP-09 07.00.14.116 PM
1992878807 799 27-SEP-09 07.00.14.116 PM 27-SEP-09 08.00.15.051 PM
1992878807 800 27-SEP-09 08.00.15.051 PM 27-SEP-09 09.00.16.049 PM

SQL> SELECT output FROM TABLE (dbms_workload_repository.awr_report_text(1992878807, 1, 799, 800));

OUTPUT
——————————————————————————–
WORKLOAD REPOSITORY report for

DB Name DB Id Instance Inst Num Release RAC Host
———— ———– ———— ——– ———– — ————
TEST 1992878807 TEST 1 10.2.0.1.0 NO localhost.lo

Snap Id Snap Time Sessions Curs/Sess
——— ——————- ——– ———
Begin Snap: 799 27-Sep-09 20:00:15 15 2.0
End Snap: 800 27-Sep-09 21:00:16 15 2.0
…..
user_dump_dest /u01/oradata/admin/TEST/udump
————————————————————-

OUTPUT
——————————————————————————–

End of Report

@?/rdbms/admin/awrrpti.sql – Script can be used for the specified database and instance so the script in addition will prompt for dbid and instance number.
@?/rdbms/admin/awrddrpt.sql – Script can be used to generate a combine report of 2 different snapshots for comparison of performance in one report so it will prompt for two pairs of snapshot snapshots to compare against.
@?/rdbms/admin/awrinfo.sql – Script print AWR information like space usage by AWR, subset of AWR snap ids, Automatic Database Diagnostic Monitor (ADDM), Active Session History (ASH) information.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s