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.

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.

ALTER TABLE sh.sales 
  ILM ADD POLICY 
    SET INMEMORY 
    MEMCOMPRESS FOR QUERY LOW
    PRIORITY HIGH 
    SEGMENT 
    AFTER 30 DAYS OF CREATION;

ALTER TABLE sh.customers 
  ILM ADD POLICY 
    MODIFY INMEMORY 
    MEMCOMPRESS FOR QUERY HIGH
    PRIORITY CRITICAL 
    SEGMENT 
    AFTER 30 DAYS OF CREATION;

ALTER TABLE sh.products 
  ILM ADD POLICY 
    NO INMEMORY 
    SEGMENT 
    AFTER 30 DAYS OF CREATION;

Initialization Parameters

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

PL/SQL Packages

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

V$ and Data Dictionary Views

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

• 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.

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.

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_INMEMORY_ADMIN

Use the DBMS_INMEMORY_ADMIN 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.

Use the DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER procedure to set the AIM_STATWINDOW_DAYS constant. For example, to set the sliding statistics window to 7 days, execute the following program:

EXEC DBMS_INMEMORY_ADMIN.AIM_SET_PARAMETER ( DBMS_INMEMORY_ADMIN.AIM_STATWINDOW_DAYS, 7 );

The default value for AIM_STATWINDOW_DAYS is 31 days.

The corresponding DBMS_INMEMORY_ADMIN.AIM_GET_PARAMETER procedure obtains the current setting for AIM_STATWINDOW_DAYS.

V$ VIEWS

The V$IM_ADOTASKS and DBA_INMEMORY_AIMTASKS views enable you to track decisions made by Automatic In-Memory tasks. The V$IM_ADOTASKDETAILS and DBA_INMEMORY_AIMTASKDETAILS views describe details relating to the tasks.

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

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. Specify INMEMORY_AUTOMATIC_LEVEL using the ALTER SYSTEM statement.

   The following example disables Automatic In-Memory:

   ALTER SYSTEM SET INMEMORY_AUTOMATIC_LEVEL = 'OFF' SCOPE=BOTH;

By default, Automatic In-Memory checks usage statistics for the past 31 days. 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
   -----------------------------
   31

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