DBAs are always concerned about the performance impact of features like db_block_checksum or db_block_checking. Cary Millsap told me an story that several years ago, he met a lot of people who had turned off redo logging with an underscore parameter. The performance they’d get from doing this would set the expectation level in their mind, which would cause them to resist (strenuously!) any notion of switching this [now horribly expensive] logging back on. Of course, it makes you wish that it weren’t even a parameter.
Cary believes that the right analysis is to think clearly about risk. Risk is a “soft” word in most people’s minds, but in finance they teach that risk is quantifiable as a probability distribution. For example, you know every day that it’s possible for a disk drive in your system to go bad. The risk of that happening can actually be calculated. For disks, it’s not too difficult, because vendors do those calculations (MTTF) for us. The probability that you’ll wish you had set db_block_checksum enabled today is probably more difficult to compute.
From a psychology perspective, DBAs would be happier if their systems were set to full or typical values to begin with. Then in response to the question, “Would you like us to remove your safety net in exchange for going between 1% and 10% faster? Here’s the horror you might face if we do it…” I’d wager that most people would say no, thank you. They will react emotionally to the idea of their safety net being taken away.
But with the baseline of its being turned off to begin with, the question is “Would you like us to install a safety net in exchange for slowing your system down between 1% and 10%? Here’s the horror you might face if we don’t…” I’d wager that most people would answer no, thank you (the opposite verdict!), because they will react emotionally to the idea of their performance being taken away. If they had been burned by block corruptions before (which is me case because I worked for corruption team at Oracle Support), then I’m going to favor the safety net argument.
But now the question is: “if I’ve never faced a corruption and I have my disks mirrored and I have a good database backup strategy, why do I need to set these kind of parameters that will impact my performance?” The answer is “Because in other case, we can not be 100% sure that Oracle is reliable to detect the corruption and could provide you wrong information and (the worst) you wont realise!”
By default, Oracle has two internal mechanisms to validate the health of a block from physical structure point of view:
- At block tail, Oracle stores some information that must match some information stored at the header of the block. In case these two pieces do not match, the block is reported as FRACTURED when it is validated. ORA-1578 error is reported if this problem is found. This validation cannot be disabled. You can see a good explanation for fractured blocks in MOS.
- The other mechanism enabled by default is DB_BLOCK_CHECKSUM. In this case, DBWr and the direct loader calculate a checksum (a number calculated from all the bytes stored in the block) and store it in the cache header of every data block when writing it to disk. Checksums are recalculated when blocks are read and compared with the information at the header. In case they do not match, block is reported as CHECKSUM corrupted and ORA-1578 error is reported at client side.
This DB_BLOCK_CHECKSUM functionality can be disabled in order to improve performance between 1% and 5% for everything but SYSTEM tablespace but, are you able to assume the risk?
Lets try to see an example and simulate a hardware failure in the middle of a block when DB_BLOCK_CHECKSUM is disabled
- We identify the rows we have stored in a row, in this case file 5 block 206. This is an 8k block tablespace.
SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA'; EMPLOYEE_ID SALARY ----------- ---------- 100 24000 101 17000 102 17000 103 9000 104 6000 105 4800 106 4800 107 4200 108 12008 109 9000 110 8200 ... ... 98 rows selected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down.
- Now, we take a copy of a piece of the block storing these rows (512 bytes)
[oracle@oel6-121 DBTEST]$dd if=example01.dbf of=part02.dbf bs=512 skip=3299 count=1
SQL> startup ORACLE instance started. Total System Global Area 2499805184 bytes Fixed Size 2927480 bytes Variable Size 671089800 bytes Database Buffers 1811939328 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> update hr.employees set salary=salary+1 where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA'; 98 rows updated. SQL> commit; Commit complete. SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA'; EMPLOYEE_ID SALARY ----------- ---------- 100 24001 101 17001 102 17001 103 9001 104 6001 105 4801 106 4801 107 4201 108 12009 109 9001 110 8201 ... ... 177 8401 178 7001 179 6201 180 3201 181 3101 182 2501 183 2801 184 4201 185 4101 186 3401 187 3001 188 3801 189 3601 190 2901 191 2501 192 4001 193 3901 194 3201 195 2801 196 3101 197 3001 98 rows selected. SQL> shutdown immediate Database closed. Database dismounted. ORACLE instance shut down. SQL>
- So far so good, but now lets simulate the problem putting back the block piece taken before the UPDATE into the middle of the updated block.
[oracle@oel6-121 DBTEST]$ dd if=example01.dbf of=part01.dbf bs=512 count=3299 3299+0 records in 3299+0 records out 1689088 bytes (1.7 MB) copied, 0.01112 s, 152 MB/s [oracle@oel6-121 DBTEST]$ dd if=example01.dbf of=part03.dbf bs=512 skip=3300 2543916+0 records in 2543916+0 records out 1302484992 bytes (1.3 GB) copied, 7.75558 s, 168 MB/s
[oracle@oel6-121 DBTEST]$ cat part01.dbf part02.dbf part03.dbf >file.dbf [oracle@oel6-121 DBTEST]$ rm example01.dbf [oracle@oel6-121 DBTEST]$ mv file.dbf example01.dbf
SQL> startup ORACLE instance started. Total System Global Area 2499805184 bytes Fixed Size 2927480 bytes Variable Size 671089800 bytes Database Buffers 1811939328 bytes Redo Buffers 13848576 bytes Database mounted. Database opened. SQL> select employee_id, salary from hr.employees where rowid>'AAAWZpAAKAAAADNCcP' and rowid<'AAAWZpAAKAAAADPAAA'; EMPLOYEE_ID SALARY ----------- ---------- 100 24001 101 17001 102 17001 103 9001 104 6001 105 4801 106 4801 107 4201 108 12009 109 9001 110 8201 ... ... 177 8401 178 7001 179 6201 180 3201 181 3101 182 2501 183 2801 191 192 4001 193 3901 194 3201 195 2801 196 3101 197 3001 91 rows selected.
- We have lost 7 rows and we have 1 row with no salary…..and there is no error anywhere!!!
In case, if DB_BLOCK_CHECKSUM had been enabled, we had seen ORA-1578 error and we could restore the block from a backup.
In summary, as my colleague Gabriel Alonso (a.k.a Gaby) says, disabling DB_BLOCK_CHECKSUM is like trying to land a plane without any instrumentation at night in the middle of a hurricane – at least risky.
David Loinaz 
Awesome thrilling article. Very well explained.
LikeLiked by 1 person
Hello David Loinaz,
Thank you for sharing this. It is such great and informative.
I just wonder whether this tablespace is ASSM or not. There are three of 512-byte block skip from 3296 to 3299. What are they skipped for?
Best regards,
Bunditj
LikeLiked by 1 person
This is ASSM tablespace indeed. The reason for going to position 3299 and not to 3296 is because I would like to corrupt a piece in the middle of the block. Block 206 goes from 3296 to 3312. If I do the same for 3296 or 3312, it will affect the block header or the tail piece. If so, header will not match the tail of the block and ORA-1578 will be reported (fractured block). That is why I went to 3299.
LikeLike
Nice .Well Explained .
LikeLike