Prerequisites

To enable the database to use IM expressions, the following conditions must be true:

• The INMEMORY_SIZE initialization parameter is set to a non-zero value.

• The value for the initialization parameter COMPATIBLE is set to 12.2.0 or higher.

To configure IM expression usage:

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

2. To configure IM expression usage, use an ALTER SYSTEM statement to set INMEMORY_EXPRESSIONS_USAGE to any of the following values:

   • ENABLE (default) — Enable dynamic and static IM expressions

   • STATIC_ONLY — Enable only static IM expressions

   • DYNAMIC_ONLY — Enable only dynamic IM expressions

   • DISABLE — Disable all IM expressions

Whenever you invoke the IME_CAPTURE_EXPRESSIONS procedure, the database queries the Expression Statistics Store (ESS), and considers only expressions on heap-organized 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 SYS_IME columns added during a previous invocation are no longer in the latest top 20 list, then their attribute changes to NO INMEMORY.

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

Internally, the IME_POPULATE_EXPRESSIONS 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 DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS.

Prerequisites

To enable the database to capture IM expressions, the following conditions must be true:

• The INMEMORY_EXPRESSIONS_USAGE initialization parameter must be set to a value other than DISABLE.

• The INMEMORY_SIZE initialization parameter is set to a non-zero value.

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

• To specify an expression capture window of arbitrary length (rather than the predetermined time span of 24 hours or forever), you must open the window with DBMS_INMEMORY_ADMIN.IME_OPEN_CAPTURE_WINDOW and close it with IME_CLOSE_CAPTURE_WINDOW. The window is global across all instances in an Oracle RAC database.

To capture and populate IM expressions:

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

2. If you want to specify the length of the window (rather than a predetermined time span), then execute DBMS_INMEMORY_ADMIN.IME_OPEN_CAPTURE_WINDOW as follows:

   EXEC DBMS_INMEMORY_ADMIN.IME_OPEN_CAPTURE_WINDOW();

   Note:

   To check whether any expression capture windows are currently open, execute DBMS_INMEMORY_ADMIN.IME_GET_CAPTURE_STATE.

3. If you opened a window with IME_OPEN_CAPTURE_WINDOW in the preceding step, then close it with IME_CLOSE_CAPTURE_WINDOW as follows:

   EXEC DBMS_INMEMORY_ADMIN.IME_CLOSE_CAPTURE_WINDOW();

4. Execute DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS, setting the interval parameter to any of the following values:

   • CUMULATIVE

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

   • CURRENT

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

   • WINDOW

     The database adds hidden virtual columns for expressions tracked in the most recent user-specified expression capture window. This window opens with the manual invocation of the IME_OPEN_CAPTURE_WINDOW procedure, and then closes with the manual invocation of the IME_CLOSE_CAPTURE_WINDOW procedure.

     If the capture window is currently open, then the database considers all expressions tracked in the current window up until this point, and then materializes the hottest expressions. To list the expressions that have been tracked in the current window, query DBA_EXPRESSION_STATISTICS with SNAPSHOT = 'WINDOW'.

5. Optionally, to force immediate population of all captured IM expressions, execute DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS as follows:

   EXEC DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

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.

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.

Prerequisites

To drop IM expressions, the following conditions must be true:

• The INMEMORY_EXPRESSIONS_USAGE initialization parameter is set to a value other than DISABLE.

• The INMEMORY_SIZE initialization parameter is set to a nonzero value.

• The COMPATIBLE initialization parameter is set to 12.2.0 or higher.

To drop IM expressions:

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

2. Execute either DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS or DBMS_INMEMORY.IME_DROP_EXPRESSIONS.

   If you execute IME_DROP_EXPRESSIONS, then specify the following parameters:

   • schema_name — The name of the schema that contains the In-Memory table

   • table_name — The name of the In-Memory table

   • column_name — The name of the SYS_IME column. By default, this value is null, which specifies all SYS_IME columns in this table.