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.
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.