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.
- Let’s create an uniform size TABLESPACE as:
CREATE TABLESPACE DATA_U01 DATAFILE ‘/home/oracle/app/oracle/oradata/cdb1/orcl/DATA_U01_01.dbf’
EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;
- Let’s create a segment with about 1M size:
CREATE TABLE UNI2 TABLESPACE DATA_U01
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
- I’m going to play with the size of the unique part keeping the overall size quite big (40M)
- 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.
- 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:
- I measure IM sizes as:
select bytes/1024/1024 mbytes, inmemory_size/1024/1024 inmem from v$im_segments WHERE populate_status=’COMPLETED’;
- 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).