Optimizing Queries with In-Memory Expressions

• About IM Expressions

IM expression是查询中使用到的计算表达式，它类似于表的虚拟列，Oracle通过ESS自动选出hot expression, 通过dbms_inmemory_admin来管理IM expression。DBA_IM_EXPRESSIONS的COLUMN_NAME列中值前缀为SYS_IME均为IM expression

In the context of the IM column store, an expression is a combination of one or more values, operators, and SQL or PL/SQL functions (DETERMINISTIC only) that resolve to a value.

An IM expression is materialized as a hidden virtual column, but is accessed in the same way as a non-virtual column. To store the materialized expressions, the IM column store uses special compression formats such as fixed-width vectors and dictionary encoding with fixed-width codes.

The Expression Statistics Store (ESS) automatically tracks the results of frequently evaluated (“hot”) expressions. You can use the DBMS_INMEMORY_ADMIN package to capture hot expressions and populate them as hidden virtual columns, or drop some or all of them.

Oracle Database automatically identifies the expressions that are candidates for population in the IM column store. In DBA_IM_EXPRESSIONS.COLUMN_NAME, IM expression columns have the prefix SYS_IME. You cannot create SYS_IME columns directly.

The primary advantages of IM expressions are as follows:

1. A query does not need to recalculate the expressions every time. If the IM column store does not populate the expression results, then the database must compute them for every row, which can be resource intensive. The database incurs the CPU overhead during the population.
2. The materialization of IM expressions enables the database to take advantage of performance-enhancing features such as SIMD vector processing and IMCU pruning.
3. The database, rather than the user, tracks which expressions are most active.

IM expressions and materialized views address the same problem: how to avoid repeatedly evaluating expressions. However, IM expressions have advantages over materialized views:

1. IM expressions can capture data that is not persistently stored.
2. To be used effectively, a materialized view must have all columns listed in the query, or the query must join the view and the base tables. In contrast, any query containing an IM expression can benefit.
3. The database identifies and creates IM expressions automatically, unlike materialized views, which are user-created objects.

For example, consider the following query, which specifies two expressions, aliased weekly_sal and ann_comp:

SELECT employee_id, last_name, salary, commission_pct,

       ROUND(salary*12/52,2) as "weekly_sal",

       12*(salary*NVL(commission_pct,0)+salary) as "ann_comp"

FROM   employees

ORDER BY ann_comp;

The arithmetical expressions ROUND(salary*12/52,2) and 12*(salary*NVL(commission_pct,0)+salary) are computationally intensive and frequently accessed, which makes them candidates for hidden IM expression columns.

• How IM Expressions Work

1. IM Expressions Infrastructure

IM expressions infrastructure用于计算生成IM expressions的结果并将它生成为虚拟列

The IM expressions infrastructure is responsible for computing and populating the results of IM expressions, IM virtual columns, and any other useful internal computations in the IM column store. These optimizations primarily benefit analytic queries.

Besides populating an IM expression, the IM column store can populate an In-Memory virtual column. The underlying mechanism is the same: an IM expression is a virtual column. However, IM virtual columns are user-created and exposed, whereas IM expressions are database-created and hidden.

IM expression分为动态expression(IM expressions and virtual columns)以及静态expression

The IM expressions infrastructure supports both dynamic expressions (IM expressions and virtual columns) and static expressions.

Starting in Oracle Database 12c Release 2 (12.2), the IM column store supports OSON, which is a binary JSON format that performs better than row-oriented JSON text storage. Queries access the actual JSON data, but use optimized virtual columns to speed access.

The database uses the IM expression infrastructure to load an efficient binary representation of JSON text columns as virtual columns. If the MAX_STRING_SIZE initialization parameter is set to EXTENDED for the VARCHAR2 data type, then the IM column store can store OSON virtual columns up to 32 KB.

Oracle Database supports multiple JSON functions: JSON_TABLE, JSON_VALUE, and JSON_EXISTS. The INMEMORY_EXPRESSIONS_USAGE initialization parameter controls the behavior of both dynamic expressions and static expressions.

相关参数INMEMORY_EXPRESSIONS_USAGE = { STATIC_ONLY | DYNAMIC_ONLY | ENABLE（default） | DISABLE }

1. STATIC_ONLY:

Tables enabled for in-memory and containing certain data types such as Oracle numbers or JSON will have these columns populated in the IM column store using a more efficient representation. Note that this setting will increase the in-memory footprint for some tables. A static configuration enables the IM column store to cache OSON (binary JSON) columns, which are marked with an IS_JSON check constraint. Internally, an OSON column is a hidden virtual column named SYS_IME_OSON. In contrast, a dynamic configuration automatically creates and populates frequently used expressions.

1. DYNAMIC_ONLY: IM expressions will be automatically created and populated into the IM column store, if used in conjunction with PL/SQL procedure DBMS_INMEMORY.IME_CAPTURE_EXPRESSIONS. Note that setting this value will increase the in-memory footprint for some tables. This value should not be used in an Oracle Cloud environment.
2. ENABLE: Both static and dynamic IM expressions will be populated into the IM column store and available to be used by queries. Note that setting this value will increase the in-memory footprint for some tables. This is the default value. This value should not be used in an Oracle Cloud environment.
3. DISABLE: No IM expressions of any kind will be populated into the IM column store.

NOTE: Changing the mode of a parameter does not have an immediate effect on existing in-memory data.

For example, if the inmemory_expressions_usage clause is changed from DYNAMIC_ONLY to DISABLE, the stored IM expressions are not immediately removed.

The next repopulation will not bring back the expressions in memory, thereby effectively removing the expressions.

1. How the Database Populates IM Expressions

ORACLE通过ESS来得出指定时间内(24小时内或数据库创建以来)20个最常访问的hot expression，生成对应的SYS_IME虚拟列，如果expression不是最新的则对应虚拟列会被设置为NO INMEMORY，但并不会删除此虚拟列。

Whenever you invoke the IME_CAPTURE_EXPRESSIONS procedure, the database queries the ESS, and identifies the 20 most frequently accessed (“hottest”) expressions in the specified time range. The time range is either the past 24 hours, or since database creation. The database only considers 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 SYS_IME virtual columns and applies the default INMEMORY column compression clause. If any SYS_IME columns that were added during a previous invocation are no longer in the latest expression list, then the database changes their attribute to NO INMEMORY.

Figure 5-1 Capturing SYS_IME Columns

一个表最多有50个SYS_IME的虚拟列，如果已达到此限制将不会再增加新的IM Expression, 此时可以使用DBMS_INMEMORY.IME_DROP_EXPRESSIONS或DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS来drop SYS_IME虚拟列。另外一个表上SYS_IME与用户自定义虚拟列和最多为1000列

The maximum number of SYS_IME columns for a table is 50, regardless of whether the attribute is INMEMORY. After a table reaches the 50-expression limit, the database does not add new SYS_IME columns. To permit new expressions, you must drop SYS_IME columns with the DBMS_INMEMORY.IME_DROP_EXPRESSIONS or DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS procedures.

Both SYS_IME virtual columns and user-defined virtual columns count toward the 1000-column limit for a table. For example, if a table contains 980 non-virtual (on-disk) columns, then you can add only 20 virtual columns.

IM Expression同虚拟列一样会继承表的INMEMORY属性

For any row, the physical columns reside in the IMCU, and the virtual columns reside in an associated IMEU. The IMEU is read-only and columnar, just like the IMCU.

Because IMEUs are logical extensions of IMCUs created for a particular INMEMORY segment, by default they inherit the INMEMORY clause, and Oracle Real Applications Cluster (Oracle RAC) properties such as DISTRIBUTE and DUPLICATE. An IMEU is associated with one and only one IMCU. The database manages IMEUs as separate structures, making them easier to add and drop.

IMEU是随IMCU repopulate同时更新的，没有先后顺序

If the source data changes, then the database changes the derived data in the IM expression during repopulation. For example, if a transaction updates 100 salary values in a table, then the Space Management Worker Processes (Wnnn) automatically update all IM expression values that are derived from these 100 changed values. The database repopulates an IMCU and its associated IMEUs together rather than first repopulating all IMCUs and then repopulating all IMEUs. IMEUs remain available for queries during IMCU repopulation.

Note: As the number of expressions to store in IMEUs goes up, the worker processes may consume slightly more CPU to compute the expression values. This overhead may increase population time.

• Capturing/Populating/Dropping IM Expressions

相关包：DBMS_INMEMORY与DBMS_INMEMORY_ADMIN

1. 载入某表到IM column store:

DBMS_INMEMORY.POPULATE(

schema_name=>'SCOTT',  

table_name=>'T1',  

subobject_name=>'T1_P1');  

1. 重载某表:

如果force为true则重载表所有IMCU,就象populate一样；反之只重载包含变动行的IMCU

DBMS_INMEMORY.REPOPULATE(

schema_name=>'SCOTT',  

table_name=>'T1',  

subobject_name=>'T1_P1',

force=>'TRUE');   

1. 生成自数据库创建以来20个hottest expressions对应的SYS_IME虚拟列:

DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS( snapshot=>'CUMULATIVE');   

1. 生成24小时内的20个hottest expressions对应的SYS_IME虚拟列:

DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS( snapshot=>'CURRENT');        

1. 清空所有表的SYS_IME列，并将这些表逐出IM Area:

如果表IM PRIORITY为NONE则要在全表扫描后重新载入。在SYS_IME列达到50个时系统不在生成新的SYS_IME列，使用此过程清空

DBMS_INMEMORY_ADMIN.IME_DROP_ALL_EXPRESSIONS();    

1. 只清除某个表的某个SYS_IME列，不指定column_name则为表所有的SYS_IME列，同时也会将此表逐出IM Area:

DBMS_INMEMORY.IME_DROP_EXPRESSIONS(

schema_name=>'SCOTT',  

table_name=>'T1',

column_name=>'SYS_IMEXXX');     

1. 默认在使用IME_CAPTURE_EXPRESSIONS后并不直接产生SYS_IME列，而是IMEU对应的IMCU repopulation后生成，此过程即马上生成SYS_IME：

DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();  

Example 5-2 Capturing the Top 20 IM Expressions in the Past 24 Hours

This example captures IM expressions using only the statistics gathered during the last day, and then forces immediate population:

EXEC DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS('CURRENT');

EXEC DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS();

The following query of DBA_IM_EXPRESSIONS shows that two IM expressions are currently populated (sample output provided):

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;

OWNER  TABLE_NAM COLUMN_NAME               SQL_EXPRESSION

------ --------- ------------------------- ---------------------------------------------

HR     EMPLOYEES SYS_IME00010000001746FD   12*("SALARY"*NVL("COMMISSION_PCT",0)+"SALARY")

HR     EMPLOYEES SYS_IME00010000001746FE   ROUND("SALARY"*12/52,2)

Example 5-3 Dropping All IM Expressions in a Table

This example drops all IM expressions in the hr.employees table:

EXEC DBMS_INMEMORY.IME_DROP_EXPRESSIONS('hr', 'employees');