Like this article? We recommend
Like this article? We recommend
Like this article? We recommend
Database Block Size
Choice of an appropriate block size for a database is often one of the most important decisions that can affect the performance of the system. Choosing the proper block size requires a very good understanding of the application environment. Most often the "one size fits all" philosophy is not suitable for Oracle databases, and DBAs have a very difficult time making the right choice. Prior to Oracle9i, the block size for an Oracle database was fixed during database creation time and could not be changed throughout the life of the database. To make matters worse, changing the database block size is not a trivial process (as described later in this article). In addition, prior to Oracle 7.3, the maximum number of extents for an object was limited by the block size; for example, with a block size of 2048 the maximum number of extents was 121. However, from Oracle 7.3 onward this restriction was lifted, and objects are now allowed to have unlimited number of extents. Oracle9i addresses this issue and allows up to four additional nonstandard block sizes to exist in a given database.
Changing Database Block Size
As mentioned earlier, the database block size is fixed during database creation time and cannot be changed without database re-creation. The parameter DB_BLOCK_SIZE from the initialization file is used to determine the block size for a database being created. (Check your operating system documentation to determine the valid settings for DB_BLOCK_SIZE.)
Before attempting to change the block size of an existing database, you should ensure that you have a full and up-to-date backup of the database so that you can recover it if necessary. You should also determine how you will transfer the data into the new database. The following options are available:
Take a full database export and then import the data into the new database. This is probably the simplest option.
Using SQL*PLUS or some other mechanism, dump the data into flat files and then use SQL*Loader to load the data from the flat files into the database.
Use database links from the existing database into the new database and transfer data using these links. This option requires that you create the new database and also keep the old database until all the data has been transferred.
Load and unload the data using Pro*C, Pro*COBOL, or another such application.
It is important to realize that you cannot take a cold backup of the database and introduce the database files into the new database. Data has to be extracted at the logical level and then loaded into the database.
The following general steps can be used to change the database block size:
Take a full cold backup of the database.
Using the selected data transfer method (see options discussed above), extract the data from the database.
Shut down and delete the existing database (if the database is no longer needed).
Change the DB_BLOCK_SIZE parameter in the initialization file to the desired value. This initialization file will be a new file if the existing database is to be kept.
Create the new database.
Using the selected data transfer method, transfer the data into the new database.
Choosing the Proper Database Block Size
The proper choice of database block size depends to a large extent on the type of data involved and the application. The following guidelines can help in this process:
Operating system block size. Good performance can be achieved by ensuring that the Oracle block size is equal to or a multiple of the operating system block size. If this is not the case, the OS may end up performing extra reads and writes during the processing of Oracle blocks, resulting in wasted CPU cycles.
Size of buffer cache used. Larger database block size means that you are using up more memory for the same number of DB_BLOCK_BUFFERS. This also means that more rows are cached. If your rows are small and you use a large block size, a block fetch will result in a lot of rows being fetched (and you may not be interested in all of them). The end result is that the OS is doing more work to fetch things that you don't need. On the other hand, if the row length is large, a large block size may prevent chaining.
Balancing of index branches. Large Oracle block sizes can result in better index balancing, as there are more rows in each branch.
Block Size in Oracle9i
The main reason for the difficulty in supporting multiple block sizes prior to Oracle9i was that the SGA was not architected to support this. Keep in mind that when data is read from the physical data files, they are actually cached in the database buffer cache (unless it is a direct read operation). The size of the database buffer cache is determined by the DB_BLOCK_SIZE and DB_BLOCK_BUFFERS parameters as follows:
Database buffer cache size = DB_BLOCK_SIZE x DB_BLOCK_BUFFERS
From Oracle9i onward, the DB_BLOCK_SIZE initialization parameter specifies the standard block size for the database. This block size is used in the SYSTEM tablespace and by default in other tablespaces. Oracle9i supports up to four additional nonstandard block sizes. If a DB_BLOCK_SIZE is not specified, the default data block size is specific to the operating system. The following things should be considered while choosing the DB_BLOCK_SIZE parameter:
Choose the most commonly used block size as the database block size.
Choose the database block size to be a multiple of the operating system's block size.
Understand the nature of transactions run against the database. Larger block sizes can help in decision support systems (DSS) and provide greater efficiency in disk and memory I/O.
In Oracle9i, you can use the create tablespace command and specify the BLOCKSIZE clause to create tablespaces of nonstandard block sizes. These nonstandard block sizes can have any power of two values between 2K and 32K. Note, however, that some platform-specific restrictions may prevent the use of certain sizes on some platforms. This flexibility of having tablespaces of different block sizes in the same database is particularly useful if you plan to transport tablespaces from one application environment to another.
Use of multiple block sizes requires that you configure sub-caches within the buffer cache area of the SGA for all of the nonstandard block sizes that you plan to use. In other words, in order to use multiple block sizes in the database, the DB_CACHE_SIZE and at least one DB_nK_CACHE_SIZE parameter must be set. The DB_CACHE_SIZE parameter specifies the size of the cache for standard block size buffer (the standard block size itself is specified by DB_BLOCK_SIZE). The size and numbers of nonstandard block size buffers are specified by the dynamic parameters. In the following table, the parameter in the first column specifies the size of the buffer cache for a block size as shown in the second column.
|
Parameter |
Block Size |
|
DB_2K_CACHE_SIZE |
2KB |
|
DB_4K_CACHE_SIZE |
4KB |
|
DB_8K_CACHE_SIZE |
8KB |
|
DB_16K_CACHE_SIZE |
16KB |
|
DB_32K_CACHE_SIZE |
32KB |
The MAX_SGA_SIZE initialization parameter specifies the maximum size of the SGA for the lifetime of the instance. Oracle9i allows you to dynamically change the initialization parameters affecting the size of the buffer caches, shared pool, and large pool—but only to the extent that the sum of these sizes combined with the size of the other SGA components (fixed SGA, variable SGA, and redo log buffers) doesn't exceed the size specified by MAX_SGA_SIZE.
Tablespaces that make use of nonstandard block sizes can be determined by using the following script:
set echo off feedback off pagesize 50 term off linesize 80
spool tblspce_with_non_std_blksize.txt
select Tablespace_Name,
Block_Size
from DBA_TABLESPACES
where Block_Size != (select Value
from v$parameter
where name = 'db_block_size');
spool off
set newpage 1 verify on feedback 6 pagesize 24
set linesize 80 heading on
Sample output of running the above script is shown below:
TABLESPACE_NAME BLOCK_SIZE ------------------------------ ---------- DRSYS 8192 TABLESPC4K 4096 TABLESPC8K 8192 INDX 4096 PROJECTS 8192 TOOLS 16384 USERS 8192
Note that you can query the parameter DB_BLOCK_SIZE to determine the standard block size in use for a given database. The above output indicates that the nonstandard block sizes in use for the database are as follows:
4KB (tablespaces TABLESPC4K and INDX)
8KB (tablespaces DRSYS, TABLESPC8K, PROJECTS, and USERS)
16KB (tablespace TOOLS)
In Oracle9i, the size of the SGA can be static as in the prior releases or it can be dynamically configured. This means that the following primary parameters used to size the SGA can be changed while the instance is running:
Buffer cache. The size in bytes of the cache of standard blocks, specified by DB_CACHE_SIZE, can be changed dynamically. However, the cache size of the nonstandard block sized buffers is not dynamic in Oracle9.1.x.
Shared pool. The size in bytes of the area used by shared SQL and PL/SQL statements, specified by SHARED_POOL_SIZE, can be changed dynamically.
Large pool. The size in bytes of the large pool used for session memory in shared server systems, parallel execution for message buffers, any backup and restore processes by disk I/O buffers, as specified by LARGE_POOL_SIZE, can be changed dynamically. LARGE_POOL_SIZE cannot be changed dynamically in Oracle9.0.1.
Part 2 in this series, "Sizing the Database Cache," discusses how to size the database cache in an Oracle9i database. The article also discusses several general guidelines, as well as some specific tips for e-business environments.