Oracle AWR Report – Automatic Workload Repository PT-17


AWR Report – Automatic Workload Repository


It will generated a performance related server reports.

10 g Feature. Prior to 10 g ‘Stats pack report’ has used.

AWR Stats Pack
Text (or) HTML Text
Any timeframe with ASH Any timeframe
Server level all resources reports


Parameters: Statistics_level = Basic/Typical/All – Based on the parameter report will be generated.

The information will gathered from following five categories.

Base Statistics Matrix Statistics SQL Statistics Active Sessions Advisory Results
Physical Reads, CPU Base+ Memory, Object memory utilization, Every past 10 minutes once Logical reads from the  query All active session with  RAM usage SQL,SQL Tuning, Memory, Segment & Undo



Advisory Types

SQL Advisor SQL Tuning Advisor Memory Advisor Segment Advisor Undo Advisor
Suggestion on Query rewriting Suggestion on  index creation Suggestion on SGA target Suggestion for shrink space compact (or) cascade Undo retention grantee need or not


By default oracle will generate the snapshot of the DB server performance every 60 minutes. It will run by the background process MMON – Memory manager.

Snapshot will be stored in SYSAUX tablespace. It has a default 7 days retention. We can change the interval and retention.


How to get the generated snapshot

Sql> select snap_id,startup_time from dba_hist_snapshot;

How to generate the snapshot manually.

Sql> Exec dbms_workload_repository.create_snapshot;

Change the default interval and retention:

Sql> Exec dbms_workload_repository.snapshot_settings (interval = > 15, retention => 2880; – Retention in minutes.


Generate the AWR report

Sql> @?/rdbms/admin/awrrpt.sql;

It will ask following parameters.

Report type: Html/text: HTML

Enter no of days: 2 – Display only last 2 days of the snap id

Snap id of first: starting id

Snap id of second: End id

Report Name: /opt/awrtest.html

It mostly 56 to 60 page report.


Generate the ADDM report

ADDM will get the data from AWR and will gather information from all the advisory and generate the report with problem and recommendations.


Sql> @?/rdbms/admin/addmrpt.sql;

Snap id of first: starting id

Snap id of second: End id

Report Name: /opt/addmtest.txt


EX: $ vi /opt/addmtest.txt

Problem 1: sql id  (96%)

Recommendation: SQL tuning

Problem 1: SGA_traget – 1500m (36%)

Recommendation: SGA_traget – 1900 M

Generate the ASH report

ASH – active session history. It  will get the data from AWR. This mostly run in the known timeframe, which occurs frequently.


Sql> @?/rdbms/admin/ashrpt.sql;


Snap time of first: 03:00:00

Snap time of second: 04:00:00

Report Name: /opt/ashtest.html

Note: AWR report is the base report for ADDM and ASH.


How to analyze the reports

There are some important parameters, which will give an overall idea of the issues.


  1. Elapsed time vs DB time

If the DB time is less than the elapsed time, the DB does not have a load or issues.

Elapsed < DB Time – DB has load and it has some problem

  1. Load Profile

Check the load profile redo size etc.

  1. Instance Efficiency Percentages (Target 100%) – RAM issue

Check the memory parameters the hit ratio should be 90% above and parse should be less than 80%.

  1. Top 5 Timed Foreground Events

Sequential read – Index problem, may be need an index or rebuild

Scattered read – Full table scan high, Need to create an index.

Direct path read – I/O high.

Once, we get the top 5, then we can take each of them and need to analyze further level.

Like, PGA – Check the sorting and Buffer pool size – Check it from size factor 1.00


  1. Main report

Check the “SQL Statistics” – Check SQL ordered by Elapsed Time & SQL ordered by CPU Time

  1. Advisory Statistics

Check the advisory of SGA and PGA current and recommended size. 1.0 is current and over some period constant value is recommended.



The ADDM will give the issue and recommendations. Just, compare with your AWR finding with ADDM.

11g onwards, Locking problem, ADDM only reports, AWR will not shows that.


Ash will has an exact issue happen on the time. Check the “Top SQL with Top Events”.

I’m currently working as a SQL server DBA in one of the top MNC. I’m passionate about SQL Server And I’m specialized in Administration and Performance tuning. I’m an active member of SQL server Central and MSDN forum. I also write articles in SQL server Central. For more Click here

Leave a Reply

Your email address will not be published. Required fields are marked *

56 + = 57