Memoptimized Rowstore - Fast Ingest Updates

The Memoptimized Rowstore Fast Ingest feature was introduced in Oracle Database 19c and I blogged about it here back in April of 2019. Since then, there have been a couple of updates to the Fast Ingest feature that I wanted to share. Support for LOBs and encryption has been added starting in Oracle Database 19.16 and 21.3.

As a quick review, the Memoptimized Rowstore Fast Ingest feature is not meant to replace large volume data loads. That is still best handled with Direct Path loads. The Fast Ingest feature reduces the overhead of the insert transaction itself and is meant to support applications that generate lots of informational data that has important value in the aggregate but that doesn't necessarily require full ACID requirements. It achieves this by inserting data directly into the memory allocated from the large pool and then these inserts are written to disk asynchronously by background processes. Since the inserts are not protected by undo or redo it is not possible to rollback the inserts and should the instance go down before the data has been written to disk it will have to be re-inserted by the application. This architecture was created to achieve maximum ingest throughput and therefore bypasses the normal Oracle transaction processing. There are special "write" APIs that can be used to verify that the data was written to disk, and we will use these APIs later in a couple of examples.

Because of the specialized nature of the Fast Ingest feature there were a lot of limitations imposed on the type of data that was supported by Fast Ingest. Two of the biggest ones were support for LOBs and encryption. Starting in Oracle Database 19.16 and 21.3 these two limitations have been removed. I will show a couple of examples using Oracle Database 19.16 to illustrate how this works.

Using the same table structure as my previous blog I will change the TEST_COL column to a SecureFiles CLOB and test Fast Ingest. I should also note that LOB support is for inline LOBs only.

create table test_SF_fi (
  id number primary key,
  test_col CLOB )
  segment creation immediate
  tablespace TS_DATA
  LOB(test_col) STORE AS SECUREFILE(
    ENABLE STORAGE IN ROW
    NOCOMPRESS
    CACHE
  )
  memoptimize for write;

Once the table is created we can run a test Fast Ingest insert of two rows using the MEMOPTIMIZE_WRITE hint:

SQL> insert /*+ memoptimize_write */ into test_SF_fi values (1, 'test');

1 row created.

SQL> insert /*+ memoptimize_write */ into test_SF_fi values (2, 'test');

1 row created.

SQL> select /*+ seqid session rows written to large pool */ to_number(dbms_memoptimize.GET_WRITE_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID)
-----------------------------------------------
                                  1794603124645

SQL> select /*+ seqid rows written to disk */ to_number(dbms_memoptimize.GET_APPLY_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID)
-----------------------------------------------
                                              0

SQL> exec dbms_memoptimize.write_end;

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_session.sleep(10);

PL/SQL procedure successfully completed.

SQL> select /*+ seqid session rows written to large pool  */ to_number(dbms_memoptimize.GET_WRITE_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID)
-----------------------------------------------
                                  1794603124645

SQL> select /*+ seqid rows written to disk  */ to_number(dbms_memoptimize.GET_APPLY_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID)
-----------------------------------------------
                                  1794612887883

SQL>
SQL> select * from test_SF_fi;

              ID TEST_COL
---------------- ------------------------------------------
               1 test
               2 test

SQL>

A couple of things to note in this example. No commit was issued because commits are not supported for Fast Ingest. Also note the use of the MEMOPTIMIZE_WRITE hint.

I also added the DBMS_MEMOPTIMIZE write API calls to verify that the data inserted was persisted to disk:

1. The first call to DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID tells us the sequence associated with the rows that we inserted into memory in the large pool. The call to DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID tells us the highest sequence id for rows written to disk. Note that the first sequence id is greater than the second one. In fact, it is 0 because no data has yet been written to disk since the instance was started.
2. I then issued a DBMS_MEMOPTIMIZE.WRITE_END call because I didn't want to wait and wanted to tell the database to initiate a write immediately. You do not have to do this, but as I said, I was impatient.
3. Next I issued a sleep command to give the database time to write the data to disk.
4. I then reissued the sequence id calls to verify that the data had been written. In this step we see that now the apply highwater mark sequence is greater than our write highwater mark sequence.
5. And finally, I queried the data to see if it was there. The ultimate test of persistence.

If you found some of the mechanics a bit confusing the DBMS_MEMOPTIMIZE package is documented in the PL/SQL Packages and Types Reference.

We have now shown that Fast Ingest supports LOBs. What about encryption? The next example is slightly more complicated since we have to set up encryption as well as our Fast Ingest test. Encryption is supported with tablespace encryption, column level encryption and SecureFiles encryption. I took the easy way out and set up a tablespace with encryption support. If you are not familiar with database encryption you can learn how it works by taking a look at the Advanced Security Guide.

The following is the tablespace definition I used after setting up encryption in the database and opening the keystore.

CREATE TABLESPACE encrypted_ts
DATAFILE SIZE 128K
AUTOEXTEND ON NEXT 64K
ENCRYPTION USING 'AES256'
DEFAULT STORAGE(ENCRYPT);

For this example, we will again use the same simple table definition from the first blog post. However, note that I have switched to the encrypted tablespace:

create table test_encrypt_fi (
id number primary key,
test_col varchar2(15) )
segment creation immediate
tablespace encrypted_ts
memoptimize for write;

And now the test:

SQL> insert /*+ memoptimize_write */ into test_encrypt_fi values (1, 'test');

1 row created.

SQL> insert /*+ memoptimize_write */ into test_encrypt_fi values (2, 'test');

1 row created.

SQL> select /*+ seqid session rows written to large pool  */ to_number(dbms_memoptimize.GET_WRITE_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID)
-----------------------------------------------
                  2308680139702

SQL> select /*+ seqid rows written to disk  */ to_number(dbms_memoptimize.GET_APPLY_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID)
-----------------------------------------------
                  2308680136692

SQL> exec dbms_memoptimize.write_end;

PL/SQL procedure successfully completed.

SQL> exec sys.dbms_session.sleep(10);

PL/SQL procedure successfully completed.

SQL> select /*+ seqid session rows written to large pool  */ to_number(dbms_memoptimize.GET_WRITE_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_WRITE_HWM_SEQID)
-----------------------------------------------
                  2308680139702

SQL> select /*+ seqid rows written to disk  */ to_number(dbms_memoptimize.GET_APPLY_HWM_SEQID) from dual;

TO_NUMBER(DBMS_MEMOPTIMIZE.GET_APPLY_HWM_SEQID)
-----------------------------------------------
                  2308689712017

SQL> select * from test_encrypt_fi;

          ID TEST_COL
---------------- ---------------
           1 test
           2 test

SQL>

Fortunately, we have the same positive result. If for some reason the rows did not get inserted, perhaps from a constraint violation, the application insert will not fail. Recall that index operations and constraint checking is done only when the data is written from the fast ingest area in the large pool to disk. This is another reason it is important to code a check using the write and apply sequence ids to verify that the rows were indeed written.

Hopefully this post helps clarify the new LOB and encryption support that has been added to the Memoptimized Rowstore Fast Ingest feature.