2.170 INMEMORY_SIZE

INMEMORY_SIZE sets the size of the In-Memory Area, which contains the IM Column Store (IM column store) on a database instance.

Property Description

Parameter type

Big integer

Syntax

INMEMORY_SIZE = integer [K | M | G]

Default value

0

Modifiable

You can use ALTER SYSTEM to increase or decrease the value of this parameter. However, the value of this parameter must always be greater than or equal to its value at instance startup.

Modifiable in a PDB

Yes

Range of values

0 to the amount of memory left in the SGA after other allocations

A value of 0 specifies that the IM column store is not allocated and no In-Memory features are enabled.

A value greater than 0 and less than 100M specifies that the IM column store is enabled for future growth, but no memory is currently provisioned to the In-Memory Area.

Basic

No

Oracle RAC

All instances should use the same value.

The database must be restarted after setting this parameter to enable the IM column store.

Typically this parameter should be set to at least the size needed to accommodate all the tables that will use the IM column store. It can be set higher to allow for growth of those tables or to accommodate other tables that will use the IM column store in the future.

This parameter can also be set per PDB to limit the maximum size of the IM column store for that PDB. Note that the sum of the PDB values do not have to equal the CDB value, and the sum of the PDB values may even be greater than the CDB value.

Unless this parameter is specifically set on a PDB, each PDB inherits the CDB value, which means they can use all of the available IM column store.

The value specified for this parameter counts toward SGA_TARGET. For example, if you set SGA_TARGET to 10 GB and you set INMEMORY_SIZE to 2 GB, then 20% of the SGA_TARGET setting is allocated to the In-Memory Area.

Starting with Oracle Database 23ai, Automatic In-Memory Sizing can automatically grow or shrink the In-Memory Area based on the benefits of the column store if the following conditions are true:

  • SGA_TARGET > 0

  • INMEMORY_AUTOMATIC_LEVEL = MEDIUM or HIGH

  • VECTOR_MEMORY_SIZE is set to 0 or not specified

In-Memory Dynamic Scans require the Resource Manager. Therefore, the Resource Manager is automatically enabled when you change the value of INMEMORY_SIZE from 0 to a non-zero value. No specific resource plan is required.

Database In-Memory Base Level allows a maximum of 16 GB for the size of the In-Memory Area. Therefore, if Database In-Memory Base Level is enabled (the value of the INMEMORY_FORCE initialization parameter is set to BASE_LEVEL), then the value of INMEMORY_SIZE cannot exceed 16 GB for a CDB. In an Oracle RAC environment, the value of INMEMORY_SIZE is limited to 16 GB for each instance.

See Also: