This content has been marked as final. Show 8 replies
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
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,
Amardeep Sidhu wrote: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:
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:
"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)."
"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."
"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)."
"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)."
"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."
"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?
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.