Search This Blog

Sunday, October 21, 2007

AWR Report/Snapshot

My Signature Article

Oracle Certification - Tips

DBA Preparation - Tips

Practical DBA - Tips

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


Example Of AWR report

AWR report Example

What is default interval period between two awr report ?

AWR report generate automatically for database every 1 hr interval period.
But Recommanded is 15 Minutes is enough in two snapshot for better performance bottleneck.

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 REPOSITORY

How to Interpreting with AWR report ?

Below is Main Section in AWR report for concern.

Load Profile

In this section we know about Logical Read, Physical Read, Soft Parse, Hard Parse

Instance Efficiency Percentages

Many Oracle Guru's is not recommended to trust on Instance percentage ratio. becuase suppose if your instance percentage show BUFFER CAHCE HIT RATIO is 100% it is not sign for your database is very fast and running smootly.

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.

Oracle Wait Event

SQL Statistics

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

Advisory Statistics

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

Visit following link for reference interpreting awr report

Reference I


Raj said...

Nice explanation!! I have one AWR report. Can you please help me in identifying the potential areas of tuning?

Anonymous said...

Sir thanks for detailed explanation . sir but iam fresher and when iam trying to learn awr report i couldn't understand it anything u have highlighted the sections which we should go can u pls exp them also

chandu reddy said...

Thank you Nice post and i posted similar topic in my blog :

Anonymous said...

need one to have good knowledge of oracle internals. no shortcuts to learn interpreting awr rpt.