Skip to content

Database In-Memory (IM) Column Store Expressions

APPLIES TO:

Oracle Database – Enterprise Edition – Version 12.2.0.1 and later
Information in this document applies to any platform.

GOAL

Frequently evaluated query expressions can be populated in the IM Column Store for subsequent reuse.

SOLUTION

Prerequisites

  • The INMEMORY_SIZE initialization parameter is set to a non-zero value >= 100M.
  • The value for the initialization parameter COMPATIBLE must be set to 12.2.0 or higher.
  • In an Oracle Real Applications Cluster (RAC) environment, INMEMORY_EXPRESSIONS_USAGE must be set to the same value on all instances.
  • The INMEMORY_EXPRESSIONS_USAGE initialization parameter must be set to a value other than DISABLE.
Note: Usage of this feature takes more space in the IM Column Store.

Documentation

Database In-Memory Guide: Optimizing Queries with In-Memory Expressions

Example

1. Run an expression that would be picked up by the optimizer.

select avg(object_id + data_object_id), sum( object_id + data_object_id)/100 from imtest;

2. Show what’s in the Expression Statistics Store (ESS).

SQL> select * from user_expression_statistics;
TABLE_NAM EXPRESSION_ID SNAPSHOT EVALUATION_COUNT FIXED_COST DYNAMIC_COST
——— ————- ———- —————- ———- ————
EXPRESSION_TEXT
——————————————————————————————————————————————————
CREATED LAST_MODI
——— ———
IMTEST 3.7221E+18 LATEST 937354 .000041667 0
“OBJECT_ID”+”DATA_OBJECT_ID”
07-SEP-16 07-SEP-16

IMTEST 3.9517E+18 LATEST 937354 8.3333E-07 0
“OBJECT_ID”
07-SEP-16 07-SEP-16

3. Manually flush the expression statistics to disk (default is every 15 min).

exec dbms_stats.flush_database_monitoring_info;

4. Now capture the expressions from the ESS.

SQL> EXEC DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS(‘CURRENT’); — expressions from the ESS in the last 24 hours.
EXEC DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

5. Show the in-memory expressions (up to 20 can be used).

COL OWNER FORMAT a6
COL TABLE_NAME FORMAT a9
COL COLUMN_NAME FORMAT a25
SET LONG 50
SET LINESIZE 150

SELECT OWNER, TABLE_NAME, COLUMN_NAME, SQL_EXPRESSION
FROM DBA_IM_EXPRESSIONS;

6. Drop the expression from inmemory (note this does not drop what’s in the ESS; that will eventually age out).

EXEC DBMS_INMEMORY.IME_DROP_EXPRESSIONS(‘SGSTEST’, ‘IMTEST’);