In-Memory Initialization Parameters

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`: 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. `HIGH`: This level fully automates management of the IM column store. Oracle Database automatically decides the optimal set of segments and the optimal columns to populate in the IM column store, evicting infrequently accessed segments. No user decision-making is required.
`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-premises 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`. In this case, the Database In-Memory option is enabled, even if you use CellMemory only.
`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 Database In-Memory Base Level option, the size must not exceed 16 GB for a CDB. In an Oracle RAC database, the `INMEMORY_SIZE` setting in each database 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.