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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 |
Connected to: <br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Production <br />With the Partitioning, OLAP and Data Mining options<p></p> <p>SQL> select file#,block_size from  V$datafile; </p> <p>     FILE# BLOCK_SIZE <br />---------- ---------- <br />         1       8192 <br />         2       8192 <br />         3       8192 <br />         4       8192 <br />         5       8192 <br />         6       8192 <br />         7       8192</p> <p>SQL> sho parameter block_size </p> <p>NAME                                 TYPE        VALUE <br />------------------------------------ ----------- ----------- <br />db_block_size                        integer     8192 <br />SQL></p> |
And now the same for 11.1,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 |
Connected to: <br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production <br />With the Partitioning, OLAP, Data Mining and Real Application Testing options<p></p> <p>SQL> select file#, block_size from V$datafile; </p> <p>     FILE# BLOCK_SIZE <br />---------- ---------- <br />         1       8192 <br />         2       8192 <br />         3       8192 <br />         4       8192 <br />         5       8192 <br />         6       8192 <br />         7       8192 </p> <p>7 rows selected. </p> <p>SQL> sho parameter block_size </p> <p>NAME                                 TYPE        VALUE <br />------------------------------------ ----------- ------- <br />db_block_size                        integer     8192</p> <p> </p> |
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!
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
BANNER <br />---------------------------------------------------------------- <br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod <br />PL/SQL Release 10.2.0.1.0 - Production <br />CORE    10.2.0.1.0      Production <br />TNS for 32-bit Windows: Version 10.2.0.1.0 - Production <br />NLSRTL Version 10.2.0.1.0 - Production <p></p> <p>SQL> desc V$log; <br />Name                                      Null?    Type <br />----------------------------------------- -------- -------------------- <br />GROUP#                                             NUMBER <br />THREAD#                                            NUMBER <br />SEQUENCE#                                          NUMBER <br />BYTES                                              NUMBER <br />MEMBERS                                            NUMBER <br />ARCHIVED                                           VARCHAR2(3) <br />STATUS                                             VARCHAR2(16) <br />FIRST_CHANGE#                                      NUMBER <br />FIRST_TIME                                         DATE </p> <p>SQL> desc V$logfile <br />Name                                      Null?    Type <br />----------------------------------------- -------- -------------------- <br />GROUP#                                             NUMBER <br />STATUS                                             VARCHAR2(7) <br />TYPE                                               VARCHAR2(7) <br />MEMBER                                             VARCHAR2(513) <br />IS_RECOVERY_DEST_FILE                              VARCHAR2(3) </p> |
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” .
|
1 2 3 4 5 6 7 8 9 10 |
SQL> select lebsz from X$kccle; <p></p> <p>     LEBSZ <br />---------- <br />       512 <br />       512 <br />       512</p> |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
BANNER <br />-------------------------------------------------------------------------------- <br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production <br />PL/SQL Release 11.1.0.6.0 - Production <br />CORE    11.1.0.6.0      Production <br />TNS for 32-bit Windows: Version 11.1.0.6.0 - Production <br />NLSRTL Version 11.1.0.6.0 - Production <p></p> <p>SQL> select lebsz from x$kccle; </p> <p>     LEBSZ <br />---------- <br />       512 <br />       512 <br />       512 </p> <p>SQL> desc V$log <br />Name                                      Null?    Type <br />----------------------------------------- -------- ---------------------------- <br />GROUP#                                             NUMBER <br />THREAD#                                            NUMBER <br />SEQUENCE#                                          NUMBER <br />BYTES                                              NUMBER <br />MEMBERS                                            NUMBER <br />ARCHIVED                                           VARCHAR2(3) <br />STATUS                                             VARCHAR2(16) <br />FIRST_CHANGE#                                      NUMBER <br />FIRST_TIME                                         DATE</p> <p>SQL> desc V$logfile <br />Name                                      Null?    Type <br />----------------------------------------- -------- ---------------------------- <br />GROUP#                                             NUMBER <br />STATUS                                             VARCHAR2(7) <br />TYPE                                               VARCHAR2(7) <br />MEMBER                                             VARCHAR2(513) <br />IS_RECOVERY_DEST_FILE                              VARCHAR2(3)</p> |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 |
SQL> select * from V$version; <p></p> <p>BANNER <br />---------------------------------------------------------------- <br />Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod <br />PL/SQL Release 10.2.0.1.0 - Production <br />CORE    10.2.0.1.0      Production <br />TNS for 32-bit Windows: Version 10.2.0.1.0 - Production <br />NLSRTL Version 10.2.0.1.0 - Production </p> <p>SQL> select cfbsz from X$kcccf; </p> <p>     CFBSZ <br />---------- <br />     16384 <br />     16384 <br />     16384 </p> <p>SQL> sho parameter control </p> <p>NAME                                 TYPE        VALUE <br />------------------------------------ ----------- ------------------------------ <br />control_file_record_keep_time        integer     7 <br />control_files                        string      E:\ORACLE\PRODUCT\10.2.0\ORADA <br />                                                 TA\ORCL\CONTROL01.CTL, E:\ORAC <br />                                                 LE\PRODUCT\10.2.0\ORADATA\ORCL <br />                                                 \CONTROL02.CTL, E:\ORACLE\PROD <br />                                                 UCT\10.2.0\ORADATA\ORCL\CONTRO <br />                                                 L03.CTL</p> |
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,
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
SQL> select * from V$version; <p></p> <p>BANNER <br />-------------------------------------------------------------------------------- <br />Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production <br />PL/SQL Release 11.1.0.6.0 - Production <br />CORE    11.1.0.6.0      Production <br />TNS for 32-bit Windows: Version 11.1.0.6.0 - Production <br />NLSRTL Version 11.1.0.6.0 - Production </p> <p>SQL> select cfbsz from x$kcccf; </p> <p>     CFBSZ <br />---------- <br />     16384 <br />     16384 <br />     16384 </p> <p>SQL> sho parameter control </p> <p>NAME                                 TYPE        VALUE <br />------------------------------------ ----------- ------------------------------ <br />control_file_record_keep_time        integer     7 <br />control_files                        string      E:\APP\ARISTADBA\ORADATA\ORCL1 <br />                                                 11G\CONTROL01.CTL, E:\APP\ARIS <br />                                                 TADBA\ORADATA\ORCL111G\CONTROL <br />                                                 02.CTL, E:\APP\ARISTADBA\ORADA <br />                                                 TA\ORCL111G\CONTROL03.CTL <br />control_management_pack_access       string      DIAGNOSTIC+TUNING</p> <p> </p> |
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 🙂
Somewhat confused, If my physical block size is 4k supported by hard drives, Is redo log file block size is default assigned 4k when I install Oracle? Or We need to assigned block size 4k and then Can i assigned two kind of block size for redo log files ?
Azar,
Yes, Oracle detects the sector size automatically and accordingly the block size for the redo log files is set. With the recent 4kb sector size disks, you would have a choice of choosing either 512byte or 4kb block size. You are not supposed to choose multiple sizes for the redo log files but just one.
HTH
Aman….
Now I’m clear, good Article. Thanks for sharing.
Mohamed Azar
@Aman : I think the internal table X$kccle makes use of the hidden parameter _log_io_size as it specifies the max number of redo blocks to write out per IO request. So to change the block size for the logs , we can change the value of this hidden parameter .. But i guess that isn’t suggested in production environments !
SQL> select name,display_value from my_v$parameter_with_hidden where name like ‘%log_io%’;
NAME DISPLAY_VALUE
—————————————- ——————–
_log_io_size 0
_flashback_log_io_error_behavior 0
**The _log_io_size parameter is not 0, it just reports as zero because it has not been explicitly set. This is the case with many of the hidden parameters.
Tell me if I am wrong on this !
Thanks Azar!
Parul,
The parameter doesn’t represent the number of blocks but the IO limit when the LGWR would start writing and is set , by default , to 1/3rd of the log buffer. The size of the log block is picked up from the physical sector by Oracle. In general, the parameter setting is done by the size of the log buffer/logblock size. Setting this parameter won’t change the size of the blocks of the redo log files.
Aman….
Excellent article, thanks for taking the time to research it.
Jared,
Thanks a bunch for the kind words and also for stopping by here 🙂 .
Cheers
Aman….
Thanks Aman for correcting me !! 🙂
Hi Aman,
Nice one and very interesting topic.Thanks for sharing.
Regards,
Anand
🙂 Thanks for the kind words sir!
Aman….
Great find, thanks.
Mdinh,
Thanks a bunch for the kind words and for stopping by here 🙂 .
Cheers
Aman….
Great, thanx
D Singh,
Thanks !
Cheers
Aman….
Good post Aman, helped to learn a new thing.
Rgds
Thanks for stopping by here on my blog Suman and for kind words. If I am correct, you are the same Suman from OTN forums right 🙂 ?
Regards
Aman….
[…] To read more on this, refer to below link http://blog.aristadba.com/?p=124 […]
Hi Aman,
Thanks for sharing your valuable thought on the topic. I have some question in my mind(may be misinterpreting whatever you said).
1. When you said: this is indeed not true that the standard block size is applicable to the redo log files.
It’s true. The standard block size is only applicable to system tablespace. Other files may have different blocksize.
2. When you said: This size basically is picked by Oracle based on the physical block size given by the media.
It’s true, but only for oracle governed file system or RAW devices(non-cooked file system). Other filesystems are governed by OS and it’s responsibility of filesystem to pick a blocksize not of Oracle.
3. When you said: We have the 512byte of the physical block size available and that’s the same is used by the redo log files.
This is purely platform specific. It differs from platform to platform.
http://forums.oracle.com/forums/thread.jspa?threadID=557236
4. When you said: 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” .
If you say something is internal, it has been kept internal purposefully. I searched the table on metalink and resultant notes were tagged with ‘Internal Only’, so no access to customers.
I personally feel that if an undocumented table/parameter is mentioned on forums or blogs, it should come with warning and consequences of using the same. The x$’s can have pathetic effect on production systems.
http://www.jlcomp.demon.co.uk/kiddy_scripts.html#_Always_be_cautious_about_x$_and_v$
5. Sirji, the above reference links for 11.2 are not working for me. May be because I am too late and oracle has removed them.
With best regards,
S.K.
Congratulations Aman for being beyond Steve Adams http://www.ixora.com.au/q+a/0102/02112927.htm
http://download.oracle.com/docs/cd/E11882_01/server.112/e17120/onlineredo002.htm#ADMIN12891 is Planning the Block Size of Redo Log Files
http://download.oracle.com/docs/cd/E11882_01/server.112/e16102/asmdiskgrps.htm#OSTMG10203 is Specifying the Sector Size for Drives
http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/dynviews_2031.htm#REFRN30127 is v$log in the reference manual
Aman, looks like this means the doc ID’s change, so I guess we need the book, chapter and heading every time we quote the docs. Sheesh.
Joel,
Thanks so much for stopping by here. I am really so happy to see you here, I mean it!
No way Joel. Steve is one of those gurus whose work is too much good and I am too small in front of him. I may have mentioned one thing which is different from what he has said but there are numerous things which I have learnt from his work, book, site and he, you, Jonathan, Tom, Hans (the list is too big) are the people whom I follow all the time and will continue to do so always.
I guess, there was some changes which has been done in the recent times. There were couple of OTN links too which are not working now and the same I have seen for the documentation too. Thanks so much for the updated doc links 🙂 .
Once again, thanks so much for stopping by here.
Regards
Aman….
Hi Santosh,
1. When you said: this is indeed not true that the standard block size is applicable to the redo log files.
It’s true. The standard block size is only applicable to system tablespace. Other files may have different blocksize.
Santosh, I believe we are talking about two different things. What you have mentioned is the Data block size for which its true that its applicable to System tablespae without change and can be changed for the non-system tablespaces. But my statement was for the redo log files for which this is not applicable. They don’t use the Data block .
2. When you said: This size basically is picked by Oracle based on the physical block size given by the media.
It’s true, but only for oracle governed file system or RAW devices(non-cooked file system). Other filesystems are governed by OS and it’s responsibility of filesystem to pick a blocksize not of Oracle
Hmm I am not sure that I totally did parse the statement. There is no Oracle governed file system AFAIK. If you meant ASM, its a volume manager which does manages the physical file extents but even this also creates a logical entry only which is called Allocation Unit(AU) . The physical block size ( sector size) , for most of the disks, is still 512bytes only and is applicable to ASM as well. Raw devices doesn’t have a file system so they are limited by the character size which is again 512 bytes only. Yes, may be when used by a volume manager, the term may change but I am not sure about it. I didn’t mention that oracle chooses the block size but it just picks up what is already used. Did I miss something? Please let me know if I have stated something wrong.
3. When you said: We have the 512byte of the physical block size available and that’s the same is used by the redo log files.
This is purely platform specific. It differs from platform to platform.
http://forums.oracle.com/forums/thread.jspa?threadID=557236
Agreed! 512bytes is not a global size and can vary. Thanks so much for pointing it out 🙂 .
4. When you said: 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” .
If you say something is internal, it has been kept internal purposefully. I searched the table on metalink and resultant notes were tagged with ‘Internal Only’, so no access to customers.
I personally feel that if an undocumented table/parameter is mentioned on forums or blogs, it should come with warning and consequences of using the same. The x$’s can have pathetic effect on production systems.
http://www.jlcomp.demon.co.uk/kiddy_scripts.html#_Always_be_cautious_about_x$_and_v$
Though I agree with all what you said but there is a lot which is not so clear in the “public” information that’s out there. I have been in the sessions meeting a lot of guys, gals who work with oracle db and despite of my very limited knowledge, I see some very basic things being wrongly interpreted by them. And most of that information is correctly shown in the internal tables, parameters. I don’t ever advise anyone to dig deeper into these internals as long as they don’t know what one is not sure what he is looking for and why? I haven’t mentioned the warning that you have mentioned but thanks for pointing it out. I shall put a sticky sort of note for it rather than mentioning it in every post.
. Sirji, the above reference links for 11.2 are not working for me. May be because I am too late and oracle has removed them.
I believe that there are link upgrade done by Oracle. I am not sure that I can keep a check on the such upgrades so the best is to search over Tahiti 🙂 . Sorry for being so lazy 🙂 .
Thanks so much for stopping by here Santosh sir g!
Regards
Aman….
@@There is no Oracle governed file system AFAIK. If you meant ASM, its a volume manager which does manages the physical file extents but even this also creates a logical entry only which is called Allocation Unit(AU) .
Just to check my understanding:
Can we consider ACFS and OCFS as Oracle’s file system.