Use Database In-Memory with Autonomous Database

The Database In-Memory feature available on Autonomous Database can significantly improve performance for real-time analytics and mixed workloads.

About Database In-Memory
Oracle Database In-Memory (Database In-Memory) is a suite of features that greatly improves performance for real-time analytics and mixed workloads.
Enabling Objects for In-Memory Population
You can enable and disable objects for population in the In-Memory column store.
Sizing the In-Memory Column Store
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.
Disabling the In-Memory Column Store
You can disable the In-Memory column store for your Autonomous Database by modifying the maximum In-Memory size to 0.

Parent topic: Features

About Database In-Memory

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

The Database In-Memory feature is useful when:

You have enabled the In-Memory feature for your on-premises database and are now migrating from the on-premises database to Autonomous Database.
Queries in your database spend most of their run time scanning and filtering data, as well as performing joins and group-by aggregations.
You need to improve the response time of your queries that are analytical in nature.

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.

You can use the INMEMORY clause in DDL statements to enable In-Memory for the following objects:

Tables
Partitions or sub-partitions
Materialized views

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 repopulation into the In-Memory column store. Depending on the priority setting, repopulation may happen automatically or on first access. If you are monitoring performance you will notice slower pefromance until the object is repopulated.

Parent topic: Use Database In-Memory with Autonomous Database

Enabling Objects for In-Memory Population

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:

CRITICAL: Object is populated immediately after the database is opened.
HIGH : Object is populated after all CRITICAL objects have been populated, if space remains available in the In-Memory column store.
MEDIUM: Object is populated after all CRITICAL and HIGH objects have been populated, and space remains available in the In-Memory column store.
LOW: Object is populated after all CRITICAL, HIGH, and MEDIUM objects have been populated, if space remains available in the In-Memory column store.
NONE: Objects are only populated after they are scanned for the first time, if space is available in the In-Memory column store. The priority NONE is the default priority.

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;

This example creates the im_emp table with the INMEMORY attribute. The example uses the default priority of NONE for the INMEMORY clause, which means the table is populated only after it is scanned for the first time.

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);

This example sets the in the INMEMORY attribute for the im_emp_tb, but it excludes the depno column.

See INMEMORY_TABLE_CLAUSE for more information.

After INMEMORY is enabled for a table, you can query the V$IM_SEGMENTS view to determine if data from the table is populated in the In-Memory column store. For example:

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

See V$IM_SEGMENTS for more information.

After the object is populated in the In-Memory column store, the object is only evicted when the object is dropped or moved, or the object is updated with the NO INMEMORY attribute. For example:

ALTER TABLE im_emp NO INMEMORY;

See How In-Memory Population Works for more information.

Notes for enabling objects for In-Memory population:

The database does not automatically populate In-Memory objects with the PRIORITY setting of NONE. However, you can manually populate these objects into In-Memory column store. See Populating the IM Column Store Manually and DBMS_INMEMORY.POPULATE Procedure for more information.
There must be continuous table scans to ensure that the In-Memory column store will automatically grow large enough for your workload. If an object is partially populated, the table scan can use both In-Memory and Exadata Smart Scan.

Parent topic: Use Database In-Memory with Autonomous Database

Sizing the In-Memory Column Store

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.

The default maximum In-Memory size for your Autonomous Database can be up to 50% of the SGA size. However, you can modify the maximum In-Memory size for your Autonomous Database:

By changing the ECPU count for your Autonomous Database.
By using the DBMS_INMEMORY_ADMIN.SET_SGA_PERCENTAGE procedure to modify the maximum In-Memory size. You can specify a value in the range between 0 and 70. Specifying a zero (0) value disables the Database In-Memory. For example:
```
BEGIN
    DBMS_INMEMORY_ADMIN.SET_SGA_PERCENTAGE(60);
END;
/
```
This example modifies the maximum In-Memory size to 60% of the SGA size.

See SET_SGA_PERCENTAGE Procedure for more information.

Query the V$INMEMORY_AREA view to retrieve the information about space allocation inside the In-Memory area. For example:

SELECT * FROM V$INMEMORY_AREA;

See V$INMEMORY_AREA for more information.

Parent topic: Use Database In-Memory with Autonomous Database

Disabling the In-Memory Column Store

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.

Parent topic: Use Database In-Memory with Autonomous Database