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.
-
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.
INMEMORY
clause in DDL statements to enable In-Memory for the following objects:
-
Tables (including external 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 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.
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 allCRITICAL
objects have been populated, if space remains available in the In-Memory column store. -
MEDIUM
: Object is populated after allCRITICAL
andHIGH
objects have been populated, and space remains available in the In-Memory column store. -
LOW
: Object is populated after allCRITICAL
,HIGH
, andMEDIUM
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 priorityNONE
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.
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.
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 ofNONE
. 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.
-
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:
This example modifies the maximum In-Memory size to 60% of the SGA size.BEGIN DBMS_INMEMORY_ADMIN.SET_SGA_PERCENTAGE(60); END; /
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