Understanding 4KB Sector Support for Oracle Files By Karen Reliford

Understanding 4KB Sector Support for Oracle Files

By Karen Reliford

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.
There are two types of 4K sector disks available.  4K emulation mode disks and 4K native mode disks.
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.

CREATE DISKGROUP group1 NORMAL REDUNDANCY
FAILGROUP f1 DISK
'/dev/diska1',
'/dev/diska2',
'/dev/diska3',
'/dev/diska4'
FAILGROUP f2 DISK
'/dev/diskb1',
'/dev/diskb2',
'/dev/diskb3',
'/dev/diskb4'
ATTRIBUTE 'sector_size'='4096';

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).

CREATE DATABASE mydb NORESETLOGS FORCE LOGGING
ARCHIVELOG
LOGFILE
  GROUP 1 '$ORACLE_BASE/oradata/mydb/redo01.rdo'
   SIZE 100M BLOCKSIZE 4096,
  GROUP 2 '$ORACLE_BASE/oradata/mydb/redo02.rdo‘
   SIZE 100M BLOCKSIZE 4096
DATAFILE
....
 
 
 
ALTER DATABASE mydb ADD LOGFILE GROUP 3
('$ORACLE_BASE/oradata/mydb/redo03a.rdo',
'$ORACLE_BASE/oradata/mydb/redo03b.rdo')
SIZE 500K BLOCKSIZE 4096;

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.