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
    EXTENT MANAGEMENT LOCAL UNIFORM SIZE 1M;

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

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

List of “hiddens” for inmemory option

NAME
VALUE
DEFLT
TYPE
DESCRIPTION
_inmemory_repopulate_threshold_blocks TRUE number In-memory repopulate threshold number of modified blocks
_inmemory_repopulate_threshold_rows TRUE number In-memory repopulate threshold number of modified rows
_inmemory_fs_blk_inv_blkcnt TRUE boolean in-memory faststart CU invalidation threshold(blocks)
_inmemory_repopulate_threshold_mintime 0 TRUE number In-memory repopulate minimum interval (millisec)
_inmemory_imcu_target_bytes 0 TRUE number IMCU target size in bytes
_inmemory_repopulate_threshold_scans 0 TRUE number In-memory repopulate threshold number of scans
_inmemory_min_ima_defersize 0 TRUE 6 Defer in-memory area allocation beyond this size
_inmemory_jscan 0 TRUE number inmemory jscan enable
_inmemory_query_check 0 TRUE number In-memory query checking
_inmemory_journal_check 0 TRUE number Depending on value does one of the DML verifications
_inmemory_prepopulate_fg 0 TRUE number Force prepopulate of in-memory segment in foreground
_inmemory_imcu_source_extents 0 TRUE number number of source extents per IMCU
_inmemory_txn_checksum 0 TRUE number checksum for SMUs and private journals
_optimizer_inmemory_quotient 0 TRUE number in-memory quotient (% of rows in in-memory format)
_inmemory_imcu_source_blocks 0 TRUE number number of source blocks per IMCU
_inmemory_analyzer_optimize_for 0 TRUE number inmemory analyzer optimize for
_inmemory_trickle_repopulate_threshold_dirty_ratio 0 TRUE number IMCO Trickle Repopulate threshold dirty ratio
_inmemory_strdlxid_timeout 0 TRUE number max time to determine straddling transactions
_inmemory_segment_populate_verify 0 TRUE number In-memory segment populate verification
_inmemory_dbg_scan 0 TRUE number In-memory scan debugging
_inmemory_trickle_repopulate_fg 0 TRUE number Trickle Repopulate in the Foreground
_inmemory_test_verification 0 TRUE number In-memory verification testing
_inmemory_log_level 1 TRUE number in-memory log level
_inmemory_enable_population_verify 1 TRUE number verify in-memory population
_parallel_inmemory_time_unit 1 TRUE number unit of work used to derive the degree of parallelism for in-memory tables (in seconds)
_inmemory_repopulate_threshold_blocks_percent 10 TRUE number In-memory repopulate threshold blocks invalid percentage
_optimizer_inmemory_pruning_ratio_rows 100 TRUE number in-memory pruning ratio for # rows (% of rows remaining after pruning)
_inmemory_private_journal_quota 100 TRUE number quota for transaction in-memory private journals
_inmemory_pct_inv_blocks_invalidate_imcu 100 TRUE number In-memory percentage invalid blocks for IMCU invalidation
_inmemory_repopulate_priority_scale_factor 100 TRUE number In-memory repopulate priority threshold scale factor
_inmemory_repopulate_invalidate_rate_percent 100 TRUE number In-memory repopulate invalidate rate percent
_inmemory_buffer_waittime 100 TRUE number wait interval for one SMU or IMCU to be freed
_inmemory_cu_timeout 100 TRUE number maximum wait time for one IMCU to be freed
_inmemory_exclto_timeout 1000 TRUE number maximum wait time to pin SMU for cleanout
_inmemory_cudrop_timeout 1000 TRUE number maximum wait time for IMCU to be freed during drop
_inmemory_rows_check_interrupt 1000 TRUE number Number of rows buffered before interrupt check
_inmemory_imcu_target_rows 1048576 TRUE number IMCU target number of rows
_inmemory_imcu_source_minbytes 1048576 TRUE number number of minimum source bytes per IMCU
_inmemory_force_fs_tbs_size 1073741824 TRUE 6 in-memory faststart force tablespace size
_inmemory_imco_cycle 120 TRUE number IMCO cycle in seconds (sleep period)
_inmemory_private_journal_numgran 128 TRUE number Number of granules per HT node
_inmemory_imcu_source_analyze_bytes 134217728 TRUE number number of source analyze bytes per IMCU
_inmemory_pin_hist_mode 16 TRUE number settings for IM pinned buffer history
_inmemory_fs_blk_inv_blk_percent 20 TRUE number in-memory faststart CU invalidation threshold(blocks)
_inmemory_repopulate_priority_threshold_row 20 TRUE number In-memory repopulate priority threshold row
_inmemory_private_journal_sharedpool_quota 20 TRUE number quota for transaction in-memory objects
_inmemory_num_hash_latches 256 TRUE number Maximum number of latches for IM buffers
_inmemory_max_populate_retry 3 TRUE number IM populate maximum number of retry
_inmemory_64k_percent 30 TRUE number percentage of in-memory area for 64k pools
_inmemory_distribute_ondemand_timeout 300 TRUE number On demand timeout for redistribute
_inmemory_trickle_repopulate_min_interval 300 TRUE number IMCO Trickle Repopulate Interval
_inmemory_distribute_timeout 300 TRUE number If true, enable auto distribute with 2safe
_inmemory_max_queued_tasks 304 TRUE number Maximum queued populating tasks on the auxiliary queue
_inmemory_repopulate_priority_threshold_block 40 TRUE number In-memory repopulate priority threshold block
_inmemory_repopulate_threshold_mintime_factor 5 TRUE number In-memory repopulate minimum interval (N*timetorepop)
_inmemory_repopulate_threshold_rows_percent 5 TRUE number In-memory repopulate threshold rows invalid percentage
_inmemory_scan_threshold_percent_noscan 50 TRUE number In-memory scan threshold maximum percent dirty no scan
_inmemory_pct_inv_rows_invalidate_imcu 50 TRUE number In-memory percentage invalid rows for IMCU invalidation
_inmemory_private_journal_numbkts 512 TRUE number Number of priv jrnl ht bkts
_inmemory_imcu_populate_minbytes 5242880 TRUE 6 minimum free space in IMA for populating IMCU
_inmemory_imcu_source_maxbytes 536870912 TRUE number IMCU maximum source size in bytes
_inmemory_pga_per_server 536870912 TRUE 6 minimum pga needed per inmemory populate server
_inmemory_servers_throttle_pgalim_percent 55 TRUE number In-memory populate servers throttling pga limit percentage
_inmemory_populate_wait_max 600 TRUE number maximum wait time in seconds for segment populate
_inmemory_small_segment_threshold 65536 TRUE 6 In-memory small segment threshold (must be larger for in-memory)
_inmemory_imcu_target_maxrows 8388608 TRUE number IMCU maximum target number of rows
_inmemory_default_flags 8459 TRUE number Default flags based on inmemory_clause_default
_parallel_inmemory_min_time_threshold AUTO TRUE string threshold above which a plan is a candidate for parallelization for in-memory tables (in seconds)
_inmemory_checksum FALSE TRUE boolean If true, checksums in-memory area to detect stray writes
_inmemory_enable_stat_alert FALSE TRUE boolean dump in-memory stats in alert log file
_inmemory_check_protect FALSE TRUE boolean If true, marks in-memory area read only to prevent stray writes
_inmemory_default_new FALSE TRUE boolean Force in-memory on new tables
_inmemory_scan_override FALSE TRUE boolean In-memory scan override
_inmemory_suppress_vsga_ima FALSE TRUE boolean Suppress inmemory area in v$sga
_inmemory_fs_raise_error FALSE TRUE boolean in-memory faststart raise error
_inmemory_populate_fg FALSE TRUE boolean populate in foreground
_inmemory_journal_row_logging FALSE TRUE boolean If true, log the entire row into the in-memory journal
_inmemory_autodist_2safe FALSE TRUE boolean If true, enable auto distribute with 2safe
_inmemory_populate_wait FALSE TRUE boolean wait for population to complete
_inmemory_fs_enable FALSE TRUE boolean in-memory faststart enable
_inmemory_lock_for_smucreate FALSE TRUE boolean take object lock during smu creation
_inmemory_force_non_engineered FALSE TRUE boolean force non-engineered systems in-memory behavior on RAC
_inmemory_validate_fetch FALSE TRUE boolean If true, validate single-row fetch between in-memory and disk
_inmemory_fs_verify FALSE TRUE boolean in-memory faststart verify
_inmemory_repopulate_disable FALSE TRUE boolean disable In-memory repopulate
_inmemory_query_fetch_by_rowid FALSE TRUE boolean In-memory fetch-by-rowid enabled
_inmemory_enable_sys FALSE TRUE boolean enable in-memory on system tablespace with sys user
_inmemory_incremental_repopulation FALSE TRUE boolean If true, incremental repopulation of IMCU will be attempted
_inmemory_check_prot_meta FALSE TRUE boolean If true, marks SMU area read only to prevent stray writes
_inmemory_fs_nodml FALSE TRUE boolean in-memory faststart assumes no dmls while populating
_inmemory_force_fs FALSE TRUE boolean in-memory faststart force
_inmemory_pruning ON TRUE string In-memory pruning
_inmemory_force_fs_tbs SYSAUX TRUE string in-memory faststart force tablespace
_optimizer_inmemory_table_expansion TRUE FALSE boolean optimizer in-memory awareness for table expansion
_optimizer_inmemory_bloom_filter TRUE FALSE boolean controls serial bloom filter for in-memory tables
_optimizer_inmemory_cluster_aware_dop TRUE FALSE boolean Affinitize DOP for inmemory objects
_optimizer_inmemory_gen_pushable_preds TRUE FALSE boolean optimizer generate pushable predicates for in-memory
_optimizer_inmemory_minmax_pruning TRUE FALSE boolean controls use of min/max pruning for costing in-memory tables
_inmemory_auto_distribute TRUE TRUE boolean If true, enable auto distribute
_inmemory_fs_enable_blk_lvl_inv TRUE TRUE boolean in-memory faststart enable block level invalidation
_inmemory_memprot TRUE TRUE boolean enable or disable memory protection for in-memory
_inmemory_imcu_align TRUE TRUE boolean Enforce 8M IMCU alignment
_inmemory_invalidate_cursors TRUE TRUE boolean In-memory populate enable cursor invalidations
_inmemory_prepopulate TRUE TRUE boolean Enable inmemory populate by IMCO
_optimizer_inmemory_access_path TRUE FALSE boolean optimizer access path costing for in-memory
_optimizer_inmemory_autodop TRUE FALSE boolean optimizer autoDOP costing for in-memory
_inmemory_query_scan TRUE TRUE boolean In-memory scan enabled
_inmemory_trickle_repopulate TRUE TRUE boolean Enable inmemory trickle repopulate