AWR Report from OEM

AWR (Automatic Workload Repository):

What is AWR? 

AWR reports collects database performance statistics and metrics for analysis and tuning, shows the exact time spent in the database, and even saves session information.

What is default interval period between two awr report ?

By default, AWR automatically generates snapshots of the performance data once every hour and retains the statistics in the workload repository for 7 days.GUI ----> OEM (Oracle Enterprise Manager)
Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSITORYThe data in the snapshot interval is then analyzed by the Automatic Database Diagnostic Monitor (ADDM).

How to Manage & change interval period for awr report ?

There is two to modify or changes in AWR report.

1. Manually ---> through DBMS_WORKLOAD_REPOSITORY plsql package

2. GUI ----> OEM (Oracle Enterprise Manager)

Login to OEM ---> Administration Tag ----> Workload Section --->AUTOMATIC WORKLOAD REPOSIT

How to Interpreting with AWR report ?

1. Load Profile

Here we know about Logical Read, Physical Read, Soft Parse, Hard Parse

2. Instance Efficiency Percentages

If your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.

3. Top 5 Timed Events

It is very important section in AWR report.

through this we can know most five wait event is effecting database performance.

4. SQL Statistics

It this section we will know about Execution time, cpu used, logical & physical read for top most sql statement.

5.Advisory Statistics

In this section we will get advice for PGA, BUFFER CACHE, SHARED POOL, JAVA POOL size for better performance.

 

 How to generate AWR Report from Back-end

Prior Oracle 10g we use STATSPACK report to analyze instance health for performance. but now in 10g we use AWR(automatic workload repository) report to analyze Oracle Database health.


How to generate AWR report ?
 

It is very easy to generate AWR report in Oracle 10g.

We have to run just one sql script to generate AWR report.


There is TWO sql script to create AWR report.

1. awrrpt.sql
If we have only One Oracle Database then run awrrpt.sql sql script.

2. awrrpti.sql
If we have more than One Oracle Instance (Like RAC) then run awrrpti.sql script so that we can particular instance for awr report creation.

Location of AWR report sql script

$ORACLE_HOME/rdbms/admin

 

How to generate AWR Report from OEM Grid

1. Choose the Database for which, AWR report to be generated

2. Choose Performance -> Snaphosts -> Automatic Workload Repository -> Run AWR Report

3. Select Beginning Snapshot, Go to time  -> Click Go -> Click Ok




4. Select Ending Snapshot   Go to time -> Click Go -> Click Ok

Now, you will be seeing on the screen "Processing View report....."



This how you will get the AUTOMATIC WORKLOAD REPOSITORY report for your Database



If you are looking to find SQL ID in past consumed much of the resource or caused performance issues, use Performance –> Top Activity –> Historical View , chose the time frame