Oracle Database In-Memory BASE_LEVEL Feature available since 19.8.0

These are absolutely exciting news. I found this email from our PM for Oracle In-Memory, Andy Rivenes, in my inbox this morning. Andy explained that with the database Release Update 19.8.0 the In-Memory BASE_LEVEL feature is now available. And you don’t have to purchase an extra license for it.

What is Oracle In-Memory?

So many things have been written and published about the In-Memory option. I’d rather link to this White Paper here (Oracle Database In-Memory with Oracle 19c) and put some additional links at the end of the blog post.

What is Oracle In-Memory Base Level?

This features allows you to experiment with Oracle Database In-Memory features without purchasing the Oracle Database In-Memory option.

See the blog post by the In-Memory team about the feature:

But please note that there are a few restrictions:

  • The INMEMORY_SIZE you define cannot exceed 16 GB for a CDB (and of course for a non-CDB, too).
  • If you have an Oracle RAC environment, you can set it to 16 GB for each instance
  • You have no influence on the compression level as it is set for all objects and columns is automatically and transparently to QUERY LOW

See the detailed license information below in the next paragraph for the fine print.

Is this true?

Yes, the Oracle Database 19c License Guide has been updated already. You need to search for “Database In-Memory Base Level” to find this:

Oracle Database In-Memory BASE_LEVEL Feature available since 19.8.0

Oracle Database In-Memory BASE_LEVEL Feature available since 19.8.0

Oracle Database 19c – License Guide (as of July 20, 2020)

What it doesn’t explicitly specify is the available just from Oracle 19.8 on. But typically the license guide does not list such restrictions as this is not a license but a feature availability restriction.

Please see also the announcement on the Oracle In-Memory blog by PM Andy Rivenes:

How to use the feature?

You need to set INMEMORY_FORCE=BASE_LEVEL and INMEMORY_SIZE=xGB (up to 16GB maximum). And once you restarted your database, you are ready to go.

Good news: Once you set INMEMORY_FORCE=BASE_LEVEL, you can’t increase INMEMORY_SIZE to more than 16GB. Hence, no accidental license violation can occur as long as INMEMORY_FORCE remains at BASE_LEVEL.

So let’s start with a quick test to demonstrate the usage. And I was curious also what happens to my TPCC artificail workload in the hands-on lab.

Something important upfront – don’t forget that COMPATIBLE must be at least at Oracle 12.1.0 as otherwise you will receive an ORA-922 when trying to load tables INMEMORY.

At first, I set the parameters and restart my database. In this case, I use the upgraded UPGR database in the Hands-On Lab – and I applied Oracle 19.8.0 to the home before of course.

alter system set inmemory_force=base_level scope=spfile;
alter system set inmemory_size=3G scope=spfile;
alter system set sga_target=4G scope=spfile;
shutdown immediate
startup

Next step is altering my tables:

ALTER TABLE TPCC.HISTORY INMEMORY PRIORITY HIGH;
ALTER TABLE TPCC.CUSTOMER INMEMORY PRIORITY HIGH;
ALTER TABLE TPCC.DISTRICT INMEMORY PRIORITY HIGH;
ALTER TABLE TPCC.ITEM INMEMORY PRIORITY HIGH;
ALTER TABLE TPCC.WAREHOUSE INMEMORY PRIORITY HIGH;
ALTER TABLE TPCC.STOCK INMEMORY PRIORITY HIGH;
ALTER TABLE TPCC.ORDERS INMEMORY PRIORITY HIGH;
-- ALTER TABLE TPCC.NEW_ORDER INMEMORY PRIORITY HIGH;
-- ALTER TABLE TPCC.ORDER_LINE INMEMORY PRIORITY HIGH;

Why did I comment out NEW_ORDER and ORDER_LINE? Unfortunately you can’t load these into the in-memory column store. Both contain IOTs – and IOTs are unfortunately not supported:
ORA-64358: in-memory column store feature not supported for IOTs

COLUMN table_name FORMAT A20

SELECT table_name,
       inmemory,
       inmemory_priority,
       inmemory_distribute,
       inmemory_compression,
       inmemory_duplicate  
FROM   dba_tables
WHERE  owner='TPCC'
ORDER BY table_name;

Afterwards I check whether they are now in-memory:

TABLE_NAME INMEMORY INMEMORY INMEMORY_DISTRI INMEMORY_COMPRESS INMEMORY_DUPL
-------------------- -------- -------- --------------- ----------------- -------------
CUSTOMER ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
DISTRICT ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
HISTORY ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
ITEM ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
NEW_ORDER DISABLED
ORDERS ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
ORDER_LINE DISABLED
STOCK ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE
WAREHOUSE ENABLED HIGH AUTO FOR QUERY LOW NO DUPLICATE

9 rows selected.

And further:

COLUMN segment_name FORMAT A20
select segment_name,inmemory_size,bytes_not_populated from v$im_segments

SEGMENT_NAME INMEMORY_SIZE BYTES_NOT_POPULATED
-------------------- ------------- -------------------
STOCK 104071168 0
HISTORY 4456448 0
DISTRICT 1310720 0
ITEM 9699328 0
ORDERS 2359296 0
CUSTOMER 68419584 0

Everything loaded into the In-Memory Column Store.

At this point, it is time to repeat my workload and check with a SPA run:

Oracle Database In-Memory BASE_LEVEL Feature available since 19.8.0

SQL Performance Analyzer comparison on ELAPSED_TIME before (11.2.0.4) to after (19.8.0 with In-Memory BASE_LEVEL)

Not bad 🙂

Try it out by yourself.

Annotation Sept 17. 2020

Based on customer feedback detecting that this can’t be set for 19.8.0 on Windows we double checked. And it looks like that the feature (tracked as bug 30927821) didn’t make it in 19.8.0 on Windows and HP-UX but will be there in 19.9.0 then. Sorry for the inconvenience.

More Information and Links

–Mike

Share this: