Thursday 13 October 2011

Oracle Database AWR Report Generation::

In this section we are going to learn how to generate AWR Report on Oracle Database.

Overview:
This article is a comprehensive guide for the generation of AWR report on Oracle Database.
AWR periodically gathers system activity and workload data. Every layer of Oracle is equipped with instrumentation that gathers information on workload which will then be used to make self-managing decisions. AWR is the place where this data is stored. AWR looks periodically at the system performance (by default every 60 minutes) and stores the information found. The information is stored in the SYSAUX tablespace. This information is the basis for all self-management decisions. For example, it is possible to identify the following statistics:
  • Top 5 Waits Section
  • Instance Activity Statistics
  • Tablespace I/O Statistics
  • Operating Systems Statistics
  • Datafile I/O Statistics
  • PGA Statistics
  • Buffer Waits Statistics
Please do keep in mind that this article should not be considered a substitution of the official guide from Oracle (http://www.oracle.com). The below mentioned link can be used to download the official AWR report generation and reading guide for Oracle Database.


Objective of the Article:
By the time we finish this article, we should be able to understand the following:
(1)Basic idea of AWR Report.
(2)Automatic AWR Report generation on oracle database.
(3)Manual AWR Report generation on oracle database.


Query of Table DBA_HIST_SNAPSHOT:
 Connect to the database.
Query the Table DBA_HIST_SNAPSHOT. (SQL>DESC DBA_HIST_SNAPSHOT)  .

Query the table DBA_HIST_SNAPSHOT for SNAP_IDs for which you want to generate the AWR Report. As shown in the below screenshot.
SQL>select snap_id from dba_hist_snapshot order by snap_id ;

For the screenshot of the steps for generating AWR Report . Please go to the link:: www.sensehaze.com Link

Select the two snap_ids ( begin_snap_id , end_snap_id )
For generation of AWR Report , Execute the below mentioned sql.
SQL>@?/rdbms/admin/awrrpt.sql







No comments:

Post a Comment