Oracle Consulting Oracle Training Oracle Support Development
Home
Catalog
Oracle Books
SQL Server Books
IT Books
Job Interview Books
eBooks
Rampant Horse Books
911 Series
Pedagogue Books

Oracle Software
image
Write for Rampant
Publish with Rampant
Rampant News
Rampant Authors
Rampant Staff
 Phone
 800-766-1884
Oracle News
Oracle Forum
Oracle Tips
Articles by our Authors
Press Releases
SQL Server Books
image
image

Oracle 11g Books

Oracle tuning

Oracle training

Oracle support

Remote Oracle

STATSPACK Viewer

Privacy Policy

 

 
 

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

 

   

 Copyright © 1996 -2016 by Burleson. All rights reserved.


Oracle® is the registered trademark of Oracle Corporation. SQL Server® is the registered trademark of Microsoft Corporation. 
Many of the designations used by computer vendors to distinguish their products are claimed as Trademarks