Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, John.

Asked: December 06, 2016 - 3:09 am UTC

Last updated: December 07, 2016 - 2:11 pm UTC

Version: 11.2

Viewed 10K+ times! This question is

You Asked

Good Evening,

In 11g, I've read about the possibility of setting redo logs to have a blocksize of 4k. Supposedly, the blocksize is automatically set based on the block sector of the disk. Supposedly, high capacity disks have block sectors of 4k. Well, in our case, the database is on high capacity disks, but the redo logs are still created with a 512 byte block size. Did I fail by not doing something so that the redo logs use a 4k blocksize instead for higher performance? Does the system admin or storage admin control the block sector size? I am trying to understand if Oracle is simply telling us that our redo logs will have either 512 bytes, 1024 bytes or 4 k block sizes which will be dependent on the sector size as an FYI or are they telling us so that we can do something to use 4k blocksizes in 11g?

Thanks,

John

and Connor said...

To my knowledge, 4k blocksizes are not *automatically* selected, they are just *available* for you to use, ie, you would do:

alter database add logfile '...' size 500m blocksize 4k;

or similar.

In terms of seeing significant benefit, then all sorts of factors (ASM, OS and the storage) come into play.

A good blog post here about it

https://flashdba.com/4k-sector-size/deep-dive-oracle-with-4k-sectors/

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

logic_sector_size=4k

Viacheslav, December 06, 2016 - 12:57 pm UTC

It seemsto me that in 12.2 version Oracle improves
support modern 4k disks. You can specify logic_sector_size=4k for ASM diskgroups (Oracle Database 12c Release 2, Automatic Storage Management Administrator's Guide):
https://docs.oracle.com/database/122/OSTMG/create-diskgroups.htm#GUID-8B656725-D347-4166-8775-1070B6C4D580




Additional point

J. Laurindo Chiappa, December 06, 2016 - 5:56 pm UTC

Additionally, I indicate that in the "Oracle Database Administrator's Guide 11gR2" we have the following information:

"
Planning the Block Size of Redo Log Files

Unlike the database block size, which can be between 2K and 32K, redo log files always default to a block size that is equal to the physical sector size of the disk. Historically, this has typically been 512 bytes (512B).

Some newer high-capacity disk drives offer 4K byte (4K) sector sizes for both increased ECC capability and improved format efficiency. Most Oracle Database platforms are able to detect this larger sector size. The database then automatically creates redo log files with a 4K block size on those disks.
"

So, it is NOT mandatory to indicate the block size for redo, in many modern hardwares....

Best regards,

J. Laurindo Chiappa



Connor McDonald
December 07, 2016 - 2:11 pm UTC

Thanks for the additional info

Thanks!

John Cantu, December 06, 2016 - 10:36 pm UTC

Thank you, I will take a look at those references you sent me, Connor. However, I wish Oracle documentation would provide more information on this. It seems like an important subject for Oracle not to provide detailed information and examples. There could be many Oracle systems out there misconfigured because of this new 4k sector "improvement". I don't have any idea how much slower.
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.