5 Replies Latest reply on Jun 4, 2013 9:46 PM by 1012008

    Understanding AWR report - Newbie

    1012766
    1012766 Jun 4, 2013 4:36 PM
      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
        • 1. Re: Understanding AWR report - Newbie
          sybrand_b Jun 4, 2013 4:44 PM (in response to 1012766)
          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

          http://osamamustafa.blogspot.nl/2012/06/understand-awr-report.html

          ------------
          Sybrand Bakker
          Senior Oracle DBA
          • 2. Re: Understanding AWR report - Newbie
            1012766
            1012766 Jun 4, 2013 5:33 PM (in response to 1012766)
            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 ?
            • 3. Re: Understanding AWR report - Newbie
              ji li
              ji li Jun 4, 2013 7:27 PM (in response to 1012766)
              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.
              • 4. Re: Understanding AWR report - Newbie
                1012008
                1012008 Jun 4, 2013 9:44 PM (in response to ji li)
                .....

                Edited by: 1009005 on Jun 4, 2013 2:40 PM
                • 5. Re: Understanding AWR report - Newbie
                  1012008
                  1012008 Jun 4, 2013 9:46 PM (in response to 1012766)
                  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