3 Enabling and Sizing the IM Column Store

To enable or disable the IM column store, specify a value for the INMEMORY_SIZE initialization parameter.

Overview of Enabling the IM Column Store

Enable the IM column store size by setting the INMEMORY_SIZE initialization parameter.

By default, INMEMORY_SIZE is set to 0, which means the IM column store is disabled. To enable the IM column store, set the initialization parameter INMEMORY_SIZE to a value greater than zero before restarting the database instance. You can dynamically increase the INMEMORY_SIZE size setting by using an ALTER SYSTEM statement.

For the Database In-Memory Base Level only, you can allocate up to 16 GB on any CDB or any instance of an Oracle RAC database.

By default, you must specify candidates for population in the IM column store using the INMEMORY clause of a CREATE or ALTER statement for a table, tablespace, or materialized view.

See Also:

Estimating the Required Size of the IM Column Store

Estimate the size of the IM column store based on your requirements, and then resize the IM column store to meet those requirements. Applying compression can reduce memory size.

The amount of memory required by the IM column store depends on the database objects stored in it and the compression method applied on each object. When choosing a compression method for the INMEMORY objects, balance the performance benefits against the amount of available memory:

  • To make the greatest reduction in memory size, choose the FOR CAPACITY HIGH or FOR CAPACITY LOW compression methods. However, these options require additional CPU during query execution to decompress the data.

  • To get the best query performance, choose the FOR QUERY HIGH or FOR QUERY LOW compression methods. However, these options consume more memory.

When sizing the IM column store, consider the following guidelines:

  1. For every object to be populated into the IM column store, estimate the amount of memory it consumes.

    Oracle Compression Advisor estimates the compression ratio that you can realize using the MEMCOMPRESS clause. The advisor uses the DBMS_COMPRESSION interface.

  2. Add the individual amounts to together.

    Note:

    After population, V$IM_SEGMENTS shows the actual size of the objects on disk and their size in the IM column store. You can use this information to calculate the compression ratio for the populated objects. However, if the objects were compressed on disk, then this query does not show the correct compression ratio.

  3. If you configured In-Memory Optimized Arithmetic, and if In-Memory tables use FOR QUERY LOW compression, then add roughly 15% to account for the dual storage of NUMBER columns.

  4. Add space to account for the growth of database objects, and to store updated versions of rows after DML operations.

    The minimum amount for dynamic resizing is 1 granule.

See Also:

Enabling the IM Column Store for a CDB or PDB

Before tables or materialized views can be populated into the IM column store, you must enable the IM column store.

In a CDB, the INMEMORY_SIZE setting in the CDB root determines the overall size of the IM column store. By default, all PDBs have access to the IM column store.

Note:

For the Database In-Memory Base Level, the INMEMORY_SIZE size at the CDB level must be less than or equal to 16G.

Within an individual PDB, you can limit access to the shared In-Memory Area by setting INMEMORY_SIZE to a different value. For example, in a CDB with 100 PDBs, you could set INMEMORY_SIZE to 16G at the CDB level, and then set INMEMORY_SIZE to 10G in one PDB, to 6G in a second PDB, and to 0 in the remaining PDBs.

Prerequisites

This task assumes that the following:

  • The CDB is open.

  • The COMPATIBLE initialization parameter is set to 12.1.0 or higher.

  • The INMEMORY_SIZE initialization parameter is set to 0 (default).

  • You want to use the Database In-Memory Base Level.

To enable the IM column store:

  1. In SQL*Plus or SQL Developer, log in to the CDB root as a user with administrator privileges.

  2. Set the INMEMORY_SIZE initialization parameter to a nonzero value.

    When you set this initialization parameter in a server parameter file (SPFILE) using the ALTER SYSTEM statement, you must specify SCOPE=SPFILE.

    For example, the following statement sets the In-Memory Area size to 16 GB:

    ALTER SYSTEM SET INMEMORY_SIZE = 16G SCOPE=SPFILE;

  3. For the Database In-Memory Base Level, set the INMEMORY_FORCE initialization parameter to BASE_LEVEL.

    For example, the following statement specifies the Base Level: 

    ALTER SYSTEM SET INMEMORY_FORCE=BASE_LEVEL SCOPE=SPFILE;

    You cannot set INMEMORY_FORCE=BASE_LEVEL at the PDB level. Also, you cannot set this parameter dynamically.

  4. Shut down the CDB, and then reopen it.

    You must reopen the CDB to initialize the IM column store in the SGA.

  5. Optionally, check the amount of memory currently allocated for the IM column store:

    SHOW PARAMETER INMEMORY_SIZE

    Note:

    After the IM column store is enabled, you can increase its size dynamically without shutting down and reopening the CDB.

Example 3-1 Enabling the IM Column Store

Assume that the INMEMORY_SIZE initialization parameter is set to 0. The following SQL*Plus example sets INMEMORY_SIZE to 10 GB, shuts down the database instance, and then reopens the database so that the change can take effect: 

SQL> SHOW PARAMETER INMEMORY_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
inmemory_size                        big integer 0

SQL> ALTER SYSTEM SET INMEMORY_SIZE=10G SCOPE=SPFILE;

System altered.

SQL> SHUTDOWN IMMEDIATE
Database closed.
Database dismounted.
ORACLE instance shut down.

SQL> STARTUP
ORACLE instance started.

Total System Global Area   11525947392 bytes
Fixed Size                     8213456 bytes
Variable Size                754977840 bytes
Database Buffers              16777216 bytes
Redo Buffers                   8560640 bytes
In-Memory Area             10737418240 bytes
Database mounted.
Database opened.

SQL> SHOW PARAMETER INMEMORY_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
inmemory_size                        big integer 10G

See Also:

Sizing the In-Memory Area

The size of the In-Memory area is set by the INMEMORY_SIZE initialization parameter. By default, the size of the In-Memory Area is 0, which means the IM column store is disabled. The In-Memory Area can also be resized dynamically, either through automatic memory management or manually via an ALTER SYSTEM command.

Automatic In-Memory sizing can automatically grow or shrink the In-Memory Area based on the benefits of the column store if the following are true:

  • SGA_TARGET > 0.
  • INMEMORY_AUTOMATIC_LEVEL = MEDIUM or HIGH.

The In-Memory Area and SGA_TARGET

The In-Memory area is subtracted from the SGA_TARGET initialization parameter setting. For example, if you set SGA_TARGET to 10 GB, and if you set the INMEMORY_SIZE to 4 GB, then 40% of the SGA_TARGET setting is allocated to the In-Memory area. The following graphic illustrates the relationship.

Figure 3-1 INMEMORY_SIZE and SGA_TARGET

Description of Figure 3-1 follows
Description of "Figure 3-1 INMEMORY_SIZE and SGA_TARGET"

The INMEMORY_SIZE parameter can be dynamically modified, but cannot be smaller than the initial value that was specified during database startup.

In-Memory Area Sizing Under Automatic Shared Memory Management (ASMM)

In-Memory Column store sizing can now be managed with ASMM when the value of sga_target is greater than zero and INMEMORY_AUTOMATIC_LEVEL on a PDB is set to either MEDIUM or HIGH.

When ASMM is enabled, it controls the size of the In-Memory Area along with the other components of the SGA, including the buffer cache and the shared pool. ASMM provides dynamic resizing of the physical In-Memory column store based on demand and workload. It automatically increases the size of the In-Memory column store when it runs out of space. It also automatically shrinks the size of the column store and makes the free memory available to other SGA components, with no database restart required. ASMM uses the statistics provided by the view V$INMEMORY_SIZE_ADVICE to determine the optimal size of the column store.

The INMEMORY_SIZE parameter specifies the minimum IM area size for ASMM.

In Memory Area Sizing When ASMM is not Enabled

If ASMM is not enabled, then the In-Memory Area size is not controlled by automatic memory management. The database does not automatically shrink the In-Memory Area when the buffer cache or shared pool requires more memory, or increase the In-Memory Area when it runs out of space.

Differences in the Effect of INMEMORY_SIZE When ASMM Enabled and When it is Disabled

In Oracle Database 23ai and later, the CDB INMEMORY_SIZE parameter specifies the minimum total column store size across all tenants when ASMM is in use (SGA_TARGET > 0). Automatic In-Memory (AIM) also requires that the INMEMORY_AUTOMATIC_LEVEL parameter is set to HIGH. This because SGA's decision to size the column store is global and applies to all PDBs

The INMEMORY_SIZE initialization parameter sets the minimum size at instance start up, but you can also change it at runtime with an ALTER command. In the case where ASMM is not enabled (SGA_TARGET=0), the INMEMORY_SIZE parameter sets the actual total size of the In-Memory area.

Note:

In releases prior to Oracle Database 23ai:

Prior to Oracle Database 23ai, the In-Memory area can be grown dynamically at the CDB level, shrinking the In-Memory area dynamically at the CDB level while the instance is running is not allowed. At the PDB level in prior releases, both growth and shrinking are possible, although shrinking the size of the In-Memory Area dynamically reduces the quota for In-Memory usage in the given PDB. In order to physically shrink the column store and make the free memory available to other SGA components in these earlier releases, you must restart the instance.

In-Memory Area Sizing When ASMM is Enabled, but INMEMORY_AUTOMATIC_LEVEL is not Set to HIGH

In this case, the In-Memory area is managed the same way as described in the previous note about pre-23ai releases of Oracle Database. On a CDB, dynamic growth of the In-Memory area can occur, but dynamic shrinking cannot. Also, the same constraints described above apply to the PDB.

Dynamically Setting Size Constraints for the In-Memory Area

Setting INMEMORY_SIZE dynamically using the ALTER SYSTEM statement establishes the lower bound for any automatic resize operations performed when ASMM is enabled. If you increase INMEMORY_SIZE, the database allocates increased memory when the following conditions are met:

  • Free memory is available in the SGA.

  • The new size for INMEMORY_SIZE is at least 1 granule.

    Note:

    • You cannot use ALTER SYSTEM to reduce INMEMORY_SIZE.
    • The CDB INMEMORY_SIZE must be at least 100MB for In-Memory to be enabled.

The V$INMEMORY_AREA and V$SGA views immediately reflect the change.

In-Memory Resource Management in a CDB

In a CDB, the size of the IM column store is set by the INMEMORY_SIZE parameter in the CDB root. Note that by default, the IM column store is shared among the PDBs. This means that a PDB can potentially "starve" other PDBs by consuming the available memory.

Within a PDB, you can limit memory consumption by using ALTER SYSTEM SET INMEMORY_SIZE. For example, at the CDB level, you might set INMEMORY_SIZE to 20G, and then configure the PDBs as in this example:

  • In hrpdb, set INMEMORY_SIZE to 0

  • In salespdb, set INMEMORY_SIZE to 10G

  • In oepdb, set INMEMORY_SIZE to 11G

In the preceding example, the INMEMORY_SIZE settings at the PDB level add up to 21G, even though INMEMORY_SIZE at the CDB level is only 20G. Oversubscription ensures that valuable space in the IM column store is not wasted if a PDB is shut down or unplugged.

PDB and CDB Behavior Under Automatic Shared Memory Management

These behaviors apply when SGA_TARGET is greater than zero, which means that ASMM is possible. For ASMM to work, keep INMEMORY_AUTOMATIC_LEVEL on PDBs and CDBs compatible as described.
  • When the Automatic In-Memory INMEMORY_AUTOMATIC_LEVEL parameter is set to HIGH at the CDB level:

    The PDB inherits the HIGH setting for INMEMORY_AUTOMATIC_LEVEL. At the PDB level, you can also change this setting to LOW or MEDIUM. An error occurs you change this setting to OFF on the PDB.

  • If INMEMORY_AUTOMATIC_LEVEL is set to OFF at the CDB level and you attempt to change it to HIGH on a CDB:

    If on any PDB INMEMORY_AUTOMATIC_LEVEL is already explicitly set to OFF, then a user error occurs.

See Also:

Increasing the Size of the IM Column Store Dynamically

You can increase the size of IM column store at database run time by using SQL to reset INMEMORY_SIZE.

Note:

If ASMM is enabled, it automatically decreases or increases the IM column store size dynamically as needed. If you want to set INMEMORY_SIZE to a value smaller than its current setting, check the prerequisites listed here, then issue the ALTER SYSTEM statement with SCOPE=SPFILE as described below. Then restart the database to effect the change.

Prerequisites

To increase the size of the IM column store dynamically using ALTER SYSTEM, the instance must meet the following prerequisites:

  • The IM column store must be enabled.

  • The compatibility level must be 12.2.0 or higher.

  • The database instances must be started with an SPFILE.

  • The new size of the IM column store must be at least 1 granule.

Steps

  1. In SQL*Plus or SQL Developer, log in to the database with administrative privileges.
  2. Optionally, check the amount of memory currently allocated for the IM column store:
    SHOW PARAMETER INMEMORY_SIZE
  3. Set the INMEMORY_SIZE initialization parameter to a value greater than the current size of the IM column store with an ALTER SYSTEM statement that specifies SCOPE=BOTH or SCOPE=MEMORY.

    When you set this parameter dynamically, you must set it to a value that is higher than its current value, and there must be enough memory available in the SGA to increase the size of the IM column store dynamically to the new value.

    For example, the following statement sets INMEMORY_SIZE to 500M dynamically:
    ALTER SYSTEM SET INMEMORY_SIZE = 500M SCOPE=BOTH;

See Also:

Views for Working Automatic Sizing of the IM Column Store

V$INMEMORY_SIZE_ADVICE estimates usage and performance statistics for different simulated sizes of the In-Memory Column Store (IM Column Store).

For various sizes of the CDB In-Memory column store, this view forecasts the cumulative time spent by database in processing user requests. This includes wait time and CPU time for all non-idle user sessions

See Also:

V$INMEMORY_SIZE_ADVICE is described in the Oracle Database Reference.

Disabling the IM Column Store

You can disable the IM column store by setting the INMEMORY_SIZE initialization parameter to zero, and then reopening the database.

Assumptions

This task assumes that the IM column store is enabled in an open database.

To disable the IM column store:

  1. Set the INMEMORY_SIZE initialization parameter to 0 in the server parameter file (SPFILE).

  2. Shut down the database.

  3. Start a database instance, and then open the database.

See Also:

Oracle Database Reference for information about the INMEMORY_SIZE initialization parameter