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.

Photo by Michael Dziedzic on Unsplash

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:

• Oracle Database In-Memory Base Level 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 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:

• https://blogs.oracle.com/in-memory/base-level-198-ru

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;
startup force

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

Afterwards I check whether they are now in-memory:

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;

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:

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

• Oracle 19c License Guide (scroll down to “Database In-Memory Base Level”)
• Parameter: INMEMORY_SIZE
• White Paper: Oracle Database In-Memory with Oracle 19c
• Oracle Blogs: Oracle Database In-Memory Base Level Feature
• Oracle Blogs: Announcement of availability Base Level Feature in Oracle 19.8.0

–Mike