7.3.3 Faster Smart Scans Using Column-level Checksum

Checksum computation and validation helps to detect errors that may happen during storage or retrieval of data. A checksum is computed when a piece of data is written to Exadata Flash Cache and the checksum is verified on a subsequent read.

Prior to the Oracle Exadata System Software release 19.1.0, a checksum was computed on a block on flash after it was read. This block usually contains multiple columns, but a typical Data Warehousing query only accesses a few columns of a table. In Oracle Exadata System Software release 19.1.0, the unit of checksum computation is reduced to a single column. A checksum is computed for each column and stored in the column compression unit (CU) header. This optimization is enabled only for In-memory Columnar format on Exadata Smart Flash Cache.

The key benefits of the column level checksum approach are:

• Selective checksum computation: When smart scan reads a flash block containing the columns of interest, checksum verification is performed only for those specific column CUs even though there are many other columns present in the cache line. This reduces the amount of data on which the checksum is performed resulting in lower CPU usage. For example, consider the predicate A < 5 and Z < 10. The flash block where column A resides could contain columns B, C, and D. However, because B, C, and D are not referenced in the query, checksums are not performed for B, C, and D.

• Just in time checksum computation: Checksums are performed only when a column is processed. For example, consider the predicate A < 5 and Z < 10. Checksum is computed on the column CU for column A and the predicates are evaluated. If there are no rows satisfying the predicate A < 5, then there is no reason to evaluate Z < 10. Checksum computation is not performed on the column CU for column Z. This can greatly reduce the amount of data on which the checksum is performed resulting in lower CPU usage.

This feature is automatically enabled when you configure the INMEMORY_SIZE database initialization parameter and upgrade to Oracle Exadata System Software release 19.1.0; no further configuration is required to use this feature.

See Offloading of Data Search and Retrieval Processing in Oracle Exadata System Software User's Guide for more information.

Minimum requirements:

• Oracle Exadata System Software release 19.1.0
• Exadata Smart Flash Cache
• Oracle Database In-Memory option