19c New Features Memoptimized Rowstore Fast Ingest

Summary:

• Regular insert (per row commit): Elapsed: 14:47.37
• Regular insert (batch commit): Elapsed: 04:34.80
• Memoptimized Rowstore Fast Insert (per row commit): Elapsed: 04:27.56

Test Script

create table test_normal_ingest (
id number primary key,
test_col varchar2(15));

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ normal_write */ into test_normal_ingest values (i, 'test');
  commit;
end loop;
end;
/

create table test_normal_ingest_batch (
id number primary key,
test_col varchar2(15));

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ normal_write */ into test_normal_ingest_batch values (i, 'test');
end loop;
commit;
end;
/


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

declare
i number(9,0);
begin
for i in 1..10000000
loop
  insert /*+ MEMOPTIMIZE_WRITE */ into test_fast_ingest values (i, 'test');
  commit;
end loop;
end;
/

Test Output:

Regular insert (per row commit): Elapsed: 14:47.37

SQL> create table test_normal_ingest (
  2  id number primary key,
  3  test_col varchar2(15));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ normal_write */ into test_normal_ingest values (i, 'test');
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:14:47.37

Regular insert (batch commit): Elapsed: 04:34.80

SQL> create table test_normal_ingest_batch (
  2  id number primary key,
  3  test_col varchar2(15));

Table created.

Elapsed: 00:00:00.01
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ normal_write */ into test_normal_ingest_batch values (i, 'test');
  7  end loop;
  8  commit;
  9  end;
 10  /

PL/SQL procedure successfully completed.
Elapsed: 00:04:34.80

Memoptimized Rowstore Fast Insert (per row commit): Elapsed: 04:27.56

SQL> create table test_fast_ingest (
  2  id number primary key,
  3  test_col varchar2(15))
  4  segment creation immediate
  5  memoptimize for write;

Table created.

Elapsed: 00:00:00.28
SQL>
SQL> declare
  2  i number(9,0);
  3  begin
  4  for i in 1..10000000
  5  loop
  6    insert /*+ MEMOPTIMIZE_WRITE */ into test_fast_ingest values (i, 'test');
  7    commit;
  8  end loop;
  9  end;
 10  /

PL/SQL procedure successfully completed.

Elapsed: 00:04:27.56

DB Setting:

SQL> show parameter  large_pool_size                      big integer 1G

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
large_pool_size                      big integer 1G


SQL> select total_size,used_space,free_space from v$memoptimize_write_area;

TOTAL_SIZE USED_SPACE FREE_SPACE
---------- ---------- ----------
2154823680    1212896 2153610784




Reference: https://docs.oracle.com/en/database/oracle/oracle-database/19/tgdba/tuning-system-global-area.html#GUID-CFADC9EA-2E2F-4EBB-BA2C-3663291DCC25