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.
This chapter contains the following topics:
- Overview of Enabling the IM Column Store
By default, theINMEMORY_SIZE
initialization parameter is set to0
, which means the IM column store is disabled. - 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. - 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. - 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. - Disabling the IM Column Store
You can disable the IM column store by setting theINMEMORY_SIZE
initialization parameter to zero, and then reopening the database.
Parent topic: Configuring and Populating the IM Column Store
3.1 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 nonzero value before restarting the database 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:
-
Oracle Database Reference to learn more about the
INMEMORY_SIZE
initialization parameter -
Oracle Database SQL Language Reference for more information about the
INMEMORY
clause
Parent topic: Enabling and Sizing the IM Column Store
3.2 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
orFOR 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
orFOR QUERY LOW
compression methods. However, these options consume more memory.
When sizing the IM column store, consider the following guidelines:
-
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 theDBMS_COMPRESSION
interface. -
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. -
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 ofNUMBER
columns. -
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 128 MB.
See Also:
-
Oracle Database Administrator’s Guide to learn how to estimate compression ratio using Compression Advisor
-
Oracle Database Reference to learn about
V$IM_SEGMENTS
Parent topic: Enabling and Sizing the IM Column Store
3.3 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.
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.
INMEMORY_SIZE
to a different value. For example, in a CDB with 100 PDBs, you could set INMEMORY_SIZE
to 20G
at the CDB level, and then set INMEMORY_SIZE
to 12G
in one PDB, to 8G
in a second PDB, and to 0
in the remaining PDBs.
Prerequisites
This task assumes that the following:
-
The database is open.
-
The
COMPATIBLE
initialization parameter is set to12.1.0
or higher. -
The
INMEMORY_SIZE
initialization parameter is set to0
(default).
To enable the IM column store:
-
In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.
-
Set the
INMEMORY_SIZE
initialization parameter to a nonzero 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 specifySCOPE=SPFILE
.For example, the following statement sets the In-Memory Area size to 10 GB:
ALTER SYSTEM SET INMEMORY_SIZE = 10G SCOPE=SPFILE;
-
Shut down the database, and then reopen it.
You must reopen the database to initialize the IM column store in the SGA.
-
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:
-
Oracle Database Upgrade Guide for information about setting the database compatibility level
-
Oracle Database Reference for more information about the
INMEMORY_SIZE
initialization parameter
Parent topic: Enabling and Sizing the IM Column Store
3.4 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.
See Also:
-
Oracle Database Reference for more information about the
INMEMORY_SIZE
initialization parameter
Parent topic: Enabling and Sizing the IM Column Store
3.5 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:
-
Set the
INMEMORY_SIZE
initialization parameter to0
in the server parameter file (SPFILE). -
Shut down the database.
-
Start a database instance, and then open the database.
See Also:
Oracle Database Reference for information about the INMEMORY_SIZE
initialization parameter
Parent topic: Enabling and Sizing the IM Column Store