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