Enabling Real-Time Analytics With Database In-Memory

  • October 30, 2017

Columnar Formats in Exadata Flash Cache

Andy Rivenes
Product Manager

With the introduction of Oracle Database 12c Release 2 it is now possible to take advantage of the Database In-Memory columnar format, and all of its ground-breaking scan performance, in the Exadata flash cache. Unfortunately there isn't much documentation on this feature (see Section 7.9 Enabling or Disabling In-Memory Columnar Caching on Storage Servers in the Oracle Exadata System Software User's Guide). The following is a description of the different columnar formats available in the Exadata flash cache and how it works with Database In-Memory.

Oracle Exadata Storage Server Software release introduced the ability to transform Hybrid Columnar Compressed (HCC) data into a pure columnar format in the flash cache. The process to accomplish this rearranges 1 MB worth of HCC data into a true columnar form and stores the page mapping in the flash cache's hash table to tell us where the columns are. It is not uncommon to see a savings of 50-80% disk I/O from the columnar cache and significant improvements to the wall clock for queries which are cell CPU bound[1].

Many people mistakenly think that this format is the same as the pure in-memory columnar format used by Database In-Memory (DBIM). This is not the case. In fact, the Database In-Memory columnar format was introduced in Oracle Exadata Storage Server Software, but not until release and is only used if the In-Memory column store has been enabled (i.e. inmemory_size is at least 100MB). If those conditions are met then for HCC objects only, the format used in the flash cache will be the same as those used for Database In-Memory. There is a two step process for this to happen. Initially, data from eligible scans will be cached in the columnar cache format so that the columnar format is available immediately. Then in the background the data will be rewritten into DBIM format. The background process runs at a lower priority so that the rewrites don’t interfere with the main workload. By default the DBIM compression level used is MEMCOMPRESS FOR CAPACITY LOW. The rewrite to true DBIM formats is done so that Smart Scans can take advantage of SIMD vector instructions (Single Instruction Multiple Data values), dictionary encoding, vector group by, and other enhancements only available to the Database In-Memory columnar format. This also ensures that the rest of the query processing on the DB servers fully utilize the columnar properties of the dataset.

With the release of Oracle Exadata System Software 18c (18.1.0) the limitation of only supporting HCC formatted data for the DBIM columnar format was removed and now uncompressed tables and OLTP compressed tables can also benefit from the DBIM columnar format. Again, the restriction remains that this only works if the In-Memory column store has been enabled, otherwise the format is used, and only for HCC objects.

Since all of this support is provided by the Oracle Exadata System Software, it is possible for both databases (requires a minimum software version of and the patch for bug 24521608) and databases to take advantage of these features.

One of the questions we're often asked is, "How do we take advantage of this new feature?" The good news is that you don't have to do anything to take advantage of it. It is done for you, automatically. Depending on the configuration of your environment, as detailed above, the data from eligible scans will be cached in the flash cache in one of the columnar formats. It is possible to change the compression used or to prevent segments from being eligible with the CELLMEMORY command detailed in Section 7.9 in the Oracle Exadata System Software User's Guide as described earlier. One of the other questions we have been asked is, "Will the columnar cached data consume all of the flash cache?" The answer is no. If there is no OLTP workload then DW workload, including columnar formatting, can consume 100% of the flash cache, but OLTP workload will push out the DW workload up to at least 50% of the size of the flash cache. This ensures that OLTP workload performance is not sacrificed for DW scans. Also, the DBIM format is used instead of caching the raw blocks so that there is no significant increase in flash usage. In 18.1 reformatting OLTP compressed blocks into DBIM format can significantly reduce the amount of flash needed as DBIM gets very good compression.

The bottom line is that In-Memory columnar format in Exadata flash cache is one more differentiator that makes Exadata the best platform for Database In-Memory and allows customers to economically support terabytes of columnar formatted data for high performance analytic processing.


[1] This description is taken from Roger Macnicol's blog description in "What's new in 12.2: CELLMEMORY Part 1"

Join the discussion

Comments ( 1 )
  • Prabhjot Singh Wednesday, March 4, 2020
    Is there a hint or session setting that will allow us to test same SQL with and without columnar format on flash cache?
Please enter your name.Please provide a valid email address.Please enter a comment.CAPTCHA challenge response provided was incorrect. Please try again.