13 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 13-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). To disable all tables and materialized views for the IM column store, set this parameter to OFF.
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.
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.