Oracle redo logs use a different blocksize

Article by author Anan Sharma

Oracle’s standard block size is applicable to only the datafiles. The redo log files and the control files use a different block size. Yes, the block size used by them is not shown in any of the views( things have changed a little from 11.1 onwards) so even if there would be any other value for the blocks, that is not visible.

I have met many oracle dba’s arguing that the standard block size set at the time of the database creation is the size which is applicable to all the types of the files. So if you have a block size of 8kb, this would be size of your datafiles, control files and also of the log files. This sounds reasonable as well since besides setting the value for the Oracle block size, you don’t have option to mention any other kind of block size as well.

So if you are setting a value of it, this should be applicable to all the database files. And this was the topic of discussion as well between me and few delegates! The answer of this doubt is a No actually! Oracle’s standard block size is applicable to only the datafiles. The redo log files and the control files use a different block size. Yes, the block size used by them is not shown in any of the views( things have changed a little from 11.1 onwards) so even if there would be any other value for the blocks, that is not visible. So let’s first check the size used in the datafiles. We shall check the values in 10.2 and 11.1 databases (10201, 11106) running on Windows XP Professional. First data files on 10g,

01 Connected to:
02   Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production
03
04 With the Partitioning, OLAP and Data Mining options
05
06
07 SQL> select file#,block_size from V$datafile;
08
09      FILE# BLOCK_SIZE
10   ---------- ----------
11
12          1       8192
13
14          2       8192
15
16          3       8192
17
18          4       8192
19
20          5       8192
21
22          6       8192
23
24          7       8192
25
26 SQL> sho parameter block_size
27
28 NAME                                 TYPE        VALUE
29   ------------------------------------ ----------- -----------
30
31 db_block_size                        integer     8192
32
33 SQL>

And now the same for 11.1,

01 Connected to:
02   Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
03
04 With the Partitioning, OLAP, Data Mining and Real Application Testing options
05
06
07 SQL> select file#, block_size from V$datafile;
08
09      FILE# BLOCK_SIZE
10   ---------- ----------
11
12          1       8192
13
14          2       8192
15
16          3       8192
17
18          4       8192
19
20          5       8192
21
22          6       8192
23
24          7       8192
25
26 7 rows selected.
27
28 SQL> sho parameter block_size
29
30 NAME                                 TYPE        VALUE
31   ------------------------------------ ----------- -------
32
33 db_block_size                        integer     8192


And this surely matches with what we have set in the parameter DB_BLOCK_SIZE!
Now, the issue is how do we check the block size used by the redo log files? In 10g, there is no provision given by oracle in the external view(s) to see this!

01 BANNER
02   ----------------------------------------------------------------
03
04 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
05
06 PL/SQL Release 10.2.0.1.0 - Production
07
08 CORE    10.2.0.1.0      Production
09
10 TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
11
12 NLSRTL Version 10.2.0.1.0 - Production
13
14
15 SQL> desc V$log;
16   Name                                      Null?    Type
17
18 ----------------------------------------- -------- --------------------
19
20 GROUP#                                             NUMBER
21
22 THREAD#                                            NUMBER
23
24 SEQUENCE#                                          NUMBER
25
26 BYTES                                              NUMBER
27
28 MEMBERS                                            NUMBER
29
30 ARCHIVED                                           VARCHAR2(3)
31
32 STATUS                                             VARCHAR2(16)
33
34 FIRST_CHANGE#                                      NUMBER
35
36 FIRST_TIME                                         DATE
37
38 SQL> desc V$logfile
39   Name                                      Null?    Type
40
41 ----------------------------------------- -------- --------------------
42
43 GROUP#                                             NUMBER
44
45 STATUS                                             VARCHAR2(7)
46
47 TYPE                                               VARCHAR2(7)
48
49 MEMBER                                             VARCHAR2(513)
50
51 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

But the size is indeed shown in an internal table, X$KCCRT( Kernel Cache Current Redo Thread). I am not going to show all the columns of this table but only that one which would be having the “log block size” .

01 SQL> select lebsz from X$kccle;
02
03
04      LEBSZ
05   ----------
06
07        512
08
09        512
10
11        512

Yes, this is the size of the redo log block in which the LGWR writes into the log files. So this is indeed not true that the standard block size is applicable to the redo log files. This size basically is picked by Oracle based on the physical block size given by the media. We have the 512byte of the physical block size available and that’s the same is used by the redo log files. Still, you should check this on your own box! Surely enough, there is no need to change this size or play around with it! In 11g(11.1), this is the same output that you would be getting from oracle as there is no change that’s there in 11.1 for this nomenclature. Here is an output from 11.1 system,

01 BANNER
02   -------------------------------------------------------------------------
03
04 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
05
06 PL/SQL Release 11.1.0.6.0 - Production
07
08 CORE    11.1.0.6.0      Production
09
10 TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
11
12 NLSRTL Version 11.1.0.6.0 - Production
13
14
15 SQL> select lebsz from x$kccle;
16
17      LEBSZ
18   ----------
19
20        512
21
22        512
23
24        512
25
26 SQL> desc V$log
27   Name                                      Null?    Type
28
29 ----------------------------------------- -------- ----------------------------
30
31 GROUP#                                             NUMBER
32
33 THREAD#                                            NUMBER
34
35 SEQUENCE#                                          NUMBER
36
37 BYTES                                              NUMBER
38
39 MEMBERS                                            NUMBER
40
41 ARCHIVED                                           VARCHAR2(3)
42
43 STATUS                                             VARCHAR2(16)
44
45 FIRST_CHANGE#                                      NUMBER
46
47 FIRST_TIME                                         DATE
48
49 SQL> desc V$logfile
50   Name                                      Null?    Type
51
52 ----------------------------------------- -------- ------------------------
53
54 GROUP#                                             NUMBER
55
56 STATUS                                             VARCHAR2(7)
57
58 TYPE                                               VARCHAR2(7)
59
60 MEMBER                                             VARCHAR2(513)
61
62 IS_RECOVERY_DEST_FILE                              VARCHAR2(3)

The output varies a little from 11.2 onwards where to check the redo log block, you won’t need to check any internal table but the same would be shown in the standard V$log. Since I don’t have 11.2 running with me on this machine so here is an excerpt from docs for the same,

http://download.oracle.com/docs/cd/E11882_01/server.112/e10820/dynviews_2030.htm#REFRN30127

You can see a new column sized called BLOCKSIZE which can have two values, 512 or 4096! Hang on a sec, two values? How that’s possible? The answer of that lies in a change that has started to come in the way our hard disks.

The standard physical sector size supported by the hard drives was always 512byte. This was( and still is) the same size of the block that was picked by redo log files as explained above. Since there are more bigger and faster media solutions available now, the hardware vendors have started moving from 512 byte physical block sized disks to 4kb sized physical block block sized disks! Surely enough, this would increase the capability of underlying systems to do a more larger chunk of IOs in both reading and writing. But for this, there remains one hurdle that if the oracle files are still going to be using the 512byte sector sized block, this optimization from the hard disk vendors won’t bear any fruit since the IO would be still limited to the underlying block size of the redo log file which would be still 512byte. To overcome this, from 11.2 onwards, Oracle supports the 4kb sector disk drives and offers two modes of the working, Emulation mode and Native mode for the block size support of the redo log files. You can read about it here,

http://download.oracle.com/docs/cd/E11882_01/server.112/e10881/chapter1.htm#FEATURENO08747

Oracle is capable to detect what’s the size of the physical block offered by the disk and accordingly, the redo log block size is picked up. And this was the right thing to do in the pastas well since there was no requirement to do any kind of tuning to this behavior as well as there was no other option available from the physical disk as well. But since now the vendors have started shipping disks with 4kb disks as well, so the support for the same must come both from Oracle and from the operating systems as well. Here is a support note from Microsoft about the same,

http://support.microsoft.com/kb/923332

The same support is offered by Oracle from 11.2 onwards where two new clauses are introduced for the same, sector_size and blocksize. The SECTOR_SIZE clause is added while you are working with the ASM(without ACFS) i.e. when you are creating a diskgroup, you can mention that what would be the size of the block used by that disk group. Oracle docs explain this in a good manner here,

http://download.oracle.com/docs/cd/E11882_01/server.112/e10500/asmdiskgrps.htm#OSTMG10203

The BLOCKSIZE support is offered from 11.2 while creating the log files. Now, you can mention that what should be the size of the redo log block on the underlying disk which supports either 4kb or 512 byte sized sector. If you are going to use 512 byte sized sector on a 4kb sector disk, this would be a non-good approach actually. So if you do know that you have a disk supporting 4kb sized sector, its advisable to use the same while creating the redo log files as well. For the same, 11.2 docs have this section added,

http://download.oracle.com/docs/cd/E11882_01/server.112/e10595/onlineredo002.htm#ADMIN12891

Emulation mode is the mode where the physical block size offered by the vendor is 4kb but the sized used by the redo log files is still 512byte only. This would be considered as a logical size where 8 logical sectors of 512 byte would map to one physical sector of 512 byte. As I said above, this would be a non-good approach. The better option would be to use the same size at both disk and within the file. There is another type of the mode offered called Native mode where the logical and physical size of the sector would be the same.
Oracle recommends that the block size used by the redo logs should match with the physical sector and the size of the data block should be either equivalent of multiple of the physical sector size. This optimization does “not” get applied to the control files which won’t experience any chance in their working since they don’t use either the standard block size or the redo log block size. Didn’t I say so already ?

Unlike the redo log and data files, control files use a standard block size of 16kb irrespective of what is offered from the media. Again, this is not shown from the standard control file views so we need to go a step ahead and check the table, X$KCCCF( Kernel Cache Current Control File). Let’s check this table on both 10.2 and 11.1 versions,

01 SQL> select * from V$version;
02
03
04 BANNER
05   ----------------------------------------------------------------
06
07 Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod
08
09 PL/SQL Release 10.2.0.1.0 - Production
10
11 CORE    10.2.0.1.0      Production
12
13 TNS for 32-bit Windows: Version 10.2.0.1.0 - Production
14
15 NLSRTL Version 10.2.0.1.0 - Production
16
17 SQL> select cfbsz from X$kcccf;
18
19      CFBSZ
20   ----------
21
22      16384
23
24      16384
25
26      16384
27
28 SQL> sho parameter control
29
30 NAME                                 TYPE        VALUE
31   ------------------------------------ ----------- ---------------------
32
33 control_file_record_keep_time        integer     7
34
35 control_files                        string      E:\ORACLE\PRODUCT\10.2.0\ORADA
36
37                                                  TA\ORCL\CONTROL01.CTL, E:\ORAC
38
39                                                  LE\PRODUCT\10.2.0\ORADATA\ORCL
40
41                                                  \CONTROL02.CTL, E:\ORACLE\PROD
42
43                                                  UCT\10.2.0\ORADATA\ORCL\CONTRO
44
45                                                  L03.CTL

What we are seeing is that there are three control files in my db and all are using 16kb as the block size. Let’s check on 11.1 now,

01 SQL> select * from V$version;
02
03
04 BANNER
05   --------------------------------------------------------------------------------
06
07 Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
08
09 PL/SQL Release 11.1.0.6.0 - Production
10
11 CORE    11.1.0.6.0      Production
12
13 TNS for 32-bit Windows: Version 11.1.0.6.0 - Production
14
15 NLSRTL Version 11.1.0.6.0 - Production
16
17 SQL> select cfbsz from x$kcccf;
18
19      CFBSZ
20   ----------
21
22      16384
23
24      16384
25
26      16384
27
28 SQL> sho parameter control
29
30 NAME                                 TYPE        VALUE
31   ------------------------------------ ----------- ------------------------------
32
33 control_file_record_keep_time        integer     7
34
35 control_files                        string      E:\APP\ARISTADBA\ORADATA\ORCL1
36
37                                                  11G\CONTROL01.CTL, E:\APP\ARIS
38
39                                                  TADBA\ORADATA\ORCL111G\CONTROL
40
41                                                  02.CTL, E:\APP\ARISTADBA\ORADA
42
43                                                  TA\ORCL111G\CONTROL03.CTL
44
45 control_management_pack_access       string      DIAGNOSTIC+TUNING

Which shows the same output with 3 files and a 16kb block size! This is sort of hardcoded only and won’t change even if you are going to use a disk which supports variable sector sizes.

To conclude, its good to believe on rumors but its more better to check their authentication since 9/10 times, rumors are just what they are, rumors! Sounds like a great punch line right