Understanding 4KB Sector Support for Oracle Files

February 2, 2011

An important decision that DBAs make when they are first creating a new Oracle database is what the standard database block size should be. Once the database is created, it is virtually impossible to change the standard block size without recreating the entire database. Read on to learn how to create tablespaces with different block sizes.

An important
decision that DBAs make when they are first creating a new Oracle database is
what the standard database block size should be. Oracle generally supports
five options, 2K, 4K, 8K (now the default), 16K and 32K. The blocksize is
determined by the DB_BLOCK_SIZE parameter and once the database is created, it
is virtually impossible to change the standard block size without recreating
the entire database.

The block
size is important because it has inherent performance impacts that range from
contention to reads. The smaller blocks are better to reduce contention
because there are usually fewer rows per block and this is generally favored
for online transaction systems. However, data warehouse or DSS systems benefit
from a larger block size because Oracle is able to retrieve more rows per block
read. Fortunately, since Oracle 9i, we have had the ability to create
tablespaces with different block sizes, which allows for transportable
tablespaces between databases and gives DBAs the ability to even further
control storage options.

It is well
understood that the DB_BLOCK_SIZE becomes the default block size for the data files
for any tablespace that is created without a specific blocksize option. However,
folks may also assume that the default block size actually applies to ALL of
the Oracle database files associated with their databases. Well, surprise!
This is not actually the case. The DB_BLOCK_SIZE parameter does not control
the size of the redo log files (this includes online redo logs, archived redo
logs and standby redo logs) nor the control file.

In all of the
versions of Oracle from 11gR1 and prior - it can be kind of tricky to determine
exactly what block size is being used for these files. That is because this
information is not readily available in most of the associated v$ views. There
are three v$ views that are used to gather information about the logs and
control files. These are v$log, v$logfile and v$controlfile.

Let's start
by doing a describe on the v$log and v$logfile views (these examples are from a
10g database with the DB_BLOCK_SIZE parameter set to 8K), an 11gR1 database
would show the same results). As you can see by the images below, neither view
shows us any information about the actual block size for the files.

In order to
really find out what the block size is for the redo files, we need to query one
of the x$ tables. The table is x$kccle (Kernel Cache ControlfileComponent Log
Entry). The lebsz column shows the actual block size being used by the redo
logs.

As we can see
by this view, the block size being used is 512 bytes- which does not match any
of the valid values for DB_BLOCK_SIZE. This value is derived automatically by Oracle
and is based on the standard sector size for hard drives, which for many years
has been 512 bytes.

The other
major file type associated with Oracle databases is the control file. We can
confirm the block size of the control files by checking the cfbsz column of x$kcccf
(Kernel Cache Current Control File).

As we can
see, the control file uses a block size of 16K regardless of what the
DB_BLOCK_SIZE is set to.

For many
years and most systems, these settings for the log file block size and control
file size have been fine, and fully compatible with the sector size of the
actual hardware devices that we have been using.

However,
hardware has been changing and evolving, and some storage systems now use a 4K
sector size rather than the old 512 byte sector size. While this is not an
issue for our control file block size of 16K because it is a multiple of the
new sector size, it does present a possible performance degradation issue for the
default log file block sizes because they do not match the hardware sectors.

With 11g R2, Oracle
has introduced the necessary support for us to control the block size of the
redo logs so that we are able to match them to the newer 4K hardware sector
sizes, which will ultimately provide the performance benefit of ensuring the
log write entries are going to align with the sector size of the hardware.

The 4K
emulation mode disks have a 4K physical sector, which is comprised of eight 512
byte logical sectors.

4K PHYSICAL
SECTOR

Log Sec 1

Log Sec 2

Log Sec 3

Log Sec 4

Log Sec 5

Log Sec 6

Log Sec 6

Log Sec 8

With this
configuration, the device still maintains a 512 byte interface to the disk
maintained through the LBA (logical block address). If a LGWR write is not
aligned with the beginning of a physical 4K sector, there will be a significant
performance hit.

With 4K
native mode disks, the physical sector and LBA are both 4K.

4K PHYSICAL
SECTOR

Log Sec 1

With this
type of disk, there is only a 4K interface available to the disk.

Beginning in
Oracle 11g R2, Oracle has new recommendations for the block sizes for the redo
log files and data files.

512 Byte Sector Disks

512K block
size is mandatory for redo logs

any size
can be used for the database block size

16K will be
used for the control file block size by default

4K Emulation Mode Disks

4K block
size is recommended for redo logs

a multiple
of 4K is recommended for the database block size (which most systems are)

16K will
be used for the control file block size by default

If log files
are created with a 512 byte size on a system using 4K emulation, a warning will
be written to the alert log regarding the potential for performance
degradation.

4K Native Mode

- 4K block
size is mandatory for redo logs

- a multiple
of 4K is mandatory for the database block size

16K will be
used for the control file block size by default

To specify a
4K size when using ASM, we add a new attribute to the CREATE DISKGROUP command,
which is the sector size. The two valid values for sector_size are 4096 or 512.

To create log
files with 4K blocks add the BLOCKSIZE option to the create log file command
(this applies both to the CREATE DATABASE and ALTER DATABASE ADD LOGFILE GROUP
commands). Again, the valid values are 4096 or 512. (1024 is support for some
HP platforms).

Also, with
Oracle 11gR2 , a new column has been added to the v$log view which shows the
blocksize.

If you are
upgrading the hardware associated with your 11gR2 database from a system that
formerly had 512 byte sectors to one that has the new 4K sector size, the
easiest solution is to create new redo groups with the 4K block size, perform
the ALTER SYSTEM SWITCH LOGFILE command until the new groups are in use and the
old ones are inactive, and then drop the old 512 byte redo log groups.

If your
hardware supports the 4K sector size, even in emulation mode, it is very important,
for performance reasons, to make sure you adjust the database redo log files
appropriately.