Call (800) 766-1884 for Oracle support & training
Free Oracle Tips


Oracle Consulting Support
Oracle Upgrades
Use New Oracle Features
Oracle Replication Support
Oracle Training
Remote Oracle DBA
System Documentation
Oracle Tips
Oracle Performance
 

Free Oracle Tips


 

HTML Text

BC Oracle tuning

Oracle training

Oracle support

Remote Oracle

 

 

   
  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.

  
 

Oracle performance tuning software 
 
 
 
 

Oracle performance tuning book

 

 
 
 
Oracle performance Tuning 10g reference poster
 
 
 
Oracle training in Linux commands
 
Oracle training Excel
 
Oracle training & performance tuning books
 

 

   

Copyright © 1996 -  2014 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation. 

Hit Counter