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.
Documentation
Database In-Memory Guide: Optimizing Queries with In-Memory Expressions
Example
1. Run an expression that would be picked up by the optimizer.
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).
4. Now capture the expressions from the ESS.
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).