My Signature ArticleTips:
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
$ORACLE_HOME/rdbms/admin
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
4 comments:
Nice explanation!! I have one AWR report. Can you please help me in identifying the potential areas of tuning?
newlotus007@gmail.com
Hi,
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
Thank you Nice post and i posted similar topic in my blog :
http://chandu208.blogspot.com/2011/04/steps-to-generate-awr-report.html
need one to have good knowledge of oracle internals. no shortcuts to learn interpreting awr rpt.
Post a Comment