Forum Stats

  • 3,852,723 Users
  • 2,264,130 Discussions


Understanding AWR report - Newbie

1012766 Member Posts: 2
edited Jun 4, 2013 5:46PM in General Database Discussions
Hello all,

I am a newbie to oracle DBA and need help in understanding AWR report. I have a historical AWR and I want to know how to figure out how the DB was doing. What are the parameters in the report I need to look into in the AWR report to make this determination.

Please help


  • Welcome to the forums.
    It is appreciated if you try to use online resources always.
    I just Googled for 'understanding AWR' on your behalf and came up with

    Sybrand Bakker
    Senior Oracle DBA
  • 1012766
    1012766 Member Posts: 2
    Thank You Sybrand for the link.

    It explains the most important part of the AWR in a easy to understand.After reading through, I got a pretty good idea about what to look for in AWR report. However I following question w.r.t how to apply these concepts in regards to my AWR reports.

    Elasped time : 60 mins
    DB Time : 115 mins
    sessions at the begining of the snap - 67
    sessions at the end of the snap -41

    ==> Since the DB time less than 2 times the Elasped time for the given number of sessions is this a bottle neck ?
    At what point does it make a bottle neck ( we say that there are active sessions waiting)

    Instance Efficiency percentage : This shows all the parameters in that section close to 100. Expect for for Parse CPU to Parse Elapsed % which is 83%
    ==> Is 83% okay?. What are thresholds for these parameters below which we can say that instance efficiency is not good enough?

    Shared Pool Stats :
    Memory Usage % : Begin :: 70, End::86 -> I think this is good because it not going beyond 90 %
    % SQL with executions>1 :Begin::97.6 End:: 97.56 ==> Is this good ? What is threshold below/above this is not good
    %Memory for SQL w/exc>1 : Begin:: 94.76, End::94.12 ==> Is this good ? What is threshold below/above this is not good

    Top 5 Timed Foreground :
    DB CPU ::: WAITS ->(blank) :: Time(s)-> 6,340 :: Avg Wait(ms)->(blank) :: %DB time-> 91.77
    ==> how do I determine what is causing the DB CPU wait event. The other events are small values

    I==> In the SQL statistics there are several sections, Which oen should look at. Is there a relation between each
    section and top 5 timed Foreground event section t ?
  • ji li
    ji li Member Posts: 1,151
    It is a lot more involved and complicated than just to answer a few questions.
    There are entire books written on the subject of performance tuning using the AWR reports.
    Suggest doing some Google searches on AWR reports and enjoy lots of good reading.
    You will have many more questions than answers, and so many things depend on other things.
    If you have a single specific question, then maybe post it and see if someone can answer it.
    But to ask in general terms how to read and interpret the AWR report to do your performance tuning is far more than anyone can tell you in a forum.
  • 1012008
    1012008 Member Posts: 24
    edited Jun 4, 2013 5:44PM

    Edited by: 1009005 on Jun 4, 2013 2:40 PM
  • 1012008
    1012008 Member Posts: 24
    edited Jun 4, 2013 5:46PM
    Take a Awr difference report (awrddrpt.sql) between two time frames you can see if any difference is there in values for a less expeirenced person this is better
    that taking awr report between two time frames with awrrpt.sql. look for top wait events read what is db file sequential read / scattered read how that occurs
    ( to be frank you wont get a one page doc /blogs ) on how to read AWR reports... you need to go through AWR report section by section starting from
    load profile search for each events and understand and after all these reading you can sum up your knowledge when an issue occurs in your database. Take Addm report for same snaps and see what oracle is suggesting dont impliment them just try to undersatnd your analysis and theirs.
    Top 5 sql statements based on execution check that in productions these will be same statements usually check if any new sql creeps up google is best option for
    understanding wait events locks and all other stuff in awr report....

    AWR is all about comparison between two time frames so if you have a historic AWR report compare with current one. Usually what we do is take an AWR report when system is stable and whenever issue happens compare the new with the old one....

    Edited by: 1009005 on Jun 4, 2013 2:45 PM
This discussion has been closed.