Hello Nenad,
I think David Loinaz ( https://twitter.com/DavidLoinaz ) or Gabriel Alonso (
https://twitter.com/Gaalons0 ) are the go-to-guys for this question as they
deal / dealt with this kind of stuff all day :)
David has also written a more general blog post about it here but not
particular to your question about LOW / MEDIUM:
https://davidloinaz.wordpress.com/2016/02/24/db_block_checksum-and-risk-perception/
Best Regards
Stefan Koehler
Independent Oracle performance consultant and researcher
Website: http://www.soocs.de
Twitter: @OracleSK
Noveljic Nenad <nenad.noveljic@xxxxxxxxxxxx> hat am 29. November 2019 um--
10:49 geschrieben:
I’ve been always setting db_block_checking to FULL(TRUE). However, it wasn’t
until recently, that I measured a significant impact on DML performance when
db_block_checking is set to MEDIUM or FULL. Further, this degradation is
clearly more severe with larger block sizes.
This prompted me to think of other options. Now, I need your help in
understanding the risks associated with different settings.
First of all, I don’t want to use OFF – for obvious reasons.
Second, the difference between LOW and MEDIUM are “full semantic checks”, see
Oracle documentation [
https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DB_BLOCK_CHECKING.html#GUID-23700E5C-6BFC-48C2-9728-EB1F93F95DD6](https://docs.oracle.com/en/database/oracle/oracle-database/19/refrn/DB_BLOCK_CHECKING.html#GUID-23700E5C-6BFC-48C2-9728-EB1F93F95DD6)
.
What are these “full semantic checks” and what would be the risk of omitting
them (i.e., setting db_block_checking to LOW)?
Finally, the only difference between MEDIUM and FULL – according to the
documentation - is that the aforementioned “full semantic checks” aren’t
performed for the index blocks. A little side note, though: I measured a
significant DML performance difference between MEDIUM and FULL even on
non-indexed tables.
What do you think could happen in case of an unnoticed index block corruption
(that is, with MEDIUM)? Wrong results or some ORA-0600/ORA-07445, perhaps?
If you’re using anything less than FULL, could you explain your decision?
Best regards,
Nenad
https://nenadnoveljic.com/blog/
