We must have known already that block sizes of non-system tablespaces can be controlled and altered, but did you ever think of block sizes of Control files and Redo log files in the database? How to check their block sizes if they have so, can we alter them when required? Well, it is really very important for us to understand these block level concepts.

I did not get some chance to look at this area until I faced a performance issue on one of the database environments I work. Through the process of looking at the issue, I could notice that block size of redo log files is not compatible to the underlined attached storage. This problem is one such real time example of the topic we discussed earlier “How important is disk type for Oracle database files?”.

In this blog, we will discuss only block sizes of control files and redo log files as modifying block sizes on tablespace level is something I hope we would have known it already.

System consideration:

We will be using database server for our next phase of discussion with following configuration:

• OS: Oracle Enterprise Linux
• Database: Oracle 12c database
• Type: Non-CDB database

Can we alter the block size of control file in #Oracle #database? Any impacts Click To Tweet

Control file block size:

Before we actually look at identifying the block size of control file, let us see the default block size of the database.

SQL> show parameter db_block_size

NAME           TYPE        VALUE

———————————— ———– ———————-

db_block_size   integer     8192

It is 8KB on my system, alright now let us check the block size of the control files on this database server.

SQL> select BLOCK_SIZE from v$controlfile;

BLOCK_SIZE

———-

16384

16384

There are two control files on my system each of 16KB size, Aww!!! Should it not be the default block size of 8KB? Well, you see that right. Oracle says that irrespective of block size supported by OS or storage control file block size will always remain 16KB and that cannot be altered.

Will this create any issue? I don’t see that this is as problematic until unless your minimum block size at your operating system is more than 16KB ex: 32KB. When minimum block size at the OS level is 4KB or 8KB then Oracle can bundle 4 blocks or 2 blocks respectively to create one logical block for the control file. Interesting isn’t it!!!

Can we increase the block size of redo log file in #Oracle #database? #Performance impact? Click To Tweet

Redo log block size:

Block size of redo log files are bit different from control files which we have seen so far. Let us quickly check the current block size of all the redo logs on my server. Note that I did not specify any block size of the redo logs when I created this database, so the output below is the default size created by DBCA.

SQL> select BLOCKSIZE from v$log;

BLOCKSIZE

———-

512

512

512

That’s 512B on my system. Just like control file, block size of redo log is not same as the default database block size(8KB).

But can we have a redo log files of different block sizes, I tried creating two other groups with 1KB , 4KB and 8KB.

SQL> alter database add logfile group 4 size 5M blocksize 1024;

ERROR at line 1:

ORA-01377: Invalid log file block size

SQL> alter database add logfile group 4 size 5M blocksize 4096;

ERROR at line 1:

ORA-01378: The logical block size (4096) of file

/pdbdata/ORACDB3/onlinelog/o1_mf_4_%u_.log is not compatible with the disk

sector size (media sector size is 512 and host sector size is 512)

SQL> alter database add logfile group 4 size 5M blocksize 8192;

ERROR at line 1:

ORA-01377: Invalid log file block size

Oracle says that only possible block sizes for redo logs are 512, 1024 and 4096 bytes.

• For 1024, my database server errors out as invalid block size just because my OS platform doesn’t support it. So 1024 is valid only on few OS platforms.
• For 8192, as database itself doesn’t support we see the same error as invalid block size.
• For 4096, there is some interesting error showing us the media sector size on the disk is 512 so it cannot create a redo log with block size as 4096.

In your environment if redo log block size is 4096 then it means that if media sector supports 4096 bytes of block size then oracle automatically identifies and creates redo logs with block size as 4096.

Will this create any issue? Having redo log block size of 4096 bytes results in redo log wastage compared to 512 bytes. There will be tremendous performance issue if your disk is not aligned with the block size of your redo log blocks.

This was the same reason why one my database environment was performing bad, it was because of 4KB of redo log block size and there was lot of redo wastage happening in the database. I could recognize the redo wastage from V$SYSSTAT.

SQL> SELECT name, value FROM v$sysstat WHERE name = ‘redo wastage’;

NAME              VALUE

——————————– ———-

redo wastage       17941684

Key points:

• With 512 bytes of media sectors we still have Control file block size as 16KB, as it logically binds 32 sectors to one block. But this is not possible in redo log.
• Having 4KB block size of redo log files will not degrade the performance of the database always. But there is chance for this to happen if your storage disk is not compatible with such setup.
• Changing already existing redo log file block size is possible from 11gR2 only if redo log group is inactive.
• Each redo log group can have different block sizes if media supports, but never a good practice.

I love to see your comments!

Feel free to give additional information in the comments, that I can validate and include in blog posts or update this as well.