1. Database In-Memory Guide
  2. Configuring the IM Column Store
  3. Enabling Objects for In-Memory Population

4 Enabling Objects for In-Memory Population

This chapter explains how to enable and disable objects for population in the IM column store, including setting compression and priority options.

This chapter contains the following topics:

4.1 About In-Memory Population

In-Memory population (population) occurs when the database reads existing row-format data from disk, transforms it into columnar format, and then stores it in the IM column store. Only objects with the INMEMORY attribute are eligible for population.

This section contains the following topics:

  • Purpose of In-Memory Population
    The IM column store does not automatically load all objects in the database into the IM column store.
  • How In-Memory Population Works
    You can specify that the database populates objects in the IM column store either at database instance startup or when INMEMORY objects are accessed. The population algorithm also varies depending on whether you use single-instance or Oracle RAC.
  • Controls for In-Memory Population
    Use the INMEMORY clause in data definition language (DDL) statements to specify which objects are eligible for population into the IM column store. You can enable tablespaces, tables, partitions, and materialized views.

4.1.1 Purpose of In-Memory Population

The IM column store does not automatically load all objects in the database into the IM column store.

If you do not use DDL to specify any objects as INMEMORY, then the IM column store remains empty. Population is necessary to transform rows from user-specified INMEMORY objects into columnar format, so that they are available for analytic queries.

Population, which transforms existing data on disk into columnar format, is different from repopulation, which loads new data into the IM column store. Because IMCUs are read-only structures, Oracle Database does not populate them when rows change. Rather, the database records the row changes in a transaction journal, and then creates new IMCUs as part of repopulation.

Related Topics

4.1.2 How In-Memory Population Works

You can specify that the database populates objects in the IM column store either at database instance startup or when INMEMORY objects are accessed. The population algorithm also varies depending on whether you use single-instance or Oracle RAC.

This section contains the following topics:

4.1.2.1 Prioritization of In-Memory Population

DDL statements include an INMEMORY PRIORITY subclause that provides more control over the population queue.

Note:

The INMEMORY PRIORITY subclause controls the priority of population, but not the speed of population.

The priority level setting applies to an entire table, partition, or subpartition, not to different column subsets. Setting the INMEMORY attribute on an object means that this object is a candidate for population in the IM column store. It does not mean that the database immediately populates the object. Oracle Database manages prioritization as follows:

  • On-demand population

    By default, the INMEMORY PRIORITY parameter is set to NONE. In this case, the database only populates the object when it is accessed through a full table scan. If the object is never accessed, or if it is accessed only through an index scan or fetch by rowid, then population never occurs.

  • Priority-based population

    When PRIORITY is set to a value other than NONE, Oracle database automatically populates the objects using an internally managed priority queue. In this case, a full scan is not a necessary condition for population. The database does the following:

    • Populates columnar data in the IM column store automatically after the database instance restarts

    • Queues population of INMEMORY objects based on the specified priority level

      For example, a table altered with INMEMORY PRIORITY CRITICAL takes precedence over a table altered with INMEMORY PRIORITY HIGH, which in turn takes precedence over a table altered with INMEMORY PRIORITY LOW. If the IM column store has insufficient space, then Oracle Database does not populate additional objects until space is available.

    • Waits to return from ALTER TABLE or ALTER MATERIALIZED VIEW statements until the changes to the object are recorded in the IM column store

After a segment is populated in the IM column store, the database only evicts it when the segment is dropped or moved, or the segment is updated with the NO INMEMORY attribute. You can evict a segment manually or by means of an ADO policy.

Example 4-1 Population of an Object in the IM Column Store

Before completing this example, the IM column store must be enabled for the database.

  1. Log in to the database as an administrator, and then query the customers table as follows: 

    SELECT cust_id, cust_last_name, cust_first_name 
FROM   sh.customers 
WHERE  cust_city = 'Hyderabad' 
AND    cust_income_level LIKE 'C%' 
AND    cust_year_of_birth > 1960;

  2. Display the execution plan for the query:

    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));
SQL_ID  frgk9dbaftmm9, child number 0
-------------------------------------
SELECT cust_id, cust_last_name, cust_first_name FROM   sh.customers
WHERE  cust_city = 'Hyderabad' AND    cust_income_level LIKE 'C%' AND
 cust_year_of_birth > 1960

Plan hash value: 2008213504

-------------------------------------------------------------------------------
| Id| Operation         | Name      |Starts|E-Rows|A-Rows|   A-Time   |Buffers|
-------------------------------------------------------------------------------
|  0| SELECT STATEMENT  |           |     1|      |    6 |00:00:00.01 |   1523|
|* 1|  TABLE ACCESS FULL| CUSTOMERS |     1|    6 |    6 |00:00:00.01 |   1523|
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND
              "CUST_INCOME_LEVEL" LIKE 'C%'))

  3. Enable the sh.customers table for population in the IM column store: 

    ALTER TABLE sh.customers INMEMORY;

    The preceding statement uses the default priority of NONE. A full scan is required to populate objects with no priority.

  4. To determine whether data from the sh.customers table has been populated in the IM column store, execute the following query (sample output included): 

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

no rows selected

    In this case, no segments are populated in the IM column store because the sh.customers table has not yet been scanned.

  5. Query sh.customers using the same statement as in Step 1: 

    SELECT cust_id, cust_last_name, cust_first_name 
FROM   sh.customers 
WHERE  cust_city = 'Hyderabad' 
AND    cust_income_level LIKE 'C%' 
AND    cust_year_of_birth > 1960;

  6. Querying the cursor shows that the database performed a full scan and accessed the IM column store:

    SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(FORMAT=>'+ALLSTATS'));
SQL_ID  frgk9dbaftmm9, child number 0
-------------------------------------
SELECT cust_id, cust_last_name, cust_first_name FROM   sh.customers
WHERE  cust_city = 'Hyderabad' AND    cust_income_level LIKE 'C%' AND
 cust_year_of_birth > 1960

Plan hash value: 2008213504

---------------------------------------------------------------------------------
| Id| Operation           | Name            |Starts|E-Rows|A-Rows|A-Time|Buffers|
---------------------------------------------------------------------------------
|  0| SELECT STATEMENT           |           |    1|     | 6 |00:00:00.02| 1523 |
|* 1|  TABLE ACCESS INMEMORY FULL| CUSTOMERS |    1|    6| 6 |00:00:00.02| 1523 |
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - inmemory(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND
              "CUST_INCOME_LEVEL" LIKE 'C%'))
       filter(("CUST_CITY"='Hyderabad' AND "CUST_YEAR_OF_BIRTH">1960 AND
              "CUST_INCOME_LEVEL" LIKE 'C%'))

  7. Query V$IM_SEGMENTS again (sample output included): 

    COL SEGMENT_NAME FORMAT a20

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

SEGMENT_NAME         POPULATE_STATUS
-------------------- ---------------
CUSTOMERS            COMPLETED

    The value COMPLETED in POPULATE_STATUS means that the table is populated in the IM column store.

  8. The DBA_FEATURE_USAGE_STATISTICS view confirms that the database used the IM column store to retrieve the results:
    COL NAME FORMAT a25
SELECT ul.NAME, ul.DETECTED_USAGES 
FROM   DBA_FEATURE_USAGE_STATISTICS ul 
WHERE  ul.VERSION= (SELECT MAX(u2.VERSION) 
                    FROM   DBA_FEATURE_USAGE_STATISTICS u2 
                    WHERE  u2.NAME = ul.NAME 
                    AND    ul.NAME LIKE '%Column Store%');

NAME                      DETECTED_USAGES
------------------------- ---------------
In-Memory Column Store    1

See Also:

"Priority Options for In-Memory Population"

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

4.1.2.2 How Background Processes Populate IMCUs

During population, the database reads data from disk in its row format, pivots the rows to create columns, and then compresses the data into In-Memory Compression Units (IMCUs).

Worker processes (Wnnn) populate the data in the IM column store. Each worker process operates on a subset of database blocks from the object. Population is a streaming mechanism, simultaneously compressing the data and converting it into columnar format.

The INMEMORY_MAX_POPULATE_SERVERS initialization parameter specifies the maximum number of worker processes to use for IM column store population. By default, the setting is one half of CPU_COUNT. Set this parameter to an appropriate value for your environment. More worker processes result in faster population, but they use more CPU resources. Fewer worker processes result in slower population, which reduces CPU overhead.

Note:

If INMEMORY_MAX_POPULATE_SERVERS is set to 0, then population is disabled.

See Also:

Oracle Database Reference for more information about the INMEMORY_MAX_POPULATE_SERVERS initialization parameter

4.1.3 Controls for In-Memory Population

Use the INMEMORY clause in data definition language (DDL) statements to specify which objects are eligible for population into the IM column store. You can enable tablespaces, tables, partitions, and materialized views.

This section contains the following topics:

  • The INMEMORY Subclause
    INMEMORY is a segment-level attribute, not a column-level attribute. However, you can apply the INMEMORY attribute to a subset of columns within a specific object.
  • Priority Options for In-Memory Population
    When you enable a database object for the IM column store, you can either enable Oracle Database to control when the object is populated in the IM column store (default), or you can specify a priority level that determines the priority of the object in the population queue.
  • IM Column Store Compression Methods
    Depending on your requirement, you can compress In-Memory objects at different levels.
  • Oracle Compression Advisor
    Oracle Compression Advisor estimates the compression ratio that you can realize using the MEMCOMPRESS clause. The advisor uses the DBMS_COMPRESSION interface.
4.1.3.1 The INMEMORY Subclause

INMEMORY is a segment-level attribute, not a column-level attribute. However, you can apply the INMEMORY attribute to a subset of columns within a specific object.

To enable or disable an object for the IM column store, specify the INMEMORY clause in any of the following statements:

  • CREATE TABLESPACE or ALTER TABLESPACE

    By default, all tables and materialized views in the tablespace are enabled for the IM column store. Individual tables and materialized views in the tablespace may have different INMEMORY attributes. The attributes for individual database objects override the attributes for the tablespace.

  • CREATE TABLE or ALTER TABLE

    By default, the IM column store populates all nonvirtual columns in the table. You can specify all or a subset of the columns for a table. For example, you might exclude the weight_class and catalog_url columns in oe.product_information from eligibility. For a partitioned table, you can populate all or a subset of the partitions in the IM column store. By default, for a partitioned table, all table partitions inherit the INMEMORY attribute.

  • CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW

    For a partitioned materialized view, you can populate all or a subset of the partitions in the IM column store.

The INMEMORY column in the DBA_TABLES view indicates which tables have the INMEMORY attribute set (ENABLED) or not set (DISABLED).

The following objects are not eligible for population in the IM column store:

  • Indexes

  • Index-organized tables

  • Hash clusters

  • Objects owned by the SYS user and stored in the SYSTEM or SYSAUX tablespace

If you enable a table for the IM column store and it contains any of the following types of columns, then these columns will not be populated in the IM column store:

  • Out-of-line columns (varrays, nested table columns, and out-of-line LOBs)

  • Columns that use the LONG or LONG RAW data types

  • Extended data type columns

Example 4-2 Specifying a Table as INMEMORY

Assume that you are connected to the database as user sh. You enable the customers table for population in the IM column store, using the default compression level of FOR QUERY LOW (see "In-Memory Compression"): 

SQL> SELECT TABLE_NAME, INMEMORY FROM USER_TABLES WHERE TABLE_NAME = 'CUSTOMERS';

TABLE_NAME INMEMORY
---------- --------
CUSTOMERS  DISABLED

SQL> ALTER TABLE customers INMEMORY;

Table altered.

SQL> SELECT TABLE_NAME, INMEMORY, INMEMORY_COMPRESSION FROM USER_TABLES WHERE TABLE_NAME='CUSTOMERS';

TABLE_NAME INMEMORY INMEMORY_COMPRESS
---------- -------- -----------------
CUSTOMERS  ENABLED  FOR QUERY LOW

See Also:

Enabling the IM Column Store for a Database
4.1.3.2 Priority Options for In-Memory Population

When you enable a database object for the IM column store, you can either enable Oracle Database to control when the object is populated in the IM column store (default), or you can specify a priority level that determines the priority of the object in the population queue.

Oracle SQL includes an INMEMORY PRIORITY clause that provides more control over the queue for population. For example, it might be more important or less important to populate a database object's data before populating the data for other database objects.

Video:

The following table describes the supported priority levels.

Table 4-1 Priority Levels for Populating a Database Object in the IM Column Store

CREATE/ALTER Syntax Description

PRIORITY NONE

The database populates the object on demand only. A full scan of the database object triggers the population of the object into the IM column store.

This is the default level when PRIORITY is not included in the INMEMORY clause.

PRIORITY LOW

The database assigns the object a low priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed.

The object is populated in the IM column store before database objects with the following priority level: NONE. The database object's data is populated in the IM column store after database objects with the following priority levels: MEDIUM, HIGH, or CRITICAL.

PRIORITY MEDIUM

The database assigns the object a medium priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed.

The database object is populated in the IM column store before database objects with the following priority levels: NONE or LOW. The database object's data is populated in the IM column store after database objects with the following priority levels: HIGH or CRITICAL.

PRIORITY HIGH

The database assigns the object a high priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed.

The database object's data is populated in the IM column store before database objects with the following priority levels: NONE, LOW, or MEDIUM. The database object's data is populated in the IM column store after database objects with the following priority level: CRITICAL.

PRIORITY CRITICAL

The database assigns the object a low priority and populates it after startup based on its position in the queue. Population does not depend on whether the object is accessed.

The database object's data is populated in the IM column store before database objects with the following priority levels: NONE, LOW, MEDIUM, or HIGH.

When more than one database object has a priority level other than NONE, Oracle Database queues all of the data for the database objects to be populated in the IM column store based on priority level. Database objects with the CRITICAL priority level are populated first; database objects with the HIGH priority level are populated next, and so on. If there is no space remaining in the IM column store, then no additional objects are populated in it until sufficient space becomes available.

Note:

If you specify all objects as CRITICAL, then the database does not consider any object as more critical than any other.

When a database is restarted, all of the data for database objects with a priority level other than NONE are populated in the IM column store during startup. For a database object with a priority level other than NONE, an ALTER TABLE or ALTER MATERIALIZED VIEW DDL statement involving the database object does not return until the DDL changes are recorded in the IM column store.

Note:

  • The priority level setting must apply to an entire table or to a table partition. Specifying different IM column store priority levels for different subsets of columns in a table is not permitted.

  • If a segment on disk is 64 KB or less, then it is not populated in the IM column store. Therefore, some small database objects that were enabled for the IM column store might not be populated.

See Also:

4.1.3.3 IM Column Store Compression Methods

Depending on your requirement, you can compress In-Memory objects at different levels.

Typically, compression is a space-saving mechanism. However, the IM column store can compress data using a new set of algorithms that also improve query performance. If the columnar data is compressed using the FOR DML or FOR QUERY options, then SQL queries execute directly on the compressed data. Thus, scanning and filtering operations execute on a much smaller amount of data. The database only decompresses data when it is required for the result set.

Video:

The V$IM_SEGMENTS and V$IM_COLUMN_LEVEL views indicate the current compression level. You can change compression levels by using the appropriate ALTER command. If a table is currently populated in the IM column store, and if you change any INMEMORY attribute of the table other than PRIORITY, then the database evicts the table from the IM column store. The repopulation behavior depends on the PRIORITY setting.

The following table summarizes the data compression methods supported in the IM column store.

Table 4-2 IM Column Store Compression Methods

CREATE/ALTER Syntax Description

NO MEMCOMPRESS

The data is not compressed.

MEMCOMPRESS FOR DML

This method results in the best DML performance.

This method compresses IM column store data the least, with the exception of NO MEMCOMPRESS.

Note:

This compression method is not supported for CELLMEMORY storage on Exadata flash cache.

MEMCOMPRESS FOR QUERY LOW

This method results in the best query performance.

This method compresses IM column store data more than MEMCOMPRESS FOR DML but less than MEMCOMPRESS FOR QUERY HIGH.

This method is the default when the INMEMORY clause is specified without a compression method in a CREATE or ALTER SQL statement or when MEMCOMPRESS FOR QUERY is specified without including either LOW or HIGH.

MEMCOMPRESS FOR QUERY HIGH

This method results in good query performance, and saves space.

This method compresses IM column store data more than MEMCOMPRESS FOR QUERY LOW but less than MEMCOMPRESS FOR CAPACITY LOW.

MEMCOMPRESS FOR CAPACITY LOW

This method balances space saving and query performance, with a bias toward space saving.

This method compresses IM column store data more than MEMCOMPRESS FOR QUERY HIGH but less than MEMCOMPRESS FOR CAPACITY HIGH. This method applies a proprietary compression technique called Oracle Zip (OZIP) that offers extremely fast decompression that is tuned specifically for Oracle Database. That data must be decompressed before it can be scanned.

This method is the default when MEMCOMPRESS FOR CAPACITY is specified without including either LOW or HIGH.

MEMCOMPRESS FOR CAPACITY HIGH

This method results in the best space saving.

This method compresses IM column store data the most.

In a SQL statement, the MEMCOMPRESS keyword must be preceded by the INMEMORY keyword.

See Also:

4.1.3.4 Oracle Compression Advisor

Oracle Compression Advisor estimates the compression ratio that you can realize using the MEMCOMPRESS clause. The advisor uses the DBMS_COMPRESSION interface.

When you run DBMS_COMPRESSION.GET_COMPRESSION_RATIO for a table, Oracle Database analyzes a sample of the rows. For this reason, Oracle Compression Advisor provides a good estimate of the compression results that a table achieves after it is populated into the IM column store.

See Also:

Oracle Database PL/SQL Packages and Types Reference to learn about DBMS_COMPRESSION.GET_COMPRESSION_RATIO

4.2 Enabling and Disabling Tables for the IM Column Store

Enable a table for the IM column store by including an INMEMORY clause in a CREATE TABLE or ALTER TABLE statement. Disable a table for the IM column store by including a NO INMEMORY clause in a CREATE TABLE or ALTER TABLE statement.

This section contains the following topics:

4.2.1 Enabling New Tables for the In-Memory Column Store

You enable a new table for the IM column store by including an INMEMORY clause in a CREATE TABLE statement.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable a new table for the IM column store:

  1. Log in to the database as a user with the necessary privileges to create the table.

  2. Run a CREATE TABLE statement with an INMEMORY clause or a NO INMEMORY clause.

See Also:

4.2.2 Enabling and Disabling Existing Tables for the IM Column Store

Enable or disable an existing table for the IM column store by including an INMEMORY or NO INMEMORY clause in an ALTER TABLE statement.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable or disable an existing table for the IM column store:

  1. Log in to the database as a user with ALTER TABLE privileges.

  2. Run an ALTER TABLE statement with an INMEMORY clause or a NO INMEMORY clause.

  3. Optionally, to view metadata (size, priority, compression level) about the In-Memory segment, query V$IM_SEGMENTS.

See Also:

4.2.3 Enabling and Disabling Tables for the IM Column Store: Examples

The following examples illustrate how to enable or disable tables for the IM column store.

Example 4-3 Creating a Table and Enabling It for the IM Column Store

The following example creates the test_inmem table and enables it for the IM column store: 

CREATE TABLE test_inmem (
     id        NUMBER(5) PRIMARY KEY,
     test_col  VARCHAR2(15))
  INMEMORY;

This example uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE.

Example 4-4 Enabling a Table for the IM Column Store

The following DDL statement enables the sh.sales table for the IM column store: 

ALTER TABLE sh.sales INMEMORY;

The preceding statement uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY and PRIORITY NONE.

The following query causes a full scan of sales, which populates the table into the IM column store: 

SELECT /*+ FULL(sales) NO_PARALLEL(sales) */ COUNT(*) FROM sh.sales;

The following query shows the population status of sales (sample output included): 

COL OWNER FORMAT a3
COL NAME FORMAT a10
COL STATUS FORMAT a20

SELECT OWNER, SEGMENT_NAME NAME, 
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS;

OWN NAME       STATUS
--- ---------- --------------------
SH  SALES      COMPLETED

The following query calculates the compression ratio. The query assumes that the tables are not further compressed on disk.

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a5
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
       v.INMEMORY_SIZE IN_MEM_SIZE,
       ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM   V$IM_SEGMENTS v
ORDER BY 4;

OWNER SEGME  ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ----- ---------- ----------- ----------
SH    SALES     851968     1310720        .65
SH    SALES     835584     1310720        .64
SH    SALES     925696     1310720        .71
SH    SALES     958464     1310720        .73
SH    SALES     950272     1310720        .73
SH    SALES     786432     1310720         .6
SH    SALES     876544     1310720        .67
SH    SALES     753664     1310720        .58
SH    SALES    1081344     1310720        .83
SH    SALES     901120     1310720        .69
SH    SALES     925696     1310720        .71
SH    SALES     933888     1310720        .71
SH    SALES     843776     1310720        .64
SH    SALES     999424     1310720        .76
SH    SALES     581632     1507328        .39
SH    SALES     696320     1507328        .46

16 rows selected.

Example 4-5 Enabling a Table for the IM Column Store with FOR CAPACITY LOW Compression

The following DDL statement enables the oe.product_information table for the IM column store and specifies the compression method FOR CAPACITY LOW: 

ALTER TABLE oe.product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY LOW;

The preceding DDL statement uses the default for the PRIORITY clause, which is NONE. The following query scans the oe.product_information table to populate it (sample output included): 

SELECT /*+ FULL(p) NO_PARALLEL(p) */ COUNT(*) 
FROM   oe.product_information p;

  COUNT(*)
----------
       288

The following query calculates the compression ratio (sample output included):

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.BYTES ORIG_SIZE,
       v.INMEMORY_SIZE IN_MEM_SIZE,
       ROUND(v.BYTES / v.INMEMORY_SIZE, 2) COMP_RATIO
FROM   V$IM_SEGMENTS v
WHERE  SEGMENT_NAME LIKE 'P%'
ORDER BY 4;

OWNER SEGMENT_NAME         ORIG_SIZE IN_MEM_SIZE COMP_RATIO
----- ------------------- ---------- ----------- ----------
OE    PRODUCT_INFORMATION      98304     1310720        .08

Example 4-6 Enabling a Table for the IM Column Store with HIGH Data Population Priority

The following DDL statement enables the oe.product_information table for the IM column store and specifies PRIORITY HIGH for populating the table data in the IM column store: 

ALTER TABLE 
  oe.product_information 
  INMEMORY 
  PRIORITY HIGH;

Example 4-7 Enabling a Table for the IM Column Store with FOR CAPACITY HIGH Compression and LOW Data Population Priority

The following query shows the priority and compression setting for the oe.product_information table: 

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a19
SET PAGESIZE 50000

SELECT v.OWNER, v.SEGMENT_NAME, v.INMEMORY_PRIORITY,
       v.INMEMORY_COMPRESSION
FROM   V$IM_SEGMENTS v
WHERE  SEGMENT_NAME LIKE 'P%';

OWNER SEGMENT_NAME        INMEMORY INMEMORY_COMPRESS
----- ------------------- -------- -----------------
OE    PRODUCT_INFORMATION HIGH     FOR CAPACITY LOW

The following DDL statement alters oe.product_information to use FOR CAPACITY HIGH table compression and PRIORITY LOW: 

ALTER TABLE oe.product_information 
  INMEMORY 
  MEMCOMPRESS FOR CAPACITY HIGH 
  PRIORITY LOW;

Example 4-8 Enabling a Partitioned Table for the IM Column Store

This following DDL statement creates a partitioned table named range_sales and specifies a different compression level for the first three partitions in the IM column store. The last two partitions are not populated in the IM column store. 

CREATE TABLE range_sales
    ( prod_id        NUMBER(6)
    , cust_id        NUMBER
    , time_id        DATE
    , channel_id     CHAR(1)
    , promo_id       NUMBER(6)
    , quantity_sold  NUMBER(3)
    , amount_sold    NUMBER(10,2)
    ) 
PARTITION BY RANGE (time_id)
  (PARTITION SALES_Q4_1999 
      VALUES LESS THAN (TO_DATE('01-JAN-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR DML,
   PARTITION SALES_Q1_2000 
      VALUES LESS THAN (TO_DATE('01-APR-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR QUERY,
   PARTITION SALES_Q2_2000 
      VALUES LESS THAN (TO_DATE('01-JUL-2015','DD-MON-YYYY'))
      INMEMORY MEMCOMPRESS FOR CAPACITY,
   PARTITION SALES_Q3_2000 
      VALUES LESS THAN (TO_DATE('01-OCT-2015','DD-MON-YYYY'))
      NO INMEMORY,
   PARTITION SALES_Q4_2000 
      VALUES LESS THAN (MAXVALUE));

Example 4-9 Disabling a Table for the IM Column Store

To disable a table for the IM column store, specify the NO INMEMORY clause. The following example disables the oe.product_information table for the IM column store: 

ALTER TABLE oe.product_information NO INMEMORY;

You can query the V$IM_SEGMENTS view to list the database objects that are populated in the IM column store.

4.3 Enabling and Disabling Columns for In-Memory Tables

You can specify the INMEMORY clause for individual columns. Both nonvirtual columns and In-Memory virtual columns (IM virtual columns) are eligible for population into the IM column store.

This section contains the following topics:

4.3.1 About IM Virtual Columns

An IM virtual column is like any other column, except that its value is derived by evaluating an expression.

Storing the precalculated IM virtual column values in the IM column store can improve query performance. The expression can include columns from the same table, constants, SQL functions, and user-defined PL/SQL functions (DETERMINISTIC only). You cannot explicitly write to an IM virtual column.

Note:

A virtual column or IM expression counts toward the limit of 1000 columns per populated object.

To populate IM virtual columns in the IM column store, set the INMEMORY_VIRTUAL_COLUMNS initialization parameter to one of the following values:

  • MANUAL (default): If a table is enabled for the IM column store, then no IM virtual columns defined on this table are eligible for population, unless they are explicitly set as INMEMORY.

  • ENABLE: If a table is enabled for the IM column store, then all IM virtual columns defined on this table are eligible for population, unless they are explicitly set as NO INMEMORY.

    By default, the compression level of the column in the IM column store is the same as the table or partition in which it is stored. However, when a different compression level is specified for the IM virtual column, it is populated at the specified compression level.

To specify that no IM virtual columns are populated in the IM column store, set this initialization parameter to DISABLE.

The underlying storage structures for IM virtual columns and IM expressions are the same. However, different mechanisms control IM expressions and IM virtual columns.

Note:

  • The IM column store only populates virtual columns for tables marked INMEMORY.

  • To populate IM virtual columns in the IM column store, the value for the initialization parameter COMPATIBLE must be set to 12.1.0 or higher.

See Also:

4.3.2 Enabling IM Virtual Columns

IM virtual columns improve query performance by avoiding repeated calculations. Also, the database can scan and filter IM virtual columns using techniques such as SIMD vector processing.

Prerequisites

To enable IM virtual columns, the following conditions must be true:

To enable IM virtual columns:

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

  2. Either set the INMEMORY_VIRTUAL_COLUMNS initialization parameter to ENABLE, or enable specific virtual columns for the IM column store.

Example 4-10 Enabling Virtual Columns for the IM Column Store

In this example, you are logged in to the database as SYSTEM. The IM column store is enabled, but population of virtual columns is currently disabled: 

SQL> SHOW PARAMETER INMEMORY_SIZE

NAME                                 TYPE        VALUE
------------------------------------ ----------- -----
inmemory_size                        big integer 200M

SQL> SHOW PARAMETER INMEMORY_VIRTUAL_COLUMNS

NAME                                 TYPE        VALUE
------------------------------------ ----------- -------
inmemory_virtual_columns             string      DISABLE

You add a virtual column to the hr.employees table, and then specify that the table is INMEMORY: 

SQL> ALTER TABLE hr.employees ADD (weekly_sal AS (ROUND(salary*12/52,2)));

Table altered.

SQL> ALTER TABLE hr.employees INMEMORY;

Table altered.

At this stage, weekly_sal is not eligible for population, although the non-virtual columns in hr.employees are eligible for population. The following statement enables weekly_sal, and any other virtual columns in hr.employees, to be populated: 

SQL> ALTER SYSTEM SET INMEMORY_VIRTUAL_COLUMNS=ENABLE SCOPE=SPFILE;

System altered.

Example 4-11 Enabling a Specific IM Virtual Column for the IM Column Store

This example assumes that the INMEMORY_VIRTUAL_COLUMNS initialization parameter is set to MANUAL, which means that IM virtual columns must be added to the IM column store explicitly. This example first creates the hr.admin_emp table: 

CREATE TABLE hr.admin_emp (
      empno      NUMBER(5) PRIMARY KEY,
      ename      VARCHAR2(15) NOT NULL,
      job        VARCHAR2(10),
      sal        NUMBER(7,2),
      hrly_rate  NUMBER(7,2) GENERATED ALWAYS AS (sal/2080),
      deptno     NUMBER(3) NOT NULL)
   INMEMORY;

At this stage, the hrly_rate virtual column is not eligible for population. The following statement explicitly specifies the virtual column as INMEMORY: 

ALTER TABLE hr.admin_emp INMEMORY(hrly_rate);

4.3.3 Enabling a Subset of Columns for the IM Column Store: Example

This example enables all columns in the oe.product_information table for the IM column store except weight_class and catalog_url. It also specifies different IM column store compression methods for the columns enabled for the IM column store. 

ALTER TABLE oe.product_information  
   INMEMORY MEMCOMPRESS FOR QUERY (
      product_id, product_name, category_id, supplier_id, min_price)
   INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (
      product_description, warranty_period, product_status, list_price)
   NO INMEMORY (
      weight_class, catalog_url);

Specifically, this example specifies the following:

  • The list of columns starting with product_id and ending with min_price are enabled for the IM column store with the MEMCOMPRESS FOR QUERY compression method.

  • The list of columns starting with product_description and ending with list_price are enabled for the IM column store with the MEMCOMPRESS FOR CAPACITY HIGH compression method.

  • The weight_class and catalog_url columns are not enabled for the IM column store.

  • The table uses the default for the PRIORITY clause, which is PRIORITY NONE.

Note:

The priority level setting must apply to an entire table or partition. Specifying different IM column store priority levels for different subsets of columns in a table is not allowed.

To determine the selective column compression levels defined for a database object, query the V$IM_COLUMN_LEVEL view, as shown in the following example: 

COL TABLE_NAME FORMAT a20
COL COLUMN_NAME FORMAT a20

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION 
FROM   V$IM_COLUMN_LEVEL 
WHERE  TABLE_NAME = 'PRODUCT_INFORMATION'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
PRODUCT_INFORMATION  CATALOG_URL          NO INMEMORY
PRODUCT_INFORMATION  CATEGORY_ID          FOR QUERY LOW
PRODUCT_INFORMATION  LIST_PRICE           FOR CAPACITY HIGH
PRODUCT_INFORMATION  MIN_PRICE            FOR QUERY LOW
PRODUCT_INFORMATION  PRODUCT_DESCRIPTION  FOR CAPACITY HIGH
PRODUCT_INFORMATION  PRODUCT_ID           FOR QUERY LOW
PRODUCT_INFORMATION  PRODUCT_NAME         FOR QUERY LOW
PRODUCT_INFORMATION  PRODUCT_STATUS       FOR CAPACITY HIGH
PRODUCT_INFORMATION  SUPPLIER_ID          FOR QUERY LOW
PRODUCT_INFORMATION  WARRANTY_PERIOD      FOR CAPACITY HIGH
PRODUCT_INFORMATION  WEIGHT_CLASS         NO INMEMORY

See Also:

4.3.4 Specifying INMEMORY Column Attributes on a NO INMEMORY Table: Example

Starting in Oracle Database 12c Release 2 (12.2), you can specify the INMEMORY clause at the column level on an object that is not yet specified as INMEMORY.

In previous releases, the column-level INMEMORY clause was only valid when specified on an INMEMORY table or partition. This restriction meant that a column could not be associated with an INMEMORY clause before the table or partition was associated with an INMEMORY clause.

Starting in Oracle Database 12c Release 2 (12.2), if you specify the INMEMORY clause at the column level, then the database records the attributes of the specified column. If the table is NO INMEMORY (default), then the column-level attributes do not affect how the table is queried until the table or partition is specified as INMEMORY. If you mark the table itself as NO INMEMORY, then the database drops any existing column-level attributes.

In this example, your goal is to ensure that column c3 in a partitioned table is never populated in the IM column store. You perform the following steps:

  1. Create a partitioned table t as follows: 

    CREATE TABLE t (c1 NUMBER, c2 NUMBER, c3 NUMBER) 
  NO INMEMORY -- this clause specifies the table itself as NO INMEMORY
  PARTITION BY LIST (c1) 
    ( PARTITION p1 VALUES (0), 
      PARTITION p2 VALUES (1), 
      PARTITION p3 VALUES (2) );

    Table t is NO INMEMORY. The table is partitioned by list on column c1, and has three partitions: p1, p2, and p3.

  2. Query the compression of the columns in the table (sample output included):

    COL TABLE_NAME FORMAT a20
COL COLUMN_NAME FORMAT a20

SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

no rows selected

    As shown by the output, no column-level INMEMORY attributes are set.

  3. To ensure that column c3 is never populated, apply the NO INMEMORY attribute to column c3: 

    ALTER TABLE t NO INMEMORY (c3);

  4. Query the compression of the columns in the table (sample output included):

    SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------
T                    C1                   DEFAULT
T                    C2                   DEFAULT
T                    C3                   NO INMEMORY

    The database has recorded the NO INMEMORY attribute for c3. The other columns use the default compression.

  5. Specify partition p3 as INMEMORY: 

    ALTER TABLE t 
  MODIFY PARTITION p3 
    INMEMORY PRIORITY CRITICAL;

    Because column c3 was previously specified as NO INMEMORY, initial population of partition p3 will not include column c3.

  6. Specify the entire table as INMEMORY: 

    ALTER TABLE t INMEMORY;

  7. Query the compression of the columns in the table (sample output included):

    SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
T                    C1                   DEFAULT
T                    C2                   DEFAULT
T                    C3                   NO INMEMORY

    The database has retained the NO INMEMORY setting for column c3. The other columns use the default compression.

  8. Apply different compression levels to columns c1 and c2:
    ALTER TABLE t 
  INMEMORY MEMCOMPRESS FOR CAPACITY HIGH (c1) 
  INMEMORY MEMCOMPRESS FOR CAPACITY LOW (c2);

  9. Query the compression of the columns in the table (sample output included):

    SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

TABLE_NAME           COLUMN_NAME          INMEMORY_COMPRESSION
-------------------- -------------------- --------------------------
T                    C1                   FOR CAPACITY HIGH
T                    C2                   FOR CAPACITY LOW
T                    C3                   NO INMEMORY

    Each column now has a different compression level.

  10. Specify the entire table as NO INMEMORY: 

    ALTER TABLE t NO INMEMORY;

  11. Query the compression of the columns in the table (sample output included):

    SELECT TABLE_NAME, COLUMN_NAME, INMEMORY_COMPRESSION
FROM   V$IM_COLUMN_LEVEL
WHERE  TABLE_NAME = 'T'
ORDER BY COLUMN_NAME;

no rows selected

    Because the entire table was specified as NO INMEMORY, the database dropped all column-level INMEMORY attributes.

See Also:

Oracle Database SQL Language Reference for ALTER TABLE syntax and semantics

4.4 Enabling and Disabling Tablespaces for the IM Column Store

You can enable or disable tablespaces for the IM column store.

Enable a tablespace for the IM column store during tablespace creation with a CREATE TABLESPACE statement that includes the INMEMORY clause. You can also alter a tablespace to enable it for the IM column store with an ALTER TABLESPACE statement that includes the INMEMORY clause.

Disable a tablespace for the IM column store by including a NO INMEMORY clause in a CREATE TABLESPACE or ALTER TABLESPACE statement.

When a tablespace is enabled for the IM column store, all tables and materialized views in the tablespace are enabled for the IM column store by default. The INMEMORY clause is the same for tables, materialized views, and tablespaces. The DEFAULT storage clause is required before the INMEMORY clause when enabling a tablespace for the IM column store and before the NO INMEMORY clause when disabling a tablespace for the IM column store.

When a tablespace is enabled for the IM column store, individual tables and materialized views in the tablespace can have different in-memory settings, and the settings for individual database objects override the settings for the tablespace. For example, if the tablespace is set to PRIORITY LOW for populating data in memory, and if a table in the tablespace is set to PRIORITY HIGH, then the table uses PRIORITY HIGH.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable or disable tablespaces for the IM column store:

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

  2. Run a CREATE TABLESPACE or ALTER TABLESPACE statement with an INMEMORY clause or a NO INMEMORY clause.

Example 4-12 Creating a Tablespace and Enabling It for the IM Column Store

The following example creates the users01 tablespace and enables it for the IM column store: 

CREATE TABLESPACE users01 
   DATAFILE 'users01.dbf' SIZE 40M 
   ONLINE
   DEFAULT INMEMORY;

This example uses the defaults for the INMEMORY clause. Therefore, MEMCOMPRESS FOR QUERY is used, and PRIORITY NONE is used.

Example 4-13 Altering a Tablespace to Enable It for the IM Column Store

The following example alters the users01 tablespace to enable it for the IM column store and specifies FOR CAPACITY HIGH compression for the database objects in the tablespace and PRIORITY LOW for populating data in memory: 

ALTER TABLESPACE users01 DEFAULT INMEMORY 
   MEMCOMPRESS FOR CAPACITY HIGH 
   PRIORITY LOW;

4.5 Enabling and Disabling Materialized Views for the IM Column Store

You can enable and disable materialized views for the IM column store.

Enable a materialized view for the IM column store by including an INMEMORY clause in a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement. Disable a materialized view for the IM column store by including a NO INMEMORY clause in a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement.

Prerequisites

Ensure that the IM column store is enabled for the database. See "Enabling the IM Column Store for a Database".

To enable or disable a materialized view for the IM column store:

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

  2. Run a CREATE MATERIALIZED VIEW or ALTER MATERIALIZED VIEW statement with either an INMEMORY clause or a NO INMEMORY clause.

Example 4-14 Creating a Materialized View and Enabling It for the IM Column Store

The following statement creates the oe.prod_info_mv materialized view and enables it for the IM column store: 

CREATE MATERIALIZED VIEW oe.prod_info_mv INMEMORY 
  AS SELECT * FROM oe.product_information;

This example uses the defaults for the INMEMORY clause: MEMCOMPRESS FOR QUERY LOW and PRIORITY NONE.

Example 4-15 Enabling a Materialized View for the IM Column Store with HIGH Data Population Priority

The following statement enables the oe.prod_info_mv materialized view for the IM column store: 

ALTER MATERIALIZED VIEW oe.prod_info_mv INMEMORY PRIORITY HIGH;

This example uses the default compression: MEMCOMPRESS FOR QUERY LOW.

See Also:

Oracle Database SQL Language Reference to learn more about the CREATE or ALTER MATERIALIZED VIEW statements

4.6 Forcing Initial Population of an In-Memory Object: Tutorial

Enabling an object for In-Memory population does not immediately populate the object.

If you enabled an object with PRIORITY set to NONE, and if you want to populate it immediately, then you have the following options:

  • Force a full table scan

  • Use the DBMS_INMEMORY.POPULATE procedure

Assumptions

This tutorial assumes the following:

  • The IM column store is enabled.

  • You want to enable the sh.customers table for In-Memory population, using the default PRIORITY of NONE.

  • You want to force the immediate population of sh.customers into the IM column store.

To force population of an INMEMORY table:

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

  2. Apply the INMEMORY attribute to the table.

    For example, enable sh.customers for IM population as follows: 

    ALTER TABLE sh.customers INMEMORY;

  3. Optionally, to check the population status, query V$IM_SEGMENTS.

    For example, use the following statement (sample output included):

    COL OWNER FORMAT a10;
COL NAME FORMAT a25;
COL STATUS FORMAT a10;

SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'CUSTOMERS';

no rows selected

    The preceding output shows that the object is not yet populated in the IM column store.

  4. Execute the DBMS_INMEMORY.POPULATE procedure on the table.

    For example, enable this procedure against sh.customers as follows: 

    EXEC DBMS_INMEMORY.POPULATE('SH', 'CUSTOMERS');

  5. Optionally, to check the population status, query V$IM_SEGMENTS.

    For example, use the following statement (sample output included):

    SELECT OWNER, SEGMENT_NAME NAME,
       POPULATE_STATUS STATUS
FROM   V$IM_SEGMENTS
WHERE  SEGMENT_NAME = 'CUSTOMERS';

OWN NAME       STATUS
--- ---------- --------------------
SH  CUSTOMERS  COMPLETED

    The object is now populated in the IM column store.

See Also:

4.7 Enabling ADO for the IM Column Store

Information Lifecycle Management (ILM) is a set of processes and policies for managing data from creation to archival or deletion.

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.

Note:

This chapter assumes that you are familiar with the basic concepts of ILM, ADO, and Heap Map. For more background, see Oracle Database VLDB and Partitioning Guide.

This section contains the following topics:

4.7.1 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, enables them for population in the IM column store.

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

4.7.2 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:

4.7.3 How ADO Works with Columnar Data

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

This section contains the following topics:

  • 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.
  • 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.
4.7.3.1 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 Maps 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:

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

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

Table 4-3 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;

Initialization Parameters

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

Table 4-4 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-5 PL/SQL Packages for ADO and the IM Column Store

Package Description
DBMS_HEATMAP 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.

V$ and Data Dictionary Views

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

Table 4-6 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 initialization parameters, packages, and views

4.7.5 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 non-zero 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-16 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-17 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: