DBMS_INMEMORY

85 DBMS_INMEMORY_ADMIN

DBMS_INMEMORY_ADMIN provides interfaces for managing an In-Memory FastStart (IM FastStart) area and In-Memory Expressions (IM expressions).

This chapter contains the following topics:

85.1 DBMS_INMEMORY_ADMIN Overview

This package provides interfaces for managing In-Memory Expressions (IM expressions) and the In-Memory FastStart (IM FastStart) area.

IM Expressions

Analytic queries often contain complex expressions or calculations that can consume significant CPU and memory during execution. Use DBMS_INMEMORY_ADMIN procedure to identify these frequently used (“hot”) expressions and populate them in the IM column store. In this way, the database avoids repeated computations and improves performance.

The database represents IM expressions as system-generated virtual columns. The name of an IM virtual column begins with SYS_IME. You can also use DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS and DBMS_INMEMORY.IME_DROP_EXPRESSIONS to remove existing SYS_IME columns.

The DBA_IM_EXPRESSIONS view shows the SYS_IME columns that have the INMEMORY attribute. After using the IME_CAPTURE_EXPRESSIONS procedure, you can query this view to see the hot expressions added to different tables in the database.

85.2 DBMS_INMEMORY_ADMIN Security Model

This package requires administrator privileges. Package subprograms execute with invoker’s rights.

85.3 DBMS_INMEMORY_ADMIN Operational Notes

It is possible for a DBMS_INMEMORY_ADMIN FastStart operation to fail or be interrupted.

In a failure or interruption scenario, the following rules determine which subprograms you can use:

If FASTSTART_ENABLE does not succeed, then the only permitted operation is re-executing FASTSTART_ENABLE.
If FASTSTART_MIGRATE_STORAGE does not succeed, then the only permitted operation is re-executing FASTSTART_MIGRATE_STORAGE.
If FASTSTART_DISABLE does not succeed, then all DBMS_INMEMORY_ADMIN operations are permitted.

85.4 Summary of DBMS_INMEMORY_ADMIN Subprograms

This table lists the DBMS_INMEMORY_ADMIN subprograms and briefly describes them.

Table 85-1 DBMS_INMEMORY_ADMIN Package Subprograms

Subprogram	Description
FASTSTART_DISABLE Procedure	This procedure disables the In-Memory FastStart (IM FastStart) feature.
FASTSTART_ENABLE Procedure	This procedure enables IM FastStart and assigns a tablespace.
FASTSTART_MIGRATE_STORAGE Procedure	This procedure moves all IM FastStart data and metadata from the existing tablespace to the specified new tablespace.
GET_FASTSTART_TABLESPACE Function	This function returns the name of the tablespace that is currently designated for IM FastStart.
IME_CAPTURE_EXPRESSIONS Procedure	This procedure captures and populates the 20 most frequently accessed (“hottest”) expressions in the database in the expression capture window.
IME_DROP_ALL_EXPRESSIONS Procedure	This procedure drops all `SYS_IME` expression virtual columns in the database.
IME_POPULATE_EXPRESSIONS Procedure	This procedure forces the population of expressions captured in the latest invocation of `DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS`.

85.4.1 FASTSTART_DISABLE Procedure

This procedure disables the In-Memory FastStart (IM FastStart) feature.

Syntax

DBMS_INMEMORY_ADMIN.FASTSTART_DISABLE();

Security Model

Administrator privileges are required to execute this procedure.

Usage Notes

When you execute the procedure, the database executes the following actions:

Waits until all IM FastStart operations complete
Disables the IM FastStart feature, and performs the following operations:
- Cleans the IM FastStart area
- Deletes IM FastStart metadata stored in the SYSAUX tablespace
- Releases the IM FastStart tablespace (but does not delete it)

This procedure does not interrupt or affect any concurrent IM column store operations.

Examples

The following PL/SQL program disables the IM FastStart feature:

EXEC DBMS_INMEMORY_ADMIN.FASTSTART_DISABLE;

The following query shows that the LOB for the IM FastStart tablespace has been deleted (sample output included):

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a20
SELECT   l.OWNER, l.SEGMENT_NAME, SUM(s.BYTES)/1024/1024 MB
FROM     DBA_LOBS l, DBA_SEGMENTS s
WHERE    l.SEGMENT_NAME = s.SEGMENT_NAME
AND      l.TABLESPACE_NAME = 'FS_TBS'
GROUP BY l.OWNER, l.SEGMENT_NAME;

no rows selected

85.4.2 FASTSTART_ENABLE Procedure

This procedure enables In-Memory FastStart (IM FastStart), and designates a tablespace for the IM FastStart (FastStart) area.

Syntax

DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE( 
   tbs_name    IN    VARCHAR2,
   nologging   IN    BOOLEAN DEFAULT TRUE);

Parameters

Table 85-2 FASTSTART_ENABLE Procedure Parameters

Parameter Description

Parameter	Description
`tbs_name`	The name of the ASSM tablespace for the FastStart area.
`nologging`	The logging mode of the LOB created for the FastStart area. If the `nologging` parameter is set to `FALSE`, then the database creates the FastStart LOB with the `LOGGING` option. If set to `TRUE` (default), then the database creates the LOB with the `NOLOGGING` option.

tbs_name

The name of the ASSM tablespace for the FastStart area.

nologging

The logging mode of the LOB created for the FastStart area.

If the nologging parameter is set to FALSE, then the database creates the FastStart LOB with the LOGGING option. If set to TRUE (default), then the database creates the LOB with the NOLOGGING option.

Security Model

Administrator privileges are required to execute this procedure.

Usage Notes

To enable IM FastStart, the ASSM tablespace specified in FASTSTART_ENABLE must exist, and the SYSAUX tablespace must be online. Only one FastStart tablespace can exist for every PDB or non-CDB. The specified tablespace must have enough space to store data for the IM column store, and it must not contain any other data before it is designated for the FastStart area. Oracle recommends sizing the tablespace at least twice of the size of the INMEMORY_SIZE initialization parameter.

The database does not create the FastStart area on disk until the IM column store is populated. After population, the data periodically saves the columnar data (but not metadata such as the transaction journal) to the FastStart area, which is represented on disk as the SYSDBIMFS_LOBSEG$ segment. The database stores the FastStart metadata in the SYSAUX tablespace. In an Oracle Real Application Clusters (Oracle RAC) environment, IM FastStart data is shared across all nodes.

Note:

IM FastStart is not supported in a standby database instance.

Whereas the initial loading of IMCUs into memory is expensive and CPU-bound, an IM FastStart tablespace requires intermittent I/O. The database periodically writes columnar data to the IM FastStart area. If a database instance must restart, then Oracle Database reads the columnar data directly from the IM FastStart area rather than reconstructing the IMCUs from scratch. No compression or formatting of the columnar data is required.

Examples

This example creates fs_tbs as an ASSM tablespace, and then uses FASTSTART_ENABLE to specify this tablespace as the IM FastStart area:

CREATE TABLESPACE fs_tbs 
  DATAFILE 'fs_tbs.dbf' SIZE 500M 
  EXTENT MANAGEMENT LOCAL 
  SEGMENT SPACE MANAGEMENT AUTO;

EXEC DBMS_INMEMORY_ADMIN.FASTSTART_ENABLE('fs_tbs');

The following query shows that the IM FastStart LOB was created (sample output included):

COL OWNER FORMAT a5
COL SEGMENT_NAME FORMAT a20
SELECT   l.OWNER, l.SEGMENT_NAME, SUM(s.BYTES)/1024/1024 MB
FROM     DBA_LOBS l, DBA_SEGMENTS s
WHERE    l.SEGMENT_NAME = s.SEGMENT_NAME
AND      l.TABLESPACE_NAME = 'FS_TBS'
GROUP BY l.OWNER, l.SEGMENT_NAME;

OWNER SEGMENT_NAME         MB
----- -------------------- ----------
SYS   SYSDBIMFS_LOBSEG$    .125

85.4.3 FASTSTART_MIGRATE_STORAGE Procedure

This procedure moves the In-Memory FastStart (IM FastStart) data and catalogs from the current tablespace to a new tablespace.

Syntax

DBMS_INMEMORY_ADMIN.FASTSTART_MIGRATE_STORAGE( 
   tbs_name    IN    VARCHAR2 );

Parameters

Table 85-3 FASTSTART_MIGRATE_STORAGE Procedure Parameters

Parameter	Description
`tbs_name`	The name of the new ASSM tablespace for the IM FastStart area.

Security Model

DBA privileges are required to execute this procedure.

Usage Notes

When you execute the procedure, the database executes the following actions:

Waits until all IM FastStart operations complete
Disables the IM FastStart feature
Copies IM FastStart data and metadata to the new tablespace, leaving the old tablespace intact
Re-enables IM FastStart the feature

Examples

The following program obtains the name of the IM FastStart tablespace, if one exists, and prints the result (sample output included):

VARIABLE b_fstbs VARCHAR2(20)
BEGIN
   :b_fstbs := DBMS_INMEMORY_ADMIN.GET_FASTSTART_TABLESPACE;
END;
/
PRINT b_fstbs

B_FSTBS
-----------------------------
FS_TBS

The following statements create a new tablespace named fs_tbs2, and then migrate the IM FastStart area to this tablespace:

CREATE TABLESPACE fs_tbs2 
  DATAFILE 'fs_tbs2.dbf' SIZE 500M
  EXTENT MANAGEMENT LOCAL 
  SEGMENT SPACE MANAGEMENT AUTO;

EXEC DBMS_INMEMORY_ADMIN.FASTSTART_MIGRATE_STORAGE('fs_tbs2');

The following program prints the name of the current IM FastStart tablespace (sample output included):

BEGIN
   :b_fstbs := DBMS_INMEMORY_ADMIN.GET_FASTSTART_TABLESPACE;
END;
/
PRINT b_fstbs

B_FSTBS
-----------------------------
FS_TBS2

85.4.4 GET_FASTSTART_TABLESPACE Function

This function returns the tablespace assigned to In-Memory FastStart (IM FastStart). If the feature is disabled, then the function returns NOT ENABLED.

Syntax

DBMS_INMEMORY_ADMIN.GET_FASTSTART_TABLESPACE();

Security Model

DBA privileges are required to execute this function.

Examples

This program obtains the name of the IM FastStart tablespace, if one exists, and prints the result:

VARIABLE b_fstbs VARCHAR2(20)
BEGIN
   :b_fstbs := DBMS_INMEMORY_ADMIN.GET_FASTSTART_TABLESPACE;
END;
/
PRINT b_fstbs

B_FSTBS
-----------------------------
NOT ENABLED

85.4.5 IME_CAPTURE_EXPRESSIONS Procedure

This procedure captures and populates the 20 most frequently accessed (“hottest”) expressions in the database in the specified time range.

Syntax

DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS(
   snapshot    IN    VARCHAR2);

Parameters

Table 85-4 IME_CAPTURE_EXPRESSIONS Procedure Parameters

Parameter Description

Parameter	Description
`snapshot`	Specifies a snapshot that defines the period of time from which expression statistics are considered. You can specify either of the following values: `CUMULATIVE` The database considers all expression statistics since the creation of the database. `CURRENT` The database considers only expressions statistics from the past 24 hours.

snapshot

Specifies a snapshot that defines the period of time from which expression statistics are considered. You can specify either of the following values:

CUMULATIVE

The database considers all expression statistics since the creation of the database.
CURRENT

The database considers only expressions statistics from the past 24 hours.

Usage Notes

When you invoke this procedure, the database queries the Expression Statistics Store (ESS), and considers only expressions on tables that are at least partially populated in the IM column store. The database adds the 20 hottest expressions to their respective tables as hidden virtual columns, prefixed with the string SYS_IME, and applies the default INMEMORY column compression clause. If any SYS_IME columns added during a previous invocation are no longer in the latest top 20 list, then the database marks them as NO INMEMORY.

Note:

You cannot execute IME_CAPTURE_EXPRESSIONS on a standby database.

The maximum number of SYS_IME columns for a table, regardless of whether the attribute is INMEMORY or NO INMEMORY, is 50. After the 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 IME_DROP_EXPRESSIONS or IME_DROP_ALL_EXPRESSIONS procedures.

The 50-expression limit for each table, which includes both INMEMORY and NO INMEMORY expressions, is different from the 20-expression limit for the database, which includes only INMEMORY expressions. For example, if 20 tables are populated in the IM column store, then each table might each have 1 SYS_IME column with the INMEMORY attribute, and 49 SYS_IME columns with the NO INMEMORY attribute.

IM expressions and virtual columns are stored in In-Memory structured called In-Memory Expression Units (IMEUs). Every IMEU has a parent In-Memory Compression Unit (IMCU) from which it is derived. An IMEU inherits the compression characteristics of its IMCU.

Note:

IM expressions do not support NLS-dependent data types.

Example 85-1 Example: Compression Inheritance

The following statement creates a NO INMEMORY table named test_tbl:

CREATE TABLE test_tbl (a NUMBER, b NUMBER, vc1 AS (2*a), vc2 AS (3*a));

The following DDL statement marks this table as INMEMORY:

ALTER TABLE test_tbl 
  INMEMORY
  INMEMORY (vc1)
  INMEMORY MEMCOMPRESS FOR DML (vc2);

The preceding statement applies compression as follows:

INMEMORY applies default compression to the entire table. The default compression is FOR QUERY LOW.
INMEMORY (vc1) applies the compression level of the table to column vc1. Because the compression for the table is FOR QUERY LOW, column vc1 inherits the compression FOR QUERY LOW.
INMEMORY MEMCOMPRESS FOR DML (vc2) applies DML compression to column vc2.

85.4.6 IME_DROP_ALL_EXPRESSIONS Procedure

This procedure drops all SYS_IME expression virtual columns in the database.

Syntax

DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS();

Usage Notes

The IME_DROP_ALL_EXPRESSIONS procedure drops all SYS_IME columns from all tables, regardless of whether they have the INMEMORY attribute. In effect, the procedure acts as a database-wide reset button.

Using IME_DROP_ALL_EXPRESSIONS triggers a drop of all IMEUs and IMCUs for segments that have SYS_IME columns. For example, if 50 populated tables have one SYS_IME column each, then IME_DROP_ALL_EXPRESSIONS removes all 50 tables from the IM column store. To populate these segments again, you must use the DBMS_INMEMORY.POPULATE procedure or perform a full table scan.

85.4.7 IME_POPULATE_EXPRESSIONS Procedure

This procedure forces the population of expressions captured in the latest invocation of DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS.

Syntax

DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

Usage Notes

If you do not invoke this procedure, then the database gradually repopulates SYS_IME columns when their parent IMCUs are repopulated. If a table is not repopulated, then any new SYS_IME columns captured by the IME_CAPTURE_EXPRESSIONS procedure are not populated. IME_POPULATE_EXPRESSIONS solves this problem by forcing population.

Internally, the procedure invokes DBMS_INMEMORY.REPOPULATE for all tables that have SYS_IME columns with the INMEMORY attribute. To populate SYS_IME columns in a specified subset of tables, use DBMS_INMEMORY.REPOPULATE instead of IME_POPULATE_EXPRESSIONS.