To enable or disable the IM column store, specify a value for the
INMEMORY_SIZE initialization parameter.
3.1 Overview of Enabling the IM Column Store
Enable the IM column store size by setting the
INMEMORY_SIZE initialization parameter.
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 minimum value of 100 MB 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, the size must be less than or equal to 16 GB for a CDB. In an Oracle RAC database, every instance must be less than or equal to 16 GB.
By default, you must specify candidates for population in the IM column store using the
INMEMORY clause of a
ALTER statement for a table, tablespace, or materialized view.
Oracle Database Reference to learn more about the
Oracle Database SQL Language Reference for more information about the
Oracle Database Licensing Information User Manual for details on which features are supported for different editions and services
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 HIGHor
FOR CAPACITY LOWcompression methods. However, these options require additional CPU during query execution to decompress the data.
To get the best query performance, choose the
FOR QUERY HIGHor
FOR QUERY LOWcompression 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
MEMCOMPRESSclause. The advisor uses the
Add the individual amounts to together.
V$IM_SEGMENTSshows 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 LOWcompression, then add roughly 15% to account for the dual storage of
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.
Oracle Database Administrator’s Guide to learn how to estimate compression ratio using Compression Advisor
Oracle Database Reference to learn about
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.
In this context, "database" is a non-CDB, CDB, or PDB. 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.
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
16G at the CDB level, and then set
10G in one PDB, to
6G in a second PDB, and to
0 in the remaining PDBs.
This task assumes that the following:
The database is open.
COMPATIBLEinitialization parameter is set to
INMEMORY_SIZEinitialization parameter is set to
You want to use the Database In-Memory Base Level.
To enable the IM column store:
In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.
INMEMORY_SIZEinitialization parameter to a nonzero value.
The minimum setting is
When you set this initialization parameter in a server parameter file (SPFILE) using the
ALTER SYSTEMstatement, you must specify
For example, the following statement sets the In-Memory Area size to 16 GB:
ALTER SYSTEM SET INMEMORY_SIZE = 16G SCOPE=SPFILE;
For the Database In-Memory Base Level, set the
INMEMORY_FORCEinitialization parameter to
For example, the following statement specifies the Base Level:
ALTER SYSTEM SET INMEMORY_FORCE=BASE_LEVEL SCOPE=SPFILE;
You cannot set
INMEMORY_FORCE=BASE_LEVELat the PDB level. Also, you cannot set this parameter dynamically.
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 16 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=16G 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 16G
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.
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
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
- In SQL*Plus or SQL Developer, log in to the database with administrative privileges.
- Optionally, check the amount of memory currently allocated for the IM column store:
SHOW PARAMETER INMEMORY_SIZE
- Set the
INMEMORY_SIZEinitialization parameter to a value greater than the current size of the IM column store with an
ALTER SYSTEMstatement that specifies
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
ALTER SYSTEM SET INMEMORY_SIZE = 500M SCOPE=BOTH;
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.
This task assumes that the IM column store is enabled in an open database.
To disable the IM column store:
INMEMORY_SIZEinitialization parameter to
0in the server parameter file (SPFILE).
Shut down the database.
Start a database instance, and then open the database.
Oracle Database Reference for information about the
INMEMORY_SIZE initialization parameter