Oracle Redo를 SSD나 Flash에 사용할 경우 Redo block size

Oracle의 Redo log를 성능좋은 SSD/Flash에 Static하게 둘 경우
SSD/Flash의 Sector Size가 4K, Oracle Redo는 512로 Default로 사용하므로
성능효과가 없음.

11g의 경우 Redo log file 생성시 blocksize 4096 Option을 사용해야 하나,
10g이하의 경우 init.ora에 Hidden parameter를 준후에 생성하여야 함.

Oracle Orion Performance Tool로 측정시 block_size를 SSD/Flash의
Sector Size인 4K로 맞추어줄 경우 성능향상이 약 10배 차이가 남 !!

특히 IO, Transaction이 많은 경우 Mirror 구성과 함께 사용하여야 함.

Oracle on Flash: The Case of the 4K Redo Log Block Size

Posted by Chas. Dye - 10.19.2012 Posted In: Database

0

 

84

 

 

Introduction: Redo Block Size Myths

Recently I presented a webinar about Oracle on flash, and demonstrated that many of the traditional storage considerations and compromises facing DBA’s and System Adminstrators are irrelevant on a Pure Storage FlashArray.  In particular, you no longer need to worry about RAID levels, stripe sizes, block sizes and so forth.  Nor do you need to make any fundamental changes to your current database configuration when you migrate to a Pure Storage array.

In this post we’ll examine the impact of redo log block size on performance in our array.  You may have come across blogs recommending a 4K redo log block size for redo logs on flash.  This option is new in Oracle 11gR2 and is designed to take advantage of Advanced Format drives which use a 4K sector size instead of the standard 512 byte sector size.  The cited advantage of the 4K redo block size is that it minimizes block misalignment problems, and hence improves performance.  There is no question that redo log block size can have a significant impact on performance on certain types of SSD’s.  Guy Harrison, for example, observed a redo write time improvement of over 3x using 4K redo logs.  Note that the 4K block size significantly increases redo wastage (redo blocks written to disk before they are full), but usually this is not a big performance concern.

How to Change Redo Block Size

To create redo logs with a non-default block size (512 bytes on most linux platforms), you must specify the “blocksize” setting in the when you create the logfile group.  Your choices are 512, 1024, and 4096.  For example:

13:28:59 system@nduasm.oracle1 SQL> alter database add logfile group 5 blocksize 4096
 13:29:09 2 /

Database altered.

If you see an error such as:

alter database add logfile group 5 size 2g blocksize 4096
 *
 ERROR at line 1:
 ORA-01378: The logical block size (4096) of file +ORARECO is not compatible with the disk sector size (media sector
 size is 512 and host sector size is 512)

you need to set the _disk_sector_size_override parameter to TRUE:

13:17:21 system@nduasm.oracle1 SQL> alter system set "_disk_sector_size_override"=TRUE scope=both;

System altered.