8 Replies Latest reply: Jul 15, 2009 11:12 AM by amardeep.sidhu RSS

    AWR study link

    A-K
      Hi,

      Please provide me with a good link for understanding the AWR report.

      Thanks.
        • 1. Re: AWR study link
          Finbar Fitzgerald
          Hi there

          You don't mention your Oracle level. For Oracle 10g the following link is useful as an introduction http://www.oracle.com/technology/pub/articles/10gdba/week6_10gdba.html.

          If you look towards the bottom of that article you will find another link to the Oracle documentation which has more comprehensive guides to the AWR , in particulart the Oracle Database Performance Tuning Guide has a good section.

          if you have access to metalink there are also some excellent articles there. Just do a search for AWR and browse
          • 2. Re: AWR study link
            591186
            [http://www.oracle-base.com/articles/10g/AutomaticWorkloadRepository10g.php]

            [http://download-west.oracle.com/docs/cd/B13789_01/server.101/b10752/autostat.htm]


            HTH
            Anantha
            • 3. Re: AWR study link
              Aman....
              Well, I believe that you want to understand the AWR report. For that, there is no as such link which is there. But Jonthan did a very nice 10 part series on his blog to understand and interpret the statspack report. And as awr report is nothing but the statspack on steriods, so that should set a base for you. Start reading this post,

              http://jonathanlewis.wordpress.com/2006/11/29/analysing-statspack-pt1/

              HTH
              Aman....
              • 4. Re: AWR study link
                711962
                And as awr report is nothing but the statspack on steriods
                Nice way to put it though :)

                - Amy De Cay

                Edited by: user11695297 on Jul 15, 2009 5:59 AM
                • 5. Re: AWR study link
                  Aman....
                  user11695297 wrote:
                  And as awr report is nothing but the statspack on steriods
                  Nice way to put it though :)
                  ;-)

                  Aman....
                  • 6. Re: AWR study link
                    amardeep.sidhu
                    Apart from the links given above you can read the 14th chapter of Richard Niemiec's book Oracle Database 10g Performance Tuning Tips & Techniques. Its available online on McGraw Hill's website:

                    http://mhprofessional.com/downloads/products/0072263059/0072263059_ch14.pdf

                    Regards,
                    Amardeep Sidhu
                    • 7. Re: AWR study link
                      Charles Hooper
                      Amardeep Sidhu wrote:
                      Apart from the links given above you can read the 14th chapter of Richard Niemiec's book Oracle Database 10g Performance Tuning Tips & Techniques. Its available online on McGraw Hill's website:

                      http://mhprofessional.com/downloads/products/0072263059/0072263059_ch14.pdf

                      Regards,
                      Amardeep Sidhu
                      Interesting link. That might be a fine book, but what I have read so far in that chapter makes me wonder... what if I had reviewed this book? Keep in mind that this book covers Oracle 10g (and mentions 10g R2 in the chapter), just a couple quotes from various pages:
                      Page 23:
                      "Some DBAs (usually those trying to sell you a tuning product) minimize the importance of hit ratios (proactive tuning) and focus completely on waits (reactive tuning), since focusing on waits is a great way to quickly solve the current burning problems. By monitoring the Instance Efficiency section (and using all of STATSPACK and Enterprise Manager), the DBA will combine reactive and proactive tuning and will find some problems before the users scream or wait events hit the top 5 list. Hit ratios are one important piece of the puzzle (so are waits)."

                      Page 24:
                      "Hit ratios are a great barometer of the health of your system. A large increase or drop from day to day is an indicator of a major change that needs to be investigated."

                      Page 27:
                      "Try to cache small tables to avoid reading them into memory over and over again. Locate the data on disk systems that have either more disk caching or are buffered by the OS file system cache. DB_FILE_MULTIBLOCK_READ_COUNT can make full scans faster (but it could also influence Oracle to do more of them)."

                      Page 42:
                      "Consistent gets The number of blocks read from the buffer cache for queries without the SELECT FOR UPDATE clause. The value for this statistic plus the value of the “db block gets” statistic constitute what is referred to as logical reads (all reads cached in memory). These are usually the CURRENT version of the block, but it can also be a Consistent Read (CR) version."

                      "Hit Ratio = (Logical Reads - Physical Reads) / Logical Reads" "The buffer hit ratio should be above 95 percent. If it is less than 95 percent, you should consider increasing the size of the data cache by increasing the DB_CACHE_SIZE initialization parameter (given that physical memory is available to do this)."

                      Page 43:
                      "If chained rows are indicated, the problem needs to be fixed as soon as possible. Chained rows can cause severe degradation of performance if a large number of rows are chained."

                      Page 44:
                      "The parameter that can be set in the init.ora to help improve the read time is the DB_FILE_MULTIBLOCK_READ_COUNT parameter, which controls the number of blocks that can be read in one I/O when a full table scan is being performed. This can reduce the number of I/Os needed to scan a table, thus improving the performance of the full table scan. Unfortunately, the optimizer might do more full table scans as a result of setting DB_FILE_MULTIBLOCK_READ_COUNT (you don’t want this behavior), so you may also need to set the OPTIMIZER_INDEX_COST_ADJ to a number, such as 10, to eliminate this problem and drive the use of indexes."

                      I wonder if the book author would want to rewrite that chapter, or leave it as it is? Any comments?

                      Charles Hooper
                      IT Manager/Oracle DBA
                      K&M Machine-Fabricating, Inc.
                      • 8. Re: AWR study link
                        amardeep.sidhu
                        Actually one day i was searching for some documents on statspack and AWR and came across this document. I never read this. The points you have posted definitely need a review. Thanks for pointing it out !

                        Apologies for the reference of un-tested/tried stuff !

                        Regards,
                        Amardeep Sidhu