How Big Should the Oracle Block Size Be in Font

Encoding Code 3 of 9 in Font How Big Should the Oracle Block Size Be

You, as the DBA, have to decide how big your Oracle blocks should be and set the DB_BLOCK_SIZE parameter in your Oracle initialization file (the init.ora file). Think of the block size as the minimum unit for conducting Oracle s business of updating, selecting, or inserting data. When a user selects data from a table, the select operation will read, or fetch, data from the database files in units of Oracle blocks. If you choose the common Oracle block size of 8KB, your data block will have exactly 8,192 bytes. If you use an Oracle block size of 64KB (65,536 bytes), even if you just want to retrieve a name that s only four characters long, you ll have to read in the entire block of 64KB that happens to contain the four characters you re interested in.

If you re coming to Oracle from SQL Server, you can think of the Oracle block size as being the same as the SQL Server page size.

As was mentioned earlier, the operating system also has a disk block size, and the operating system reads and writes information in whole blocks. Ideally, the Oracle block size should be a multiple of the disk block size; if not, you may be wasting time reading and writing whole disk blocks while only making use of part of the data on each I/O. On an HP-UX system, for example, if you set your Oracle block size to a multiple of the operating system block size, you gain 5 percent in performance.

Oracle offers the following guidelines for choosing the database block size: Choose a smaller block size if your rows are small and access is predominantly random. Choose a larger block size if the rows are small and access is mostly sequential (or random and sequential), or if you have large rows. In 10, which discusses the creation of Oracle databases, you ll learn a lot more about Oracle database block size and the criteria for choosing an appropriate block size.

Note The Oracle block size that you should choose depends on what you re going to do with your database. For example, a small block size is useful if you re working with small rows and you re doing a lot of index lookups. Larger block sizes are useful in report applications when you re doing large table scans. If you are unsure about what block size to use, remember that Oracle recommends that you choose a block size of 8KB for systems that process a large number of transactions.

The DB_BLOCK_SIZE initialization parameter determines the standard block size in your Oracle database, and it can range from 2KB to 32KB. The system tablespace is always created with the standard block size, and Oracle lets you specify up to four additional nonstandard block sizes. For example, you can have 2KB, 4KB, 8KB, 16KB, and 32KB block sizes all within the same database I discuss the reasons you might wish to do this shortly, in the Tablespaces section. If you choose to configure multiple Oracle block sizes, you must also configure corresponding subcaches in the buffer cache of the system global area (SGA), which is Oracle s memory allocation, as you ll learn in the Understanding Main Memory section of this chapter. Multiple data block sizes aren t always necessary, and you ll do just fine in most cases with one standard Oracle block size. Multiple block sizes are useful primarily when transporting tablespaces between databases with different database block sizes.

All data blocks can be divided into two main parts: the row data portion and the free space portion. (There are also other smaller areas, such as overhead and header space for maintenance purposes.) The row data section of data blocks contains the data stored in the tables or their indexes. The free space section is the space left in the Oracle block for new data to be inserted or for existing rows in the block to be extended. Sometimes it may be useful to find out exactly what data is in a particular block or to find out which block contains a particular piece of data. You can actually see what s inside a data block by dumping the block contents. Oracle blocks can be dumped at the operating system level (referred to as binary dumps), and you can also perform Oracle-formatted block dumps. The most common reason for performing a block dump is to investigate block corruption, which may be caused by operating system or Oracle software errors, hardware defects, or memory or I/O caching problems. The Recovery Manager (RMAN) provides ways to recover from block corruption, and you can also use the Data Recovery Advisor to adopt other strategies to recover from data block corruption, as I explain in 16. Let s look at what s actually in an Oracle data block. First, before you do a data dump, you need to find out which datafile and data block you want to dump. Listing 5-1 shows a query that enables you to determine the file and block IDs.