Analyzing AWR Report

AWR can be helpfule to analyse below issue

The AWR can be used to identify

• SQLs or Modules with heavy loads or potential performance issues. These could be from other processes than the one with reported issues.
• Symptoms of those heavy loads (e.g. logical I/O (buffer gets), Physical I/O, contention, waits).
• SQLs that could be using sub-optimal execution plans (e.g. buffer gets, segment statistics).
• Numbers of executions.
• Parsing issues.
• General performance issues, e.g. system capacity (I/O, memory, CPU), system/DB configuration.
• SGA (shared pool/buffer cache) and PGA sizing advice.

Basic steps to look into the AWR

1. Need to check DB time is not much greater than Available DB time

Here Available DB Time = Number of CPU * Elapse Time 

                         = 72*60 = 4320 Min 

As here DB time is 17,435 which is much greater than DB time available so here is a issue

      2. Need to check DB time is not much greater than Available DB time

In above example number of session increased from 2k to 4k which is also showing issue  

      3. Load Profile 

This is also an important section to look upon, we can figure out if there is high physical read /write , Hard parses or high rate of sql executions. 

4.Check top 10 foreground event for any suspicious activity

This is very useful section of the report, Here in below example we can see ~ 80 % DB time is spent on 2 events 

5. Check SQL ordered by Elapsed Time

So above two query are suspicious, we can look into execution plan of above query for more details