14 In-Memory Initialization Parameters

Several initialization parameters control the behavior of the IM column store.

This chapter is a summary only. Oracle Database Reference contains complete information for all database views.

Table 14-1 Initialization Parameters Related to the IM Column Store

Initialization Parameter Description

INMEMORY_ADG_ENABLED

Indicates whether In-Memory for Oracle Active Data Guard is enabled (TRUE) or disabled (FALSE) on the standby database. The default is TRUE.

For Active Data Guard, media recovery must retrieve In-Memory objects when applying redo and invalidate the related objects after the query advance. This parameter controls whether media recovery performs the retrieving and invalidating.

You can only modify this system-level parameter when standby recovery is not running. If the standby database uses Oracle RAC, then this parameter must be set to the same value on every instance.

INMEMORY_AUTOMATIC_LEVEL

Automates the management of the IM column store by helping to ensure that the working data set is always in the IM column store.

You can set the following values:

  • OFF: This is the default. When this value is set, Automatic In-Memory is disabled. This value returns the IM column store to the behavior that existed before Oracle Database 18c. If you do not expect a stable working data set, then set the parameter to DISABLE.

  • LOW: When this value is set, the database evicts cold segments from the IM column store when it is under memory pressure.

  • MEDIUM: This level includes an additional optimization that ensures that any hot segment that was not populated because of memory pressure is populated first.

INMEMORY_CLAUSE_DEFAULT

Specifies a default IM column store clause for new tables and materialized views.

This parameter supports the following options:

  • To specify that there is no default IM column store clause for new tables and materialized views, leave this parameter unset or set it to an empty string. Setting this parameter to NO INMEMORY has the same effect as setting it to the default value (the empty string).

  • To specify that the clause is the default for all new tables and materialized views, set this parameter to a valid INMEMORY clause. The clause can include valid clauses for IM column store compression methods and data population options. The options are:

    • If the clause starts with INMEMORY, then all new tables and materialized views, including those without an INMEMORY clause, are populated in the IM column store.

    • If the clause omits INMEMORY, then it only applies to new tables and materialized views that are enabled for the IM column store with an INMEMORY clause during creation.

INMEMORY_EXPRESSIONS_USAGE

Controls which IM expressions are eligible to be populated in the IM column store.

This parameter supports the following options:

  • ENABLE

    The database populates both static and dynamic IM expressions into the IM column store. Setting this value increases the In-Memory footprint for some tables. This is the default.

  • STATIC_ONLY

    A static configuration enables the IM column store to cache OSON (binary JSON) columns, which are marked with an IS_JSON check constraint. Internally, an OSON column is a hidden virtual column named SYS_IME_OSON.

  • DYNAMIC_ONLY

    The database only populates frequently used or “hot” expressions that have been added to the table as SYS_IME hidden virtual columns. Setting this value increases the In-Memory footprint for some tables.

  • DISABLE

    The database does not populate any IM expressions, whether static or dynamic, into the IM column store.

Note:

IM expressions do not support NLS-dependent data types.

INMEMORY_FORCE

Enables or disables tables and materialized views for the IM column store.

This parameter supports the following options:

  • To allow the INMEMORY or NO INMEMORY attributes to determine population, set this parameter to DEFAULT (the default value). You can set this value dynamically.

  • To disable all tables and materialized views for the IM column store, set this parameter to OFF. You can set this value dynamically.

  • To enable the Database In-Memory Base Level, set this parameter to BASE_LEVEL in the CDB root initialization parameter file (not at the PDB level). You cannot set this value dynamically.

    When the CDB uses the Base Level, Automatic In-Memory is disabled, and the compression level for INMEMORY objects and columns is automatically set to QUERY LOW.

  • To use the CellMemory feature without incurring the overhead of creating an IM column store, set this parameter to CELLMEMORY_LEVEL. This option is valid only for on-premise Oracle Exadata systems.

    You cannot set this value dynamically.

    Note that if the value of INMEMORY_SIZE is greater than 0, then setting INMEMORY_FORCE=CELLMEMORY_LEVEL is equivalent to setting INMEMORY_FORCE=DEFAULT. The IM column store is enabled, even if you use only CellMemory.

INMEMORY_MAX_POPULATE_SERVERS

Specifies the maximum number of Space Management Worker Processes (Wnnn) to use for population so that the processes do not overload the rest of the system.

Set this parameter to an appropriate value based on the number of cores in the system. The default is half the effective CPU thread count or the PGA_AGGREGATE_TARGET value divided by 512 MB, whichever is less.

Note: When INMEMORY_MAX_POPULATE_SERVERS is set to 0, objects cannot be populated in the IM column store

INMEMORY_OPTIMIZED_ARITHMETIC

Controls whether NUMBER columns are stored in an In-Memory optimized format.

This parameter supports the following options:

  • DISABLE (the default) does not use the optimized encoding.

  • ENABLE encodes numbers in an optimized format that enables native calculations using SIMD hardware. This optimization is available for tables with FOR QUERY LOW compression.

INMEMORY_QUERY

Specifies whether In-Memory queries are allowed.

This parameter supports the following options:

  • ENABLE (the default) allows queries to access populated objects.

  • DISABLE blocks access to populated objects.

INMEMORY_SIZE

Sets the size of the IM column store in a database instance.

The default is 0, which disables the IM column store. The minimum non-zero setting is 100M.

For the In-Memory Free Tier option, the size must not exceed 16 GB for a non-CDB or CDB. In an Oracle RAC database, the INMEMORY_SIZE setting in each instance must not exceed 16 GB.

INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT

Limits the percentage of the total population and repopulation processes that perform trickle repopulation.

The value for this parameter is a percentage of the INMEMORY_MAX_POPULATE_SERVERS initialization parameter value. For example, if INMEMORY_TRICKLE_REPOPULATE_SERVERS_PERCENT is 5 percent, and if INMEMORY_MAX_POPULATE_SERVERS is 20, then the IM column store uses an average of 1 core (.05 * 20) for trickle repopulation.

INMEMORY_VIRTUAL_COLUMNS

Controls which expressions are populated in the IM column store.

This parameter supports the following options:

  • MANUAL (the default) permits population of IM virtual columns explicitly specified as INMEMORY.

  • ENABLE permits population of all IM virtual columns in an INMEMORY table, unless these columns have been explicitly excluded from the IM column store.

  • DISABLE specifies that no IM virtual columns are eligible for population.

OPTIMIZER_INMEMORY_AWARE

Controls the optimizer cost model enhancements for Database In-Memory.

This parameter supports the following options:

  • TRUE (default) optimizes SQL statements that reference INMEMORY objects.

  • FALSE ignores the INMEMORY attribute of tables during optimization.