Exadata In-Memory Column Store and INMEMORY_FORCE on OCI

While testing the In-Memory feature extensions at the Exadata storage level, I thought I was going to hit a limitation that would prevent a full test of DBIM across the database server DRAM and the storage server flash. As early as version 12.1 of Exadata, HCC-compressed tables were stored in flash in a pure columnar format, and version 12.2 extends that format by rewriting those tables into Database In-Memory format. More recent versions take away the restriction to only reformat HCC tables.

Flash In-Memory Column Store Overview

When tables are stored in the Database In-Memory format in cell server flash storage, you now have available the same features as Database In-Memory such as the single-instruction SIMD vector processing methods when doing smart scans along with faster joins and aggregation. Think of the Database In-Memory column store expanding it’s footprint beyond the database server to flash in the cell servers. Here’s the diagram from the Oracle documentation that shows one picture explaining the concept better than writing 1000 words:

For very large databases on Exadata, having “only” 1.5 TB of DRAM on each database server might not be enough — so why not “extend” the in-memory features to use flash on the cell servers? it won’t be quite as fast as DRAM local to the DB server, but still much faster than even using the standard offloading operations of Exadata storage.

Since database version 19.8, the parameter INMEMORY_FORCE not only can be set to BASE_LEVEL but also can set to CELLMEMORY_LEVEL to enable and use the in-memory column store only in flash memory at the cell level if you also set INMEMORY_SIZE to 0. This combination can be useful if you don’t have enough memory at the DB level and don’t want the overhead of managing the in-memory column store at the DB level.

The problem I had was with part of the description of the INMEMORY_FORCE parameter when it is set to CELL_MEMORY_LEVEL (as of March 2022):

This was concerning particularly because I am primarily working in an OCI environment, and I didn’t think that a restriction like that would make sense. So I tried it out by checking some combinations of those parameters and a familiar data set.

Test Data and Query

To exercise the In-Memory features in cell server flash, I created and populated the tables from the Sample Star Schema Benchmark queries available for any Oracle database via Andy Rivenes’ GitHub. Here’s the query that joins the fact and dimension tables:

select
  d.d_year,
  c.c_nation,
  sum(lo_revenue - lo_supplycost) profit
from
  LINEORDER l,
  DATE_DIM d,
  PART p,
  SUPPLIER s,
  CUSTOMER c
where
  l.lo_orderdate = d.d_datekey
  and l.lo_partkey = p.p_partkey
  and l.lo_suppkey = s.s_suppkey
  and l.lo_custkey = c.c_custkey
  and s.s_region = 'AMERICA'
  and c.c_region = 'AMERICA'
group by
  d.d_year,
  c.c_nation
order by
  d.d_year,
  c.c_nation;

After the query runs, I’ll use a query against V$MYSTAT and V$STATNAME to see what the DB and cell layers are doing in terms of offloading and IM operations:

select statistic#,v.name,to_char(value,'999,999,999,999,999') value
from v$mystat
   join v$statname v
      using(statistic#)
where 
   v.name like '%vector group by%'
   or v.name like 'key vectors%'
   or v.name like '%columnar cache%'
   or v.name like '%for IM%'
   or v.name like '%IM scan%'
order by statistic#;

Parameter Values During Testing

Before running the query I changed the value of INMEMORY_SIZE to 1520M and changed the tables’ attributes to INMEMORY MEMCOMPRESS FOR QUERY LOW. Next, I set INMEMORY_SIZE to 0 and INMEMORY_FORCE=CELLMEMORY_LEVEL to see if the cell servers would use the in-memory features in flash only, even though I was not using an Exadata on-premises.

INMEMORY_SIZE=1520M, INMEMORY_FORCE=DEFAULT, tables INMEMORY MEMCOMPRESS FOR QUERY LOW

Having the in-memory column store enabled at the database level with a size of 1520M and the tables enabled for in-memory, it is clear from the session statistics that all of the IM operations happen at the DB level, including the key vector creation and usage:

It was also clear that the IM table contents completely fit into the allocated 1520M of DRAM.

INMEMORY_SIZE=0, INMEMORY_FORCE=CELLMEMORY_LEVEL, tables INMEMORY MEMCOMPRESS FOR QUERY LOW

This was the “moment of truth”. Would the parameter setting of INMEMORY_FORCE=CELLMEMORY_LEVEL work in OCI? As the query against V$MYSTAT demonstrates, not only did the storage layer offload almost all of the I/O, but that is where the IM processing occurs.

Conclusions

The Oracle documentation has been known to be occasionally inaccurate, often due to features and capabilities of the Exadata platform being enhanced on a frequent basis, sometimes too fast for the documentation to keep up!

It was reassuring to know that I could leverage the in-memory functionality only at the cell level in flash, even though I was not using Exadata on-premises.