So far as Oracle default block size is concerned, 8k is the default size and optimal size for most of the OLTP and transactions. It strikes a good balance between IOPS and bandwidth test. Typically , OLTP and mixed workload databases use an 8K block size which is the introduced in XtremIO 3.0. Now OLTP databases run nearly 1.5X faster, data warehouses achieve nearly 2X better throughput, and cloning virtual machines takes half the time. Arguably, the 8k DB block size is ideal for most workloads. It strikes a very good balance between IOPS and Bandwidth.
However, a very strong case can be made for 4K in extreme circumstance where rows fit nicely in 4k block size, the buffer cache is not effective due to the randomness of the application access, and the speed of the storage now becomes the most determining factor for successful deployment. When using a 4KB request size, the XtremIO array can service approximately 20-30% more I/O’s per second than 8KB requests.
Flash arrays use the latest 4K sector size advanced format and perform at their optimal level when managing 4K blocks. The testing has proven that by using a 4K database block size under certain workload conditions the performance improves over 8K and 16K. The 4K block size was able to complete more work within the same time frame pushing through higher transactions per second at lower response times due. The reason for this is because less reads are required when fetching data, which is less than 4k in size. For a business this means an increase in application or end user productivity and efficiency along with cost savings by better utilizing resources.
What can be the optimal DB block size here (4k or 8k) ? Or is it dependent on the requirement and the nature of the DB environment ?