Oracle Architecture - Oracle Essentials - page 49

Setting the Database Block Size
Prior to Oracle9 i , you set the database block size for an Oracle Database at the time you
created the database, and you couldn't change it without re-creating the database. Since
Oracle9 i , you have more flexibility, because you can have multiple block sizes in the
same database. In all versions, the default block size for the database is set using the
DB_BLOCK_SIZE instance initialization parameter.
How do you choose an appropriate block size for an Oracle Database? Oracle defaults
to a block size based on the operating system used, but understanding the implications
of the block size can help you determine a more appropriate setting for your workload.
The block size is the minimum amount of data that can be read or written at one time.
In online transaction processing (OLTP) systems, a transaction typically involves a rel‐
atively small, well-defined set of rows, such as the rows used for placing an order for a
set of products for a specific customer. The access to rows in these operations tends to
be through indexes, as opposed to through a scan of the entire table. Because of this,
having smaller blocks (4 KB) might be appropriate. Oracle won't waste system resources
by accessing larger blocks that contain additional data not required by the transaction.
Data warehouses workloads can include reading millions of rows and scans of all the
data in a table. For this type of activity, using bigger database blocks enables each block
read to deliver more data to the requesting user. To support these operations best, data
warehouses usually have larger blocks, such as 8 KB or 16 KB. Each I/O operation might
take a little longer due to the larger block size, but the reduced number of operations
will end up improving overall performance.
Datafile structure
The first block of each datafile is called the datafile header . It contains critical informa‐
tion used to maintain the overall integrity of the database. One of the most critical pieces
of information in this header is the checkpoint structure . This is a logical timestamp that
indicates the last point at which changes were written to the datafile. This timestamp is
critical during an Oracle recovery process as the timestamp in the header determines
which redo logs to apply in bringing the datafile to the current point in time.
Extents and segments
From a physical point of view, a datafile is stored as operating system blocks. From a
logical point of view, datafiles have three intermediate organizational levels: data blocks,
extents, and segments. An extent is a set of data blocks that are contiguous within an
Oracle datafile. A segment is an object that takes up space in an Oracle Database, such
as a table or an index that is composed of one or more extents.