4 Automating Management of In-Memory Objects

Automatic In-Memory and Automatic Data Optimization (ADO) manage objects in the IM column store dynamically, without user intervention.

Note:

Automatic In-Memory and ADO do not currently support external tables and hybrid partitioned tables.

Configuring Automatic In-Memory

When INMEMORY_AUTOMATIC_LEVEL is HIGH, all objects are specified as INMEMORY by default. Automatic In-Memory uses access tracking and column statistics to manage objects in the IM column store.

Note:

If the INMEMORY_FORCE initialization parameter is set to BASE_LEVEL, then Automatic In-Memory is disabled even if INMEMORY_AUTOMATIC_LEVEL is set. Even if tables have a compression level of AUTO, Automatic In-Memory background operations do not run.

Purpose of Automatic In-Memory

Automatic In-Memory optimizes the SQL workload as it changes, without manual intervention.

The working data set consists of the most frequently queried segments. Typically, the working data set changes with time for many applications. Users must decide which segments to enable as INMEMORY, monitor usage to decide which IM segments to populate and evict, and create ADO IM policies. These tasks require a thorough understanding of the workload.

To free the DBA from manual maintenance chores, Automatic In-Memory uses frequently updated internal statistics to maintain the working data set in the IM column store. Oracle Database decides what to populate and what to evict, and when to do it. In a sense, the IM column store becomes "self-driving."

How Automatic In-Memory Works

Automatic In-Memory uses segment level access tracking as well as internal statistics to determine how frequently In-Memory objects and columns are accessed.

Automatic In-Memory Heat Level Statistics

Automatic In-Memory monitors segments using segment level access tracking in addition to a column statistics infrastructure that is similar to a heat map.

As shown in the following diagram, Automatic In-Memory uses the heat level statistics to determine which segments to populate and evict, and which columns to compress.

Figure 4-1 Automatic In-Memory

Description of "Figure 4-1 Automatic In-Memory"

When INMEMORY_AUTOMATIC_LEVEL is HIGH, Automatic In-Memory continuously monitors column statistics in the IM store. The database identifies cold regions of the IM store through internal column statistics, which are similar to those used by Heat Map but do not require HEAT_MAP to be set to ON. Using heat-level statistics, Automatic In-Memory optimizes storage by populating and evicting objects and by compressing columns.

How Enabling Objects for Automatic In-Memory Works

Depending on the INMEMORY_AUTOMATIC_LEVEL setting, either the database or the user enables specific objects for In-Memory access.

When the INMEMORY_AUTOMATIC_LEVEL initialization parameter is set to HIGH, all segments that do not have a pre-existing INMEMORY setting are automatically marked INMEMORY MEMCOMPRESS AUTO. For partitioned tables, Oracle Database sets the compression level to MEMCOMPRESS AUTO for existing and new partitions. In all cases, if segments have a pre-existing INMEMORY setting, then the previous setting is retained.

The MEMCOMPRESS AUTO setting means the segments are automatically enabled for In-Memory access, with no DDL required. Note that In-Memory tables have overhead for DML. If you are concerned about the HIGH level enabling INMEMORY for tables with moderate or high levels of DML, then you can specify these tables as NO INMEMORY manually. You can re-enable the NO INMEMORY tables by specifying the INMEMORY MEMCOMPRESS AUTO clause.

When INMEMORY_AUTOMATIC_LEVEL is not HIGH, all objects are NO INMEMORY by default. If you set INMEMORY_AUTOMATIC_LEVEL to HIGH, and if you later set it to a different value, then the database changes all segments that were previously MEMCOMPRESS AUTO to NO INMEMORY. In this case, you must execute DDL to enable the desired segments for In-Memory access, as you must do when Automatic In-Memory is not enabled.

Example 4-1 Setting Automatic In-Memory to HIGH

In this example, the IM column store is enabled, but no segments currently have a user-specified INMEMORY setting, and Automatic In-Memory is disabled. You log in to salespdb as an administrator, and then do the following:

1. Query the data dictionary to determine whether any tables are specified as INMEMORY:

   SELECT TABLE_NAME, PARTITIONED, INMEMORY, INMEMORY_COMPRESSION 
   FROM   DBA_TABLES 
   WHERE OWNER='SH' AND TABLE_NAME IN ('COUNTRIES', 'PRODUCTS', 'TIMES') 
   ORDER BY TABLE_NAME;
   
   TABLE_NAME                 PAR INMEMORY INMEMORY_COMPRESS
   -------------------------- --- -------- -----------------
   COUNTRIES                  NO  DISABLED
   PRODUCTS                   NO  DISABLED
   TIMES                      NO  DISABLED

2. Apply the INMEMORY attribute to the countries table:

   ALTER TABLE sh.countries INMEMORY MEMCOMPRESS FOR CAPACITY HIGH;

3. Query the data dictionary to confirm the change:

   SELECT TABLE_NAME, PARTITIONED, INMEMORY, INMEMORY_COMPRESSION 
   FROM   DBA_TABLES 
   WHERE OWNER='SH' AND TABLE_NAME IN ('COUNTRIES', 'PRODUCTS', 'TIMES') 
   ORDER BY TABLE_NAME;
   
   TABLE_NAME                 PAR INMEMORY INMEMORY_COMPRESS
   -------------------------- --- -------- -----------------
   COUNTRIES                  NO  ENABLED  FOR CAPACITY HIGH
   PRODUCTS                   NO  DISABLED
   TIMES                      NO  DISABLED

4. Connect to the CDB root, and then set INMEMORY_AUTOMATIC_LEVEL to HIGH:

   ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = 'HIGH' SCOPE=SPFILE;

5. Shut down the CDB, and then re-open it:

   SHUTDOWN IMMEDIATE
   STARTUP

6. Log in to salespdb, and then query the data dictionary to determine the current INMEMORY compression settings:

   SELECT TABLE_NAME, PARTITIONED, INMEMORY, INMEMORY_COMPRESSION 
   FROM   DBA_TABLES 
   WHERE OWNER='SH' AND TABLE_NAME IN ('COUNTRIES', 'PRODUCTS', 'TIMES') 
   ORDER BY TABLE_NAME;
   
   TABLE_NAME                 PAR INMEMORY INMEMORY_COMPRESS
   -------------------------- --- -------- -----------------
   COUNTRIES                  NO  ENABLED  FOR CAPACITY HIGH
   PRODUCTS                   NO  ENABLED  AUTO
   TIMES                      NO  ENABLED  AUTO

   The countries table, which was manually specified as INMEMORY, retains its previous settings. The other tables now have the compression level AUTO.

7. Apply the INMEMORY MEMCOMPRESS AUTO attribute to the countries table:

   ALTER TABLE sh.countries INMEMORY MEMCOMPRESS AUTO;

8. Query the data dictionary to determine the current INMEMORY compression settings:

   SELECT TABLE_NAME, PARTITIONED, INMEMORY, INMEMORY_COMPRESSION 
   FROM   DBA_TABLES 
   WHERE OWNER='SH' AND TABLE_NAME IN ('COUNTRIES', 'PRODUCTS', 'TIMES') 
   ORDER BY TABLE_NAME;
   
   TABLE_NAME                 PAR INMEMORY INMEMORY_COMPRESS
   -------------------------- --- -------- -----------------
   COUNTRIES                  NO  ENABLED  AUTO
   PRODUCTS                   NO  ENABLED  AUTO
   TIMES                      NO  ENABLED  AUTO

   All tables now show the compression level AUTO.

See Also:

"Populating the IM Column Store Manually"

How Automatic In-Memory Population Works

Depending on the INMEMORY_AUTOMATIC_LEVEL setting, population either occurs automatically for objects in the working data set, or depends on the user-specified INMEMORY settings.

When INMEMORY_AUTOMATIC_LEVEL is set to HIGH, the database populates only objects that it decides belong in the working data set. This decision is based on current usage statistics. For example, if a specific partition of the sales table is "hot" (frequently queried), then Automatic In-Memory may populate this partition and keeps in the IM column store as long as it is hot. Automatic In-Memory may also decide to populate cold columns at a higher compression level.

The MEDIUM setting for INMEMORY_AUTOMATIC_LEVEL is similar to the HIGH setting. The only difference is that at the HIGH setting, Oracle Database compresses cold columns. When INMEMORY_AUTOMATIC_LEVEL is LOW, the database populates objects according to their user-specified INMEMORY settings. For example, if objects are set to INMEMORY PRIORITY NONE, then you must manually force population of these objects using a scan or PL/SQL call.

How Automatic In-Memory Eviction Works

The unit of data eviction is an INMEMORY segment.

To ensure that the working data set is always populated, Automatic In-Memory automatically evicts cold (infrequently accessed) segments. Memory pressure occurs when the size of the INMEMORY data set exceeds the available memory for the IM column store, and some populated segments become inactive.

By automatically evicting cold segments, Automatic In-Memory provides the following benefits:

• Improved performance

  Automatic In-Memory improves workload performance because the working data set resides in the IM column store.

• Ease of management

  Evicting cold segments manually involves significant user intervention. Automatic In-Memory automates this process, requiring minimal user intervention.

When INMEMORY_AUTOMATIC_LEVEL Is HIGH

When the INMEMORY_AUTOMATIC_LEVEL initialization parameter is set to HIGH, Automatic In-Memory uses internal statistics to decide when to evict a segment. Also, Automatic In-Memory can recompress cold columns in MEMCOMPRESS AUTO segments to save space. Segments with a PRIORITY setting other than NONE are excluded from the automatic eviction algorithm.

When INMEMORY_AUTOMATIC_LEVEL Is LOW or MEDIUM

When INMEMORY_AUTOMATIC_LEVEL is LOW or MEDIUM, an INMEMORY segment is only eligible for automatic eviction when its priority is NONE. The IM column store only removes a populated segment if it is dropped or moved, the INMEMORY option is removed, or an IM ADO policy acts on it. The basic process is as follows:

1. A population job fails, which means that IM column store space has been exhausted.

2. The database uses internal statistics of eligible populated segments to define the set of objects to evict. The statistics are similar to those used by Heat Map, but do not require Heat Map to be enabled.

3. For each segment in the set, the database checks whether an ADO policy is enabled for the segment:

   • If an enabled policy requires that the segment remain populated, then the ADO policy overrides Automatic In-Memory. The database does nothing.

   • If no policy prevents eviction, then Automatic In-Memory submits tasks to evict the segments.

4. The database evicts any segments that pass the preceding checks, freeing up space in the IM column store.

   The INMEMORY attribute is retained for evicted segments.

For example, a nightly batch job loads a sales partition (with priority NONE), and then queries the partition to trigger population. Because the IM column store is almost at its maximum capacity, only half the rows of the partition are populated. The failure to completely populate the new partition triggers Automatic In-Memory, which evicts a cold segment. A subsequent on-demand populate job for the new partition completely populates the new sales partition.

See Also:

"Space Management Worker Processes (Wnnn)"

User Interface for Automatic In-Memory

Enable and disable Automatic In-Memory using the initialization parameter INMEMORY_AUTOMATIC_LEVEL.

Initialization Parameters

The system-level initialization parameter INMEMORY_AUTOMATIC_LEVEL has the following possible values:

• OFF (Default)

  This option disables Automatic In-Memory, returning the IM column store to its Oracle Database 12c Release 2 (12.2.0.1) behavior.

• LOW

  When under memory pressure, the database evicts cold segments from the IM column store.

• MEDIUM

  This level includes an additional optimization that ensures that any hot segment that was not populated because of memory pressure is populated first.

• HIGH

  Setting INMEMORY_AUTOMATIC_LEVEL to HIGH should improve query performance for all reference workloads with low degradation in transactional throughput. At this level Automatic In-Memory marks all user segments INMEMORY COMPRESS AUTO by default. Any segments that were previously marked INMEMORY by the user retain their attributes.

  The HIGH setting ensures that all database objects are candidates for population to the In-Memory Column store except system-created objects and segments that are annotated with NO INMEMORY explicitly. When INMEMORY_AUTOMATIC_LEVEL=HIGH is set, these objects are excluded from any Automatic In-Memory action, including marking them for In-Memory.

  When changed from HIGH to any other value, segments set to INMEMORY COMPRESS AUTO change to NO INMEMORY.

Note:

Automatic In-Memory does not require the HEAT_MAP initialization parameter to be enabled.

See Also:

Oracle Database Reference to learn more about INMEMORY_AUTOMATIC_LEVEL

Additional Shared Pool Memory Requirements

Oracle recommends that you provision enough memory for the working data set to fit in the IM column store. As a rule of thumb for sizing the additional Automatic In-Memory shared pool requirement, multiply 5 KB by the number of INMEMORY segments of SGA memory. For example, if 10,000 segments have the INMEMORY attribute, then reserve 50 MB of the shared pool for Automatic In-Memory.

DBMS_AUTOIM

The DBMS_AUTOIM package provides APIs to manage the execution of the AIM features as well as a reporting function.

Use the DBMS_AUTOIM package to control the time window in which Automatic In-Memory considers statistics. For example, you can specify that Automatic In-Memory only consider the past month or the past week.

The default value for AIM_STATWINDOW_DAYS is 1 day.

Use the ACTIVITY_REPORT function to generate a report on the automatic creation of IM performance features. These features include Autonomous Join Groups, Autonomous Bloom Filter Optimization, Autonomous Optimized Arithmetic, and Autonomous Vector Optimization.

Data Dictionary Views That Work With DBMS_AUTOIM

The DBA_INMEMORY_AIMTASKS view enables you to track decisions made by Automatic In-Memory tasks. For example, the following query shows tasks ordered by creation date:

SELECT TASK_ID, TO_CHAR(CREATION_TIME,'DD-MON-YY hh24:mi:ss') AS CREATE_TIME, STATE 
FROM DBA_INMEMORY_AIMTASKS
ORDER BY CREATE_TIME;

   TASK_ID CREATE_TIME                 STATE
---------- --------------------------- ----------
         1 11-JUN-19 12:11:09          DONE
         2 11-JUN-19 12:15:12          DONE
         3 11-JUN-19 12:17:12          DONE
         4 11-JUN-19 12:19:12          DONE
         5 11-JUN-19 12:21:14          DONE
...

The DBA_INMEMORY_AIMTASKDETAILS view describes details relating to the tasks. For example, the following query shows the action and state for task 1 for schema sh:

COL TASK_ID FORMAT a99999
COL OBJECT_OWNER FORMAT a7
COL OBJECT_NAME FORMAT a19
COL SUBOBJECT_NAME FORMAT a13
COL ACTION FORMAT a13
COL STATE FORMAT a10

SELECT * FROM DBA_INMEMORY_AIMTASKDETAILS
WHERE  OBJECT_OWNER = 'SH' and TASK_ID = 1;

   TASK_ID OBJECT_ OBJECT_NAME        SUBOBJECT_NAM ACTION        STATE
---------- ------- ------------------ ------------- ------------- –--------
         1 SH      CAL_MONTH_SALES_MV               EVICT         DONE
         1 SH      CHANNELS                         EVICT         DONE
         1 SH      COSTS              COSTS_Q1_1998 POPULATE      SCHEDULED
         1 SH      COSTS              COSTS_Q1_1999 POPULATE      SCHEDULED
...

See Also:

Oracle Database Reference to learn more about DBA_INMEMORY_AIMTASKS and DBA_INMEMORY_AIMTASKDETAILS

The database reference describes the following additional views related to DBMS_AIM_ADMIN:

• V_AUTO_IM_FEATURES, which identifies the various IM performance features that are optimized by AIM.
• DBA_AIM_PERF_FEATURES shows you the table columns on which AIM performance features are enabled.
• DBA_JOINGROUPS has been updated to include the AUTO_CREATED and CREATION_DATE columns in its table of join groups belonging to the user. The automatic creation of join groups is an AIM feature that in certain queries enables the database to eliminate the performance overhead of decompressing and hashing column values.

Controlling Automatic In-Memory

Use the INMEMORY_AUTOMATIC_LEVEL initialization parameter to control Automatic In-Memory.

By default, Automatic In-Memory is set to OFF. Enable it by setting INMEMORY_AUTOMATIC_LEVEL to MEDIUM, LOW, or HIGH.

Prerequisites

To set this parameter with ALTER SYSTEM, you must have the ALTER SYSTEM privilege.

To change the INMEMORY_AUTOMATIC_LEVEL setting:

1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

2. Show the current setting for INMEMORY_AUTOMATIC_LEVEL:

   SQL> SHOW PARAMETER inmemory_automatic_level
   
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- -----
   inmemory_automatic_level             string      OFF

3. Specify INMEMORY_AUTOMATIC_LEVEL using the ALTER SYSTEM statement.

   The following example sets Automatic In-Memory to its fully automated level:

   ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = 'HIGH' SCOPE=SPFILE;

4. Shut down the database, and then reopen it using the SPFILE.

See Also:

Oracle Database Reference to learn more about INMEMORY_AUTOMATIC_LEVEL

Setting the Time Interval for Automatic In-Memory

Use the DBMS_INMEMORY_ADMIN package to set the time interval for the usage statistics checked by Automatic In-Memory.

By default, Automatic In-Memory checks usage statistics for the past 24 hours. You can change the current setting by supplying the AIM_STATWINDOW_DAYS parameter to DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER.

Prerequisites

You must have administrator privileges to execute the DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER and DBMS_INMEMORY_ADMIN.AIM_GET_PARAMETER procedures.

Assumptions

You want to set the interval to 7 days.

To change the Automatic In-Memory interval setting:

1. In SQL*Plus or SQL Developer, log in to the database as a user with administrative privileges.

2. Optionally, check the current setting of the aim_statwindow_days parameter.

   The following example calls the DBMS_INMEMORY_ADMIN.AIM_GET_PARAMETER procedure:

   VARIABLE b_interval NUMBER
   
   BEGIN
     DBMS_INMEMORY_ADMIN.AIM_GET_PARAMETER(
       DBMS_INMEMORY_ADMIN.AIM_STATWINDOW_DAYS, :b_interval);
   END;
   /
   
   PRINT b_interval
   
   B_INTERVAL
   -----------------------------
   1

3. Change the aim_statwindow_days setting with the DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER procedure.

   The following code changes the setting to 7 days:

   BEGIN
     DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER( 
       DBMS_INMEMORY_ADMIN.AIM_STATWINDOW_DAYS, 7);
   END;
   /

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER and DBMS_INMEMORY_ADMIN.AIM_GET_PARAMETER procedures

Enabling ADO for the IM Column Store

Automatic Data Optimization (ADO) creates policies, and automates actions based on those policies, to implement your ILM strategy.

ADO uses Heat Map, which tracks data access patterns for blocks and segments. ADO and Heat Map are a part of Information Lifecycle Management (ILM), which is a set of processes and policies for managing data from creation to archival or deletion. This chapter assumes that you are familiar with ILM, ADO, and Heat Map.

See Also:

Oracle Database VLDB and Partitioning Guide for background about ILM, ADO, and Heat Map

About ADO Policies and the IM Column Store

ADO manages the IM column store through ADO policies. You can only create an ADO policy with an INMEMORY clause at the segment level.

The database treats an ADO policy like an attribute of an object. ADO policies are at the database level, not the instance level. Oracle Database supports the following types of ADO policies for Database In-Memory:

• INMEMORY policy

  This policy marks objects with the INMEMORY attribute, enabling them for population in the IM column store. When set at the table level, the INMEMORY attribute applies to all partitions, whether internal or external.

• Recompression policy

  This policy changes the compression level on an INMEMORY object.

• NO INMEMORY policy

  This policy removes an object from the IM column store and removes its INMEMORY attribute.

Oracle Database supports the following criteria to determine when policies apply:

• A specified number of days since the object was modified

  Obtain this value from the column SEGMENT_WRITE_TIME in the DBA_HEAT_MAP_SEGMENT view.

• A specified number of days since the object was accessed

  This value is the greater value in the columns SEGMENT_WRITE_TIME, FULL_SCAN, and LOOKUP_SCAN in the DBA_HEAT_MAP_SEGMENT view.

• A specified number of days since the object was created

  Obtain this value from the CREATED column in DBA_OBJECTS.

• A user-defined function returns a Boolean value

See Also:

• Oracle Database Reference to learn about the DBA_HEAT_MAP_SEGMENT view

• Oracle Database SQL Language Reference to learn about the INMEMORY clause

Purpose of ADO and the IM Column Store

ADO manages the IM column store as a new data tier.

You can create policies to evict objects from the IM column store when they are being accessed less often, and populate objects when they are being accessed more often and would improve query performance. ADO manages the IM column store using Heat Map statistics.

Purpose of INMEMORY Policies

In many databases, segments undergo heavy modification after creation. To maximize performance, ADO can populate these segments in the IM column store when write activity subsides. For example, if you add a partition to a table every day, then you can create a policy that populates the sales_2016_d100 partition one day after creation:

ALTER TABLE sales MODIFY PARTITION sales_2016_d100 
  ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY 
    PRIORITY HIGH
  AFTER 1 DAYS OF CREATION

Similarly, you may know that write activity on a table subsides two months after creation, and want to populate this object when this time condition is met:

ALTER TABLE 2016_ski_sales
  ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY 
    PRIORITY CRITICAL
  AFTER 60 DAYS OF CREATION

The preceding policy causes all existing and new partitions of the 2016_ski_sales table to inherit the policy. When the segment qualifies for the policy, the database marks every partition independently with the specified INMEMORY clause. If the segment already has an INMEMORY policy, then the database ignores the new policy.

Purpose of Recompression Policies

You may want to compress data in the IM column store based on access patterns. For example, you may want to change a segment from DML compression to query compression 2 days after DML activity on the segment has ceased:

ALTER TABLE lineorders 
  ILM ADD POLICY MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH 
  AFTER 2 DAYS OF NO MODIFICATION

If the object is not populated in IM column store, then this policy only changes the compression attribute. If the object is populated in the IM column store, then ADO repopulates the object using the new compression level. The database ignores the policy if the segment does not already have the INMEMORY attribute.

Purpose of NO INMEMORY Policies

To optimize space in the IM column store, you may want to evict inactive segments using a NO INMEMORY policy. This policy is also useful for preventing population of inactive segments by infrequent queries. For example, if reports on a specific sales partition run frequently during the year, but typically not every week, then you may want to may want to evict this partition after a week of no access:

ALTER TABLE sales MODIFY PARTITION sales_2015_q1
  ILM ADD POLICY NO INMEMORY AFTER 7 DAYS OF NO ACCESS;

If the sales table for 1998 is rarely queried, then you may want to evict after 1 day of no access:

ALTER TABLE sales_1998
  ILM ADD POLICY NO INMEMORY AFTER 1 DAYS OF NO ACCESS;

Queries of an evicted segment are never blocked. The database can always access the data through the traditional buffer cache mechanism.

See Also:

• "Row Data in the Database Buffer Cache"

• Oracle Database VLDB and Partitioning Guide

How ADO Works with Columnar Data

From the ADO perspective, the IM column store is another storage tier.

How Heat Map Works

When enabled, Heat Map automatically discovers data access patterns. ADO uses the Heat Map data to implement user-defined policies at the database level.

Heat Map automatically tracks usage information at the row and segment levels. At the row level, Heat Map tracks data modification times, and then aggregates these times to the block level. At the segment level, Heat Map tracks times for modifications, full table scans, and index lookups.

When an IM column store is enabled, Heat Map tracks access patterns for columnar data. For example, the sales table may be “hot,” whereas the locations table may be “cold.” The ADO algorithms work the same way for columnar data as for row-based data.

The database periodically writes Heat Map data to the data dictionary. The database exposes Heat Map data in data dictionary views. For example, to obtain the read and write time for In-Memory objects, query the ALL_HEAT_MAP_SEGMENT view.

See Also:

• Oracle Database VLDB and Partitioning Guide to learn more about Heat Map

• Oracle Database Reference to learn about the ALL_HEAT_MAP_SEGMENT view

How Policy Evaluation Works

The policy evaluation for IM column store policies uses the same infrastructure as the evaluation of other ADO policies. The database evaluates and executes policies automatically during the maintenance window.

The database evaluates policies using Heat Map statistics, which are stored in the data dictionary. Setting INMEMORY attributes is mostly a metadata operation, and thus minimally affects performance.

ADO uses the Job Scheduler to perform population. The In-Memory Coordinator Process (IMCO) performs the population.

Controls for ADO and the IM Column Store

Enable Heat Map using the HEAT_MAP initialization parameter. Control ADO through a SQL and PL/SQL interface.

ILM Clause in DDL Statements

No new SQL statements are required to create In-Memory policies, but the ILM clause has new options. The following table describes SQL options for ADO and the IM column store. Note that Automatic In-Memory does not support external tables and hybrid partitioned tables.

Table 4-1 ILM Clause for ADO and the IM Column Store

Clause	Description	Examples
SET INMEMORY	Sets the INMEMORY attribute for the object	ALTER TABLE sh.sales ILM ADD POLICY SET INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH SEGMENT AFTER 30 DAYS OF CREATION;
MODIFY INMEMORY	Modifies the compression level for the object	ALTER TABLE sh.customers ILM ADD POLICY MODIFY INMEMORY MEMCOMPRESS FOR QUERY HIGH PRIORITY CRITICAL SEGMENT AFTER 30 DAYS OF CREATION;
NO INMEMORY	Sets the NO INMEMORY attribute for the object	ALTER TABLE sh.products ILM ADD POLICY NO INMEMORY SEGMENT AFTER 30 DAYS OF CREATION;

See Also:

Oracle Database SQL Language Reference to learn more about the ilm_policy_clause of CREATE TABLE

Initialization Parameters

The following table describes initialization parameters that are relevant for ADO and the IM column store.

Table 4-2 Initialization Parameters for ADO and the IM Column Store

Initialization Parameter	Description
COMPATIBLE	Specifies the release with which the database must maintain compatibility. For ADO to manage the IM column store, set this parameter to 12.2.0 or higher.
HEAT_MAP	Enables both the Heat Map and ADO features. For ADO to manage the IM column store, set this parameter to ON.
INMEMORY_SIZE	Enables the IM column store. This parameter must be set to a nonzero value.

PL/SQL Packages

The following table describes PL/SQL packages that are relevant for ADO and the IM column store.

Table 4-3 PL/SQL Packages for ADO and the IM Column Store

Package	Description
DBMS_HEAT_MAP	Displays detailed Heat Map data at the tablespace, segment, object, extent, and block levels.
DBMS_ILM	Implements ILM strategies using ADO policies.
DBMS_ILM_ADMIN	Customizes ADO policy execution.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn more about DBMS_HEAT_MAP, DBMS_ILM, and DBMS_ILM_ADMIN packages

V$ and Data Dictionary Views

The following table describes views that are relevant for ADO and the IM column store.

Table 4-4 Views for ADO and the IM Column Store

View	Description
DBA_HEAT_MAP_SEG_HISTOGRAM	Displays segment access information for all segments visible to the user.
DBA_HEAT_MAP_SEGMENT	Displays the latest segment access time for all segments visible to the user.
DBA_HEATMAP_TOP_OBJECTS	Displays heat map information for the top 10000 objects by default.
DBA_HEATMAP_TOP_TABLESPACES	Displays heat map information for the top 10000 tablespaces.
DBA_ILMDATAMOVEMENTPOLICIES	Displays information specific to data movement-related attributes of an ADO policy in a database. The ACTION_TYPE column describes policies related to the IM column store. Possible values are COMPRESSION, STORAGE, EVICT, and ANNOTATE.
V$HEAT_MAP_SEGMENT	Displays real-time segment access information.

See Also:

Oracle Database Reference to learn more about views

Creating an ADO Policy for the IM Column Store

You can use ADO policies to set, modify, or remove the INMEMORY clause for objects based on Heat Map statistics.

To create an ADO IM column store policy, specify the ILM ADD POLICY clause in an ALTER TABLE statement, followed by one of the following subclauses:

• SET INMEMORY ... SEGMENT

  This option is useful when you want to mark segments with the INMEMORY attribute only when DML activity subsides.

• MODIFY INMEMORY ... MEMCOMPRESS ... SEGMENT

  Storing data uncompressed or at the MEMCOMPRESS FOR DML level is appropriate when it is frequently modified. The alternative compression levels are more suited for queries. If the activity on a segment transitions from mostly writes to mostly reads, then you can use the MODIFY clause to apply a different compression method.

• NO INMEMORY ... SEGMENT

  This option is useful when access to a segment decreases with time (it becomes “cold”), and to prevent population of this segment as a result of random access.

Prerequisites

Before you can use an ADO IM column store policy, you must meet the following prerequisites:

• Enable the IM column store for the database by setting the INMEMORY_SIZE initialization parameter to a nonzero value and restarting the database.

• The HEAT_MAP initialization parameter must be set to ON.

  Heat Map provides data access tracking at the segment-level and data modification tracking at the segment and row level.

• The COMPATIBLE initialization parameter must be set to 12.2.0 or higher.

To create an ADO policy:

1. In SQL*Plus or SQL Developer, log in to the database as a user with the necessary privileges.

2. Use an ALTER TABLE statement with the ILM ADD POLICY ... INMEMORY clause.

Example 4-2 Creating an Eviction Policy

In this example, you create a policy specifying that oe.order_items table is evicted from the IM column store if it has not been accessed in three days. An ADO IM column store policy must be a segment-level policy.

ALTER TABLE oe.order_items ILM ADD POLICY
   NO INMEMORY SEGMENT
   AFTER 3 DAYS OF NO ACCESS;

Example 4-3 Executing an ILM Policy Using DBMS_ILM

You can also evaluate and executes policies manually. Thus, you can programmatically decide when you want an object compressed or tiered. The following example manually executes an ADO task for sh.sales:

DECLARE
  v_executonid NUMBER;
BEGIN
  DBMS_ILM.EXECUTE_ILM ( owner           => 'SH', 
                         object_name     => 'SALES',
                         execution_mode  => DBMS_ILM.ILM_EXECUTION_OFFLINE,
                         task_id         => v_executionid);
END;
/

See Also:

• Oracle Database SQL Language Reference for CREATE TABLE syntax and semantics

• Oracle Database PL/SQL Packages and Types Reference to learn more about the DBMS_ILM package

Allowing AIM to Automatically Enable and Manage Performance Features

You can let the database automatically determine when to enable and manage certain In-Memory features, based on data requirements.

Deciding whether or not your data can benefit from enabling In-Memory features can require considerable analysis of the workload in order to understand the costs and benefits. For example, join groups can provide a performance improvement if rightly applied. In previous Oracle Database releases, you first study the workload to determine where join groups are potentially beneficial, then create them, monitor performance, and then drop them if there is no benefit or a performance regression.

When Automatic In-Memory is set to HIGH and AIM_TASK is enabled via DBMS_AUTOIM.SET_PARAMETER ('AIM_TASK', 'ENABLE'), the database can now do all of this automatically for join groups, In-Memory optimized arithmetic, and vector optimizations. Auto-tuning also automatically caches the hash of a join column when the join uses a bloom filter. It also monitors this change and reverses it if not cost effective.

The DBMS_AUTOIM API

DBMS_AUTOIM is a PL/SQL interface for controlling some AIM settings.

DBMS_AUTOIM provides a SET_PARAMETER procedures for controlling some AIM settings. It also includes an ACTIVITY_REPORT function for gathering data on a selected feature.

DBMS_AUTOIM.SET_PARAMETER

DBMS_AUTOIM.SET_PARAMETER( 
parameter_name    IN NUMBER,
parameter_value   IN NUMBER);

• Enable or disable Automatic IM performance feature creation.

  DBMS_AUTOIM.SET_PARAMETER('AIM_TASK', 'ENABLE');
  DBMS_AUTOIM.SET_PARAMETER('AIM_TASK', 'DISABLE');
  

• Set Automatic IM performance feature creation task frequency, in seconds. The default is 900.

  DBMS_AUTOIM.SET_PARAMETER('INTERVAL',900);

DBMS_AUTOIM.ACTIVITY_REPORT

DBMS_AUTOIM.ACTIVITY_REPORT(
     feature_id          IN number                   := NULL,
     start_time          IN timestamp with time zone := NULL,
     end_time            IN timestamp with time zone := NULL,
     type                IN varchar2                 := 'TEXT',
     level               IN varchar2                 := 'TYPICAL'
) return clob;

Table 4-5 DBMS_AUTOIM.ACTIVITY_REPORT Parameters

Name	Description
feature_id	If NULL, a report is generated for all AIM features. The default is NULL. You can use V$AUTO_IM_FEATURES to get a list of the feature IDs. SQL> select * from v$auto_im_features;
start_time	Start time from which AIM activities are considered for the report. If NULL, start_time defaults to the start time of the last execution. The default is NULL.
end_time	End time for which AIM activities are considered for the report. If NULL, end_time defaults to the end time of the last execution. The default is NULL.
type	Type of the report. Possible values: 'TEXT', 'HTML', and 'XML'. The default is 'TEXT'.
level	Level of verbosity of the report. Possible values: 'BASIC' and 'DETAILED' Default is 'BASIC'.

Example

This example creates a typical report of AutoIM activity in last 24 hours.

SQL>set long 2000000
SQL>set heading off
SQL>select dbms_autoim.activity_report() from dual;

See Also:

The PL/SQL Packages and Types Reference provides more information about DBMS_AUTOIM.

Views for Inspecting AIM

Two views provide information about AIM performance features.

The following AIM views are available.

• V$AUTO_IM_FEATURES

  Describes table columns on which AIM performance features are enabled.

• DBA_AIM_PERF_FEATURES

  Describes the In-Memory Column Store performance features that are optimized by AIM.

See Also:

The links above to the Oracle Database Reference provide more information about these views.