3 Enabling and Sizing the IM Column Store

Enable the IM column store by specifying its size. You can also resize the IM column store, or disable it.

This chapter contains the following topics:

Topics:

Overview of Enabling the IM Column Store

By default, the INMEMORY_SIZE initialization parameter 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 non-zero value before restarting the instance.

You can dynamically increase the INMEMORY_SIZE size setting by using an ALTER SYSTEM statement.

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. Add additional 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 128 MB.

See Also:

Enabling the IM Column Store for a Database

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

Prerequisites

This task assumes that the following:

  • The database is open.

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

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

To enable the IM column store:

  1. In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.

  2. Set the INMEMORY_SIZE initialization parameter to a non-zero value.

    The minimum setting is 100M.

    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 10 GB:

    ALTER SYSTEM SET INMEMORY_SIZE = 10G SCOPE=SPFILE;

  3. Shut down the database, and then reopen it.

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

  4. 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 reopening the database.

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:

Increasing the Size of the IM Column Store Dynamically

When more memory is required for the IM column store, you can increase its size dynamically.

The size of the IM column store cannot be decreased dynamically. If you set INMEMORY_SIZE to a value smaller than its current setting, then you must specify SCOPE=SPFILE in the ALTER SYSTEM statement. If you set this parameter by specifying SCOPE=SPFILE, then you must restart the database for the change to take effect.

Prerequisites

To increase the size of the IM column store dynamically, you must meet the following prerequisites:

  • The 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 128 megabytes greater than the current INMEMORY_SIZE setting.

  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:

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