DBMS_INMEMORY

85 DBMS_INMEMORY

The DBMS_INMEMORY package provides an interface for In-Memory Column Store (IM column store) functionality.

This chapter contains the following topics:

85.1 DBMS_INMEMORY Overview

This package contains procedures for populating and repopulating the IM column store, and for dropping IM expressions from a specified table.

IM Population and Repopulation

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.

Population, which transforms existing data on disk into columnar format, is different from repopulation, which loads new data into the IM column store. Repopulation occurs automatically after their columnar data undergo significant DML activity.

When an object has the INMEMORY attribute and a priority other than NONE, the database gradually populates the object in the IM column store according to an internal priority queue. Objects with priority of NONE are populated only when they undergo a full scan.

The DBMS_INMEMORY.POPULATE procedure forces immediate population of an object. The DBMS_INMEMORY.POPULATE_WAIT function forces population of objects that meet specified priority criteria, and also specifies a timeout interval within which populate must complete. The DBMS_INMEMORY.REPOPULATE procedure forces immediate repopulation of an object.

85.2 DBMS_INMEMORY Security Model

The DBMS_INMEMORY package subprograms execute with invoker’s rights.

The POPULATE and REPOPULATE procedures require the invoking user to have SELECT privileges on the specified object. For IME_DROP_EXPRESSIONS, the invoking user must have ALTER TABLE privileges on the specified table.

85.3 Summary of DBMS_INMEMORY Subprograms

This table lists and briefly describes the DBMS_INMEMORY package subprograms.

Table 85-1 DBMS_INMEMORY Package Subprograms

Subprogram	Description
IME_DROP_EXPRESSIONS Procedure	Drops a specified set of `SYS_IME` virtual columns from a table
POPULATE Procedure	Forces population of the specified table
POPULATE_WAIT Function	Initiates population of all `INMEMORY` objects that have a priority greater than or equal to the specified priority, and sets a timeout interval within which population must occur
REPOPULATE Procedure	Forces repopulation of the specified table
SEGMENT_DEALLOCATE_VERSIONS Procedure	Deallocates non-current IMCUs in the IM column store

85.3.1 IME_DROP_EXPRESSIONS Procedure

This procedure drops a specified set of SYS_IME virtual columns from a table.

Syntax

DBMS_INMEMORY.IME_DROP_EXPRESSIONS(
   schema_name    IN    VARCHAR2,
   table_name     IN    VARCHAR2,
   column_name    IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 85-2 IME_DROP_EXPRESSIONS Procedure Parameters

Parameter	Description
`schema_name`	The name of the schema that contains the In-Memory table
`table_name`	The name of the In-Memory table that contains the `SYS_IME` columns
`column_name`	The name of the `SYS_IME` column. By default this value is null, which specifies all `SYS_IME` columns in this table.

Usage Notes

Typical reasons for dropping SYS_IME columns are space and performance. The maximum number of SYS_IME columns for a table, regardless of whether the attribute is INMEMORY or NO INMEMORY, is 50. After the 50-expression limit is reached for a table, the database will not add new SYS_IME columns. To make space for new expressions, you must manually drop SYS_IME columns with the DBMS_INMEMORY.IME_DROP_EXPRESSIONS or DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS procedures.

To drop a specified SYS_IME column or all SYS_IME columns in the requested table, use DBMS_INMEMORY.IME_DROP_EXPRESSIONS . To populate these segments again, either invoke the DBMS_INMEMORY.POPULATE procedure, or perform a full table scan.

85.3.2 POPULATE Procedure

This procedure forces population of the specified table, partition, or subpartition into the IM column store.

Syntax

DBMS_INMEMORY.POPULATE(
   schema_name      IN    VARCHAR2,
   table_name       IN    VARCHAR2,
   subobject_name   IN    VARCHAR2 DEFAULT NULL);

Parameters

Table 85-3 POPULATE Procedure Parameters

Parameter	Description
`schema_name`	Name of schema
`table_name`	Name of table
`subobject_name`	Partition or subpartition

85.3.3 POPULATE_WAIT Function

This function initiates population of all INMEMORY objects that have a priority greater than or equal to the specified priority, and returns a status value for the population. A user-specified interval specifies the maximum time that the function waits before returning the value to the caller.

Syntax

DBMS_INMEMORY.POPULATE_WAIT(
   priority    IN    VARCHAR2 DEFAULT 'LOW',
   percentage  IN    NUMBER   DEFAULT 100,
   timeout     IN    NUMBER   DEFAULT 99999999,
   force       IN    VARCHAR2 DEFAULT FALSE)
RETURN VARCHAR2;

Parameters

Table 85-4 POPULATE_WAIT Function Parameters

Parameter	Description
`priority`	Specifies that the database populate all `INMEMORY` objects with the specified priority setting or higher. The default priority is `LOW`. `NONE` is considered lowest priority. If you set to `priority` to `NONE`, then this function waits for all `INMEMORY` objects to populate.
`percentage`	Specifies the percentage of population required for the function to consider population to be complete. The default is `100`. For example, if `percentage` is `50` and `priority` is `NONE`, and if 50% of the `INMEMORY` objects are populated in the IM column store, then the function returns the value `0` (population successful).
`timeout`	Specifies the number of seconds that must pass before the function returns `-1`, which indicates that the populate operation timed out. The default is `99999999` seconds, which is 115.74 days. Assume that `timeout` is `600`, `priority` is `LOW`, and `percentage` is `100`. If 10 minutes pass, but all `PRIORITY LOW` objects are not yet fully populated, then the function returns `-1`.
`force`	Specifies that the database should drop all `INMEMORY` segments that have a priority greater than or equal than the specified priority, and then repopulate these segments. The default is `FALSE`. Assume that the `INMEMORY` attribute applies to the `sales` table, which is partitioned. Only half the `sales` partitions are currently populated in the IM column store. If you execute `POPULATE_WAIT` with `force` set to `TRUE`, then the database drops all `sales` segments, and then repopulates them.

Return Values

The following table describes the possible return values for POPULATE_WAIT. The function returns the values 0, 1, 2, and 3 only if the condition is met before the end of the interval specified by timeout. For example, if timeout is 600, then the function returns 1 only if an out-of-memory error occurs before 600 seconds pass. The function returns -1 only if the end of the timeout interval occurs before the database completes the requested operation.

Table 85-5 Return Values for POPULATE_WAIT

Constant	Value	Description
`POPULATE_TIMEOUT`	`-1`	The function timed out while waiting for population to complete. Existing population jobs continue running in the background after `-1` is returned. Reissuing `POPULATE_TIMEOUT` after `-1` is returned reinitiates population; segments that are already populated are not dropped.
`POPULATE_SUCCESS`	`0`	All objects that met the `priority` criteria were populated to the specified `percentage` of completion.
`POPULATE_OUT_OF_MEMORY`	`1`	The In-Memory pool had insufficient memory to populate the objects that met the `priority` criteria to the specified `percentage` of completion.
`POPULATE_NO_INMEMORY_OBJECTS`	`2`	No `INMEMORY` objects met the specified `priority` criteria.
`POPULATE_INMEMORY_SIZE_ZERO`	`3`	The In-Memory column store is not enabled.

Usage Notes

Sample use cases for ensuring that objects are populated include:

When the database is closed, open the database with STARTUP RESTRICT so that only administrators can access the database, and then execute POPULATE_WAIT with the desired timeout setting. If POPULATE_WAIT returns -1, indicating a timeout, then reexecute POPULATE_WAIT. When the function returns 0, disable the restricted session so that non-administrative users can query the database.
Block database connections by using services or an application tier technique. When no analytic indexes exists, and when the application depends on the IM column store to provide reasonable performance, these techniques prevent runaway queries.

Example 85-1 Specifying a Timeout Interval for In-Memory Population

In this example, the database contains a number of In-Memory tables with a variety of priority settings. Your goal is to populate every In-Memory table to 100% completion in a restricted database session, and then disable the restricted session so that the application can be guaranteed of querying only the In-Memory representations.

Assume that the database is shut down. In SQL*Plus, you connect to an idle instance as SYSDBA, and then execute the following command (sample output included):

SQL> STARTUP RESTRICT
ORACLE instance started.

Total System Global Area 1157624280 bytes
Fixed Size                  8839640 bytes
Variable Size             754974720 bytes
Database Buffers           16777216 bytes
Redo Buffers                7933952 bytes
In-Memory Area            369098752 bytes
Database mounted.
Database opened.

The database is open, but is accessible only to administrative users. You execute the following statements in SQL*Plus (sample output shown in bold):

VARIABLE b_pop_status NUMBER

SELECT DBMS_INMEMORY_ADMIN.POPULATE_WAIT(
         priority   => 'NONE' ,
         percentage => 100    ,
         timeout    => 300    )
  INTO b_pop_status
FROM   DUAL;

PRINT b_pop_status
-1

After 5 minutes, the function returns the number –1. This code indicates that the function timed out while waiting for population to complete. 5 minutes is not long enough to populate all INMEMORY tables. You re-execute the SELECT statement, specifying a 30-minute timeout:

SELECT DBMS_INMEMORY_ADMIN.POPULATE_WAIT(
         priority   => 'NONE' ,
         percentage => 100    ,
         timeout    => 1800   )
  INTO b_pop_status
FROM   DUAL;

PRINT b_pop_status
0

After 8 minutes, the function returns the number 0. This code indicates that all tables are completely populated. You now disable the restricted session so that the application can start query In-Memory objects with full confidence that only In-Memory representations will be accessed:

ALTER SYSTEM DISABLE RESTRICTED SESSION;

85.3.4 REPOPULATE Procedure

This procedure forces repopulation of a table, partition, or subpartition that is currently populated in the IM column store.

Syntax

DBMS_INMEMORY.REPOPULATE(
   schema_name      IN    VARCHAR2,
   table_name       IN    VARCHAR2,
   subobject_name   IN    VARCHAR2 DEFAULT NULL,
   force            IN    BOOLEAN DEFAULT FALSE);

Parameters

Table 85-6 REPOPULATE Procedure Parameters

Parameter	Description
`schema_name`	Name of the schema that owns the object.
`table_name`	Name of the table requiring repopulation.
`subobject_name`	Name of the partition or subpartition. If null, then repopulate the entire table.
`force`	Whether to repopulate all IMCUs in the segment, just as in initial population. The following values are possible for the `force` parameter: `FALSE` — The database repopulates only IMCUs containing modified rows. This is the default. `TRUE` — The database drops the segment, and then rebuilds it. The database increments the statistics and performs all other tasks related to initial population. For example, IMCU 1 contains rows 1 to 500,000, and IMCU 2 contains rows 500,001 to 1,000,000. A statement modifies row 600,000. When `force` is `FALSE`, the database only repopulates IMCU 2. When `force` is `TRUE`, the database repopulates both IMCUs. Consider further that the `INMEMORY_VIRTUAL_COLUMNS` initialization parameter is set to `ENABLE`, and an application creates a new virtual column. When `force` is `FALSE`, the database only repopulates IMCU 2 with the new column. When `force` is `TRUE`, the database repopulates both IMCUs with the new column.

85.3.5 SEGMENT_DEALLOCATE_VERSIONS Procedure

This procedure deallocates non-current IMCUs in the IM column store.

Syntax

DBMS_INMEMORY.SEGMENT_DEALLOCATE_VERSIONS(
   SCHEMA_NAME      IN  VARCHAR2,
   TABLE_NAME       IN  VARCHAR2,
   PARTITION_NAME   IN  VARCHAR2 DEFAULT NULL,
   SPCPRESSURE      IN  BOOLEAN DEFAULT FALSE);

Parameters

Table 85-7 SEGMENT_DEALLOCATE_VERSIONS Procedure Parameters

Parameter	Description
`schema_name`	Name of the schema that owns the object.
`table_name`	Name of the table requiring repopulation.
`partition_name`	Name of the partition or subpartition. If null, then repopulate the entire table.
`spcpressure`	Whether to force deallocation of non-current IMCUs (`TRUE`), or wait for the database to deallocate them automatically. By default, the database deallocates non-current IMCUs every two minutes.

Usage Notes

During repopulation, the IM column store maintains both the current IMCU and non-current IMCU. This mechanism, which is called double buffering, ensures that queries do not decrease performance because an IMCU is unavailable during repopulation. After repopulation completes, the IM column store retains the non-current IMCU for a short time (2 minutes by default) to optimize queries with older SCNs. Typically, the default behavior is sufficient. However, you can force deallocation of non-current IMCUs by using the SEGMENT_DEALLOCATE_VERSIONS procedure.

Example

The following program forces deallocation of non-current IMCUs for the products table:

BEGIN
  DBMS_INMEMORY.SEGMENT_DEALLOCATE_VERSIONS( 
    schema_name => 'SH'
  , table_name  => 'PRODUCTS'
  , spcpressure => TRUE );
END;