AWR Report

I think as a performance tester we should know about the basic analysis of AWR report. In this post I am going to explain about basic analysis of AWR report.

AWR: Automatic Workload Repository

Very crucial part of AWR report is SQL Statistics. Which has all sql query details executed during report time interval.  

• We can genaerte AWR report on hourly basis. It will not generate less than an hour.So we can say this is the limitation of Oracle database. Instead of having one report for long time like one report for 4hrs. it's is better to have four reports each for one hour.  Reason is that  if we take long durtion report it will give Average of all counters during that period of time. That would not be much useful.

• It's always good to have two AWR Reports, one for good time (when database was performing well), second when performance is poor. This way we can easily compare good and bad report to find out the culprit.
• After getting an AWR Report This is first and Top part of the report. In this part cross check for database and instance and and database version with the Database having performance issue.This report also show RAC=YES if it's an RAC database.

• "DB CPU(s)" per second:   Before that let's understand how DB CUP's work. Suppose you have 12 cores into the system. So, per wall clock second you have 12 seconds to work on CPU.

       So, if "DB CPU(s)" per second in this report > cores in (Host Configuration ) means env is CPU bound      and either need more CPU's or need to further check is this happening all the time or just for a fraction of time. As per my experience there are very few cases, when system is CPU bound.

Ex: machine has 12 cores and DB CPU(s) per second is 6.8. So, this is not a CPU bound case

•  Parses and Hard parses: If the ratio of hard parse to parse is high, this means Database is performing more hard parse. So, needs to look at parameters like cursor_sharing and application level for bind variables etc.

 

• Instance Efficiency percentage:
  

  In these statistics, you have to look at "% Non-Parse CPU". If this value is near 100% means most of the CPU resources are used into operations other than parsing, which is good for database health.
• Top 5 Timed Foreground Events: First of all check for wait class if wait class is  User I/O , System I/O,  Others etc this could be fine but if wait class has value "Concurrency" then there could be some serious problem. Next to look at is Time (s) which show how many times DB was waiting in this class and then Avg Wait (ms). If Time(s) are high but  Avg Wait (ms) is low then you can ignore this. If both are high or Avg Wait (ms) is high then this has to further investigate.
  
• Sql Ordered by Elapsed Time: Look for query has low executions and high Elapsed time per Exec (s) and this query could be a candidate for troubleshooting or optimizations. Ex: you can have query as maximum Elapsed time but no execution. So you have to investigate this.                                                 In Important point, if executions is 0, it doesn't means query is not executing, this might be the case when query was still executing and you took AWR report. That's why query completion was not covered in Report.
   
• This link is very useful:http://prezi.com/glqm9zemzhup/interpreting-awr-report-straight-to-the-goal/

You can get the AWR report in .txt format from oracle, and input the file in the following link. It will analyse and suggest you the bottlenecks.

http://burleson-dba.com/SP/
http://www.spviewer.com/spanlz.html