Effectiveness of CU compression and _inmemory_imcu_source_maxbytes

It seems quite obvious that effectiveness of compression depends on unit size considered for compression.
The amount of data loaded in-memory seems driven by _inmemory_imcu_source_maxbytes hidden parameter.
I decided to create a quite artificial example.

  1. Let’s create an uniform size TABLESPACE as:

    CREATE TABLESPACE DATA_U01 DATAFILE ‘/home/oracle/app/oracle/oradata/cdb1/orcl/DATA_U01_01.dbf’
    SIZE 500M

  2. Let’s create a segment with about 1M size:

    AS SELECT (1e11) + mod(rownum, 1*5e4) +1 id, (1e1) + mod(rownum, 1*5e4) id2
    FROM dual connect by level <=1*5e4

    The expression above creates 1M size table with fully unique values

  3. I’m going to play with the size of the unique part keeping the overall size  quite big  (40M)
  4. IM_compr_table
  5. A few remarks:
    If you have an unique part greater than a size of the segment you can’t expect effective compression.
    If you have an unique part greater than a size of “single” load event
    you can’t expect effective compression, it’s a quite natural hypothesis and I’m going to prove it as well.
  6. My parameterized CTAS expression looks like

    CREATE TABLE REMIDM.UNI2 TABLESPACE DATA_U01 AS select (1e11) + mod(rownum, &uq_size*5e4) +1 id, (1e1) + mod(rownum, &uq_size*5e4) id2 from dual connect by level <=40*5e4;

    So I do measures for 2 “_inmemory_imcu_source_maxbytes” sizes,
    i.e. 4M and 16M and read 40M segment with unique part varied from 1M to 8M, you can see results below:

  7. IM_compression
  8. I measure IM sizes as:

    select bytes/1024/1024 mbytes, inmemory_size/1024/1024 inmem from v$im_segments WHERE populate_status=’COMPLETED’;

  9. You can see that after “unique” part reach some value near the “_inmemory_imcu_source_maxbytes” size you stop with compression change at some “negative” compression value ~1.2 (50 M inmemory vs 40M on the disk).