 |
|
Oracle Tips by Burleson |
Database Buffer Tuning
When we refer to database buffer tuning we are actually discussing
the tuning of the memory used to store data used by Oracle
processes. All data that passes to users and then back to the
database passes through buffers. If there aren’t enough db block
buffers there is a significant hit on performance. Likewise if the
database base block buffers aren’t of the correct size then they
can’t be efficiently utilized.
Generally it is suggested that the database block buffer size be set
to at least 8192 (8k). This size of 8k allows for optimal storage of
data and index information on most Oracle platforms. The product of
db_block_size and db_block_buffers should be no less than 5-10% of
the total physical data size (including indexes) for the system.
Usually the product of db_block_size and db_block_buffers will be
larger than 5-10% of the physical database size, but this is a good
general starting point. Of course the size of the buffer area and
other shared global area components, should not exceed 50-60% of the
available physical memory or swapping will result.
One gross indicator of database buffer health is called the hit
ratio. The hit ratio is expressed as a percent and is calculated
using the formula:
(1-(physical reads/(db block
gets+consistent gets))) * 100
Traditionally the information for calculating the database block
buffers hit ratio is taken from the V$SYSSTATS view. However, in
versions 7.3.4 and higher of the database the “physical reads”
parameter was altered to include “direct reads” which skews the hit
ratio in the downward direction. In Oracle8i the statistic “direct
reads” is also recorded so you can subtract the “direct reads” from
the “physical reads” to get the correct value with which to
calculate hit ratio. However, Oracle has provided the
V$BUFFER_POOL_STATISTICS view if the DBA runs the CATPERF.SQL script
in the latest releases in which uncontaminated values for “physical
reads” are available and this view should be used where it is
available.
Hit ratio should generally be as close to 100% as is possible to
achieve, however, in some cases artificially high values can be
received if nonselective indexes are used in queries. Hit ratio is
not the best indicator of performance of the database block buffers.
It is suggested that hit ratio be monitored to give a quick look at
performance, however tuning decisions should be made on a more
detailed analysis of the buffer area.
This is an excerpt by
Mike Ault’s e-book:
Tuning
Third-party Vendor Oracle Systems
Tuning when you can't touch the code
ISBN:
0-9740716-3-3
http://www.rampant-books.com/ebook_vendor_tune.htm
|
