AristaDBA’s Oracle Blog….

Some times, there is an assumption about things. And as with assumption always, its mostly not true. Still, we do believe on those assumptions and spread them as well , not caring that it may happen that the things are not the way you know them or have read them anywhere! I had a similar sort of discussion few days back with some guys and one of the discussions did end up  as a matter of small confusion. This small note is about that confusion’s answer!

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,

Connected to:
  Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production 

With the Partitioning, OLAP and Data Mining options


SQL> select file#,block_size from  V$datafile; 

     FILE# BLOCK_SIZE
  ---------- ---------- 

         1       8192 

         2       8192 

         3       8192 

         4       8192 

         5       8192 

         6       8192 

         7       8192

SQL> sho parameter block_size 

NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ----------- 

db_block_size                        integer     8192 

SQL>

And now the same for 11.1,

Connected to:
  Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 

With the Partitioning, OLAP, Data Mining and Real Application Testing options


SQL> select file#, block_size from V$datafile; 

     FILE# BLOCK_SIZE
  ---------- ---------- 

         1       8192 

         2       8192 

         3       8192 

         4       8192 

         5       8192 

         6       8192 

         7       8192 

7 rows selected. 

SQL> sho parameter block_size 

NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------- 

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!

BANNER
  ---------------------------------------------------------------- 

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod 

PL/SQL Release 10.2.0.1.0 - Production 

CORE    10.2.0.1.0      Production 

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production 

NLSRTL Version 10.2.0.1.0 - Production


SQL> desc V$log;
  Name                                      Null?    Type 

----------------------------------------- -------- -------------------- 

GROUP#                                             NUMBER 

THREAD#                                            NUMBER 

SEQUENCE#                                          NUMBER 

BYTES                                              NUMBER 

MEMBERS                                            NUMBER 

ARCHIVED                                           VARCHAR2(3) 

STATUS                                             VARCHAR2(16) 

FIRST_CHANGE#                                      NUMBER 

FIRST_TIME                                         DATE 

SQL> desc V$logfile
  Name                                      Null?    Type 

----------------------------------------- -------- -------------------- 

GROUP#                                             NUMBER 

STATUS                                             VARCHAR2(7) 

TYPE                                               VARCHAR2(7) 

MEMBER                                             VARCHAR2(513) 

IS_RECOVERY_DEST_FILE                              VARCHAR2(3) 

SQL>

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” .

SQL> select lebsz from X$kccle;


     LEBSZ
  ---------- 

       512 

       512 

       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,

BANNER
  -------------------------------------------------------------------------------- 

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production 

PL/SQL Release 11.1.0.6.0 - Production 

CORE    11.1.0.6.0      Production 

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production 

NLSRTL Version 11.1.0.6.0 - Production


SQL> select lebsz from x$kccle; 

     LEBSZ
  ---------- 

       512 

       512 

       512 

SQL> desc V$log
  Name                                      Null?    Type 

----------------------------------------- -------- ---------------------------- 

GROUP#                                             NUMBER 

THREAD#                                            NUMBER 

SEQUENCE#                                          NUMBER 

BYTES                                              NUMBER 

MEMBERS                                            NUMBER 

ARCHIVED                                           VARCHAR2(3) 

STATUS                                             VARCHAR2(16) 

FIRST_CHANGE#                                      NUMBER 

FIRST_TIME                                         DATE

SQL> desc V$logfile
  Name                                      Null?    Type 

----------------------------------------- -------- ---------------------------- 

GROUP#                                             NUMBER 

STATUS                                             VARCHAR2(7) 

TYPE                                               VARCHAR2(7) 

MEMBER                                             VARCHAR2(513) 

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 physical 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 past as 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 4096 bytes. 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,

SQL> select * from V$version;


BANNER
  ----------------------------------------------------------------

Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

PL/SQL Release 10.2.0.1.0 - Production

CORE    10.2.0.1.0      Production

TNS for 32-bit Windows: Version 10.2.0.1.0 - Production

NLSRTL Version 10.2.0.1.0 - Production 

SQL> select cfbsz from X$kcccf; 

     CFBSZ
  ----------

     16384

     16384

     16384 

SQL> sho parameter control 

NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      E:\ORACLE\PRODUCT\10.2.0\ORADA

                                                 TA\ORCL\CONTROL01.CTL, E:\ORAC

                                                 LE\PRODUCT\10.2.0\ORADATA\ORCL

                                                 \CONTROL02.CTL, E:\ORACLE\PROD

                                                 UCT\10.2.0\ORADATA\ORCL\CONTRO

                                                 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,

SQL> select * from V$version;


BANNER
  --------------------------------------------------------------------------------

Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production

PL/SQL Release 11.1.0.6.0 - Production

CORE    11.1.0.6.0      Production

TNS for 32-bit Windows: Version 11.1.0.6.0 - Production

NLSRTL Version 11.1.0.6.0 - Production 

SQL> select cfbsz from x$kcccf; 

     CFBSZ
  ----------

     16384

     16384

     16384 

SQL> sho parameter control 

NAME                                 TYPE        VALUE
  ------------------------------------ ----------- ------------------------------

control_file_record_keep_time        integer     7

control_files                        string      E:\APP\ARISTADBA\ORADATA\ORCL1

                                                 11G\CONTROL01.CTL, E:\APP\ARIS

                                                 TADBA\ORADATA\ORCL111G\CONTROL

                                                 02.CTL, E:\APP\ARISTADBA\ORADA

                                                 TA\ORCL111G\CONTROL03.CTL

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 !

Special thanks to Jared for pointing and correcting a typo in the post