 |
|
The
db_file_multiblock_read_count and Oracle
Oracle Tips by Burleson |
Oracle has a parameter that controls the rate
for which blocks are read when long contiguous data blocks are
requested. The db_block_size parameters can have a dramatic
impact on system performance. In addition, there is an important
relationship between db_block_size and the
db_file_multiblock_read_count parameter. At the physical level in
UNIX, Oracle always reads in a minimum of 64K blocks.
Therefore, the values of
db_file_multiblock_read_count and db_block_size should be
set such that their product is 64K. For example:
|
8K blocks |
Db_block_size = 8,192 |
db_file_multiblock_read_count = 8 |
|
16K blocks |
Db_block_size = 16,384 |
db_file_multiblock_read_count = 4 |
Again, the db_file_multiblock_read_count is most
beneficial for systems that perform frequent full table scans, such as
data warehouses.
Note - Increasing the
db_file_multiblock_read_count can change the behavior of the
Cost-based SQL optimizer, increasing the likelihood of Oracle choosing
nested loop table access and favoring full table scans over index
range scans.
Of course, everything changes in Oracle,
where you can dynamically allocate separate data buffers for any
desired block size. In Oracle, you can choose block sizes of 2K, 4K,
8K, 16K and 32K (on platforms that support 32k block sizes).
Tuning the Oracle7 through
Oracle8i Data Buffers
The purpose of the Oracle data buffers is to
reduce disk I/O. As you know, disk I/O is the single largest component
to performance, and all tools that reduce I/O are of great interest to
someone who is tuning Oracle. Oracle has an insatiable hunger for data
buffers, and the addition of buffers will always improve performance
by reducing physical disk I/O. We will begin by examining the nature
of the data buffers and then explore how the DBA can use STATSPACK to
monitor and tune the data buffers for the maximum performance.
Introduction of Data Block
Caching
When an SQL statement requests a row, Oracle
will first check the internal memory structures to see if the data is
already in a data buffer. By caching data blocks in RAM, Oracle avoids
doing unnecessary I/O. With the advent of very large SGAs in some
64-bit releases of Oracle, small databases can be entirely cached, and
the DBA defines one data buffer for each database block. However, for
very large databases, the RAM data buffers cannot hold all of the
database blocks and Oracle has a scheme for keeping frequently used
blocks in RAM.
When there is
not enough room in the data buffer for the whole database, Oracle
utilizes a least-recently-used algorithm to determine which database
pages are to be flushed from memory. Oracle keeps an in-memory control
structure for each block in the data buffer. Each new data block
enters at the middle of the data buffer, and moves a data block to the
front of the list every time the block is requested. Data blocks that
are not frequently referenced will age-out to the end of the data
buffer, where they will eventually be erased to make room for a new
data block, as shown in Figure 9-5.
Figure 9-59: Aging data blocks from the RAM
block buffer
Later in this chapter we will see how to use
the stats$buffer_pool_statistics table to see the behavior of each of
the data buffer pools.
Starting in Oracle8, Oracle provides three
separate pools of RAM within the db_cache_size region to hold
incoming Oracle data blocks:
-
KEEP pool This
pool is used to hold tables that are frequently referenced by the
application. This normally includes small tables that have frequent
full tables scans and reference tables for the application. The KEEP
pool is the next evolution of the Oracle7 table cache
command.
-
RECYCLE pool This
data pool is reserved for large-table full table scans. Because
Oracle data blocks from full table scans are unlikely to be reread,
the RECYCLE pool is used so that the incoming data blocks do not
“flush out” data blocks from more frequently used tables and
indexes.
-
DEFAULT pool The
DEFAULT pool is used for all table and index access that is not
appropriate for the KEEP pool or the RECYCLE pool.
Remember, the KEEP and RECYCLE pools are
subsets of the DEFAULT pool.
In addition, we must remember that each of
these pools are segregated into “hot” and “cool” areas. Please note
that Oracle has also dramatically changed the way that data blocks are
handled within each of these buffers. In Oracle7, incoming data blocks
were always added to the most recently used end of the data buffer.
Starting in Oracle8i, incoming data blocks use a midpoint insertion
technique. We will go into this in greater detail later in this
chapter.
To see your current buffer parameters, you can
use the server manager utility (svrmgrl) and issue the show
parameters buffer command. Here is a list from an Oracle8i
database.
SVRMGR>
show parameters buffer
NAME TYPE VALUE
----------------------------------- -------
------------------------------
buffer_pool_keep string
500
buffer_pool_recycle
string
db_block_buffers integer
6000
log_archive_buffer_size integer
64
log_archive_buffers integer
4
log_buffer integer
2048000
sort_write_buffer_size integer
32768
sort_write_buffers integer
2
use_indirect_data_buffers boolean FALSE
Here we see the KEEP pool (buffer_pool_keep), the
RECYCLE pool (buffer_pool_recycle) and the DEFAULT pool (db_cache_size).
Here is the same listing for an Oracle
database. As the buffer parameters change, so do the listed columns.
SQL> show
parameters buffer
NAME TYPE VALUE
------------------------------------ ------- ------
buffer_pool_keep string
buffer_pool_recycle string
db_block_buffers integer 0
log_buffer integer 524288
use_indirect_data_buffers boolean FALSE
Full Data Caching
Starting with Oracle8i, it is possible to
create a database that is fully cached in the data buffers. Prior to
the introduction of the 64-bit versions of Oracle, the maximum size of
an Oracle SGA was 1.7 gigabytes on many UNIX platforms. With 64-bit
addressing, there is no practical limitation on the size of an Oracle
SGA, and the DBA can create database instances with enough data
buffers to cache the whole database.
To appreciate the benefit of full data caching,
we must remember the time difference between retrieving a data block
in RAM vs. fetching a data block from disk. Access time on disks is
expressed in milliseconds (thousandths of a second) while RAM speed is
expressed in nanoseconds (billionths of a second). In sum, RAM access
is faster by two orders of magnitude, and RAM can be thought of as
14,000 times faster than disk.
When fully caching an Oracle database, we must
carefully plan for the caching. The multiple data buffer pools are no
longer needed when caching the entire database, and most DBAs cache
all of the data blocks in the DEFAULT data pool.
Today, any database with less than 20 gigabytes
is commonly fully cached, while larger databases still require partial
data buffer caches. To calculate the number of allocated data blocks,
the DBA simply issues the following command:
SQL> select
2 sum(blocks)
3 from
4 dba_data_files;
SUM(BLOCKS)
-----------
217360
SQL> select
2 sum(blocks)
3 from
4* dba_extents
SUM(BLOCKS)
-----------
127723
And the actual number of used blocks can only be determined by
looking at DBMS_ROWID for actual block addresses>>
select
sum(blocks)
from
dba_data_files;
As the database expands, the DBA must be
mindful to increase the db_block_ buffers, but this approach
ensures that all read activity is fully cached. At database startup
time, the DBA will invoke a script to load the buffers, generally by
issuing a select count(*) from xxx; for all of the tables in
the database.
This ensures that all data blocks are cached
for reads, but write activity will still require disk I/O. With RAM
memory getting less expensive every year, Oracle DBAs will continue to
make smaller databases fully cached.
This is an excerpt from "Oracle
High Performance tuning with STATSPACK" by Oracle Press.
 |
If you like Oracle tuning, you may enjoy the new book "Oracle
Tuning: The Definitive Reference", over 900 pages of BC's favorite tuning
tips & scripts.
You can buy it direct from the publisher for 30%-off and get instant access to
the code depot of Oracle tuning scripts. |
|
