Oracle Database In-Memory (Database In-Memory) is a suite of features that greatly improves performance for real-time analytics and mixed workloads.

The In-Memory column store is the key feature of Database In-Memory. The In-Memory column store resides in the In-Memory Area, which is an optional portion of the System Global Area (SGA). When enabled for In-Memory, tables, partitions, sub-partitions and materialized views are populated in a columnar format in the In-Memory column store. This columnar format is optimized for very fast scans, which can enable analytic queries to run orders of magnitude faster than row format-based queries. See Oracle Database In-Memory for more information.

The In-Memory column store dynamically grows over time to accommodate the hottest INMEMORY objects. You can use the PRIORITY table clause or have continuous table scans to ensure that the In-Memory column store grows large enough for your workload. See Sizing the In-Memory Column Store for more information.

See Enabling Objects for In-Memory Population for more information.

Note the following for enabling Database In-Memory:

• Database In-Memory is only available for an Autonomous Database instance with:

  • The ECPU compute model

  • A minimum of 16 ECPUs

• If you restart the database, objects enabled for INMEMORY require re-population into the In-Memory column store. Depending on the priority setting, re-population may happen automatically or on first access. If you are monitoring performance you will notice slower performance until the object is repopulated.

You can enable and disable objects for population in the In-Memory column store.

Only objects with the INMEMORY clause are eligible for population into the In-Memory column store. Use DDL statements CREATE TABLE or ALTER TABLE to apply the INMEMORY clause.

By default, objects are created with the NO INMEMORY attribute, which means they are not eligible for population into the In-Memory column store.

Enabling an object as INMEMORY is specifying that an object can potentially reside in the In-Memory column store. In-Memory population is a separate step that occurs when the database reads existing row-format data, transforms it into columnar format, and then stores it in the In-Memory column store. By default, all of the columns in an object with the INMEMORY attribute are populated into the In-Memory column store.

Setting the INMEMORY attribute on an object implies that the object is a candidate for population in the In-Memory column store. The object is not immediately populated to the In-Memory column store. However, you can use the PRIORITY clause with the INMEMORY attribute to control the priority of population into the In-Memory column store. The PRIORITY clause can be specified with the following values:

Example to create a table with the INMEMORY attribute:

CREATE TABLE im_emp (
    id  NUMBER,  name VARCHAR2(20),  
    depno NUMBER,  sal   NUMBER,  
    mgr   NUMBER,  
    loc   VARCHAR2(20)) 
    INMEMORY;

Example to use the PRIORITY clause with the INMEMORY attribute:

CREATE TABLE im_emp_1 (
    id  NUMBER,  name VARCHAR2(20),  
    depno NUMBER,  sal   NUMBER,  
    mgr   NUMBER,  
    loc   VARCHAR2(20)) 
    INMEMORY PRIORITY CRITICAL;

Example to set the INMEMORY attribute for an existing table:

ALTER TABLE employees INMEMORY;

This example sets the in the INMEMORY attribute for the employees table.

You can also alter a table to set the INMEMORY attribute only for a subset of columns in the table. For example:

ALTER TABLE im_emp_tb INMEMORY NO INMEMORY(depno);

See INMEMORY_TABLE_CLAUSE for more information.

SELECT SEGMENT_NAME, POPULATE_STATUS 
FROM   V$IM_SEGMENTS 
WHERE  SEGMENT_NAME = 'IM_EMP';

See V$IM_SEGMENTS for more information.

ALTER TABLE im_emp NO INMEMORY;

See How In-Memory Population Works for more information.

When your Autonomous Database instance has 16 or more ECPUs, the Database In-Memory feature is enabled by default, and up to 50% of the SGA can be used by In-Memory.

The memory for the In-Memory Area is not reserved upfront, and the initial size of the In-Memory Area is 0. The In-Memory Area grows gradually each time there is insufficient space for the In-Memory population.

Objects in the In-Memory column store are dynamically managed by Automatic In-Memory (AIM). Automatic In-Memory is by default enabled for your Autonomous Database.

AIM uses internal statistics to determine how frequently In-Memory objects and columns are accessed. If the In-Memory column store is full and other more frequently accessed segments would benefit from population in the In-Memory column store, then the In-Memory column store evicts inactive segments.

To ensure that the working data set is always populated, AIM automatically evicts cold (infrequently accessed) segments. See Automating Management of In-Memory Objects for more information.

SELECT * FROM V$INMEMORY_AREA;

You can disable the In-Memory column store for your Autonomous Database by modifying the maximum In-Memory size to 0.

For example:

BEGIN
    DBMS_INMEMORY_ADMIN.SET_SGA_PERCENTAGE(0);
END;
/

This example modifies the maximum In-Memory size value to zero (0) and disables Database In-Memory for the instance.

See SET_SGA_PERCENTAGE Procedure for more information.