Starting with Oracle version 12.2, the In-Memory column store can materialize expressions that resolve to a value. This includes deterministic PL/SQL functions. In-memory virtual expressions use the same underlying mechanism as in-memory virtual columns, adding some automation to the process. Virtual expressions are stored as a hidden virtual column. A notable addition is Expression Statistics Store, which tracks frequently evaluated expressions. It is exposed through {all,dba,user}_expression_statistics view.
Mind the data types
As of writing of this, not all the data types are supported. For example, NUMBERs are fine, but as we discovered the hard way, TIMESTAMPs are not. While virtual columns for expressions returning TIMESTAMPs are created, expressions are not loaded into the In-Memory Expression Units. On a more positive note, there are rumours about a backport to 19c being in the works, and there’s a documentation bug as well.
A little experiment
The rest of the blog post uses setup from the in-memory virtual columns blog. For in-memory virtual expressions to work, the parameter INMEMORY_EXPRESSIONS_USAGE must be set to ENABLE:
INMTEST@localhost/orclpdb1 > show parameter INMEMORY_EXPRESSIONS_USAGE
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_expressions_usage string ENABLE
Let’s generate some frequently used expressions. My expectation here is that slow_round(amount, 2) should end up in the Expression Statistics Store, and after repopulation, the anonymous PL/SQL block becomes much faster.
INMTEST@localhost/orclpdb1 28-04-2023 07:31:19> DECLARE
2 l_ts TIMESTAMP;
3 l_amount NUMBER;
4 l_day TIMESTAMP;
5 BEGIN
6 FOR i IN 0..200 LOOP
7 l_day := trunc(systimestamp - dbms_random.value(0, 366));
8 SELECT
9 trunc(created_ts),
10 SUM(slow_round(amount, 2))
11 INTO l_ts, l_amount
12 FROM
13 transactions
14 WHERE
15 trunc(created_ts) = l_day
16 GROUP BY
17 trunc(created_ts);
18
19 END LOOP;
20 END;
21 /
PL/SQL procedure successfully completed.
Elapsed: 00:42:30.03
This indeed generated some expressions:
INMTEST@localhost/orclpdb1 28-04-2023 08:13:49> SELECT
2 expression_text,
3 evaluation_count,
4 fixed_cost,
5 dynamic_cost,
6 created,
7 last_modified
8 FROM
9 dba_expression_statistics
10 WHERE
11 table_name = 'TRANSACTIONS';
EXPRESSION_TEXT EVALUATION_COUNT FIXED_COST DYNAMIC_COST CREATED LAST_MODIFIED
---------------------------------------- ---------------- ---------- ------------ ------------------- -------------------
TRUNC("CREATED_TS") 13726895 .000055108 0 28-04-2023 07:31:19 28-04-2023 08:13:49
"CREATED_TS" 13726895 5.5108E-08 0 28-04-2023 07:30:38 28-04-2023 08:13:49
"AMOUNT" 13726895 5.5108E-08 0 28-04-2023 07:30:38 28-04-2023 08:13:49
"INMTEST"."SLOW_ROUND"("AMOUNT",2) 13726895 .000165325 0 28-04-2023 07:31:19 28-04-2023 08:13:49
TRUNC("CREATED_TS") 13726895 .000041331 0 28-04-2023 07:31:19 28-04-2023 08:13:49
Apart from our "SLOW_ROUND"("AMOUNT",2), it has captured the relatively useless TIMESTAMP returning expressions as well. Now we can add the virtual columns with DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS. Regardless of what the procedure name suggests, it queries the Expression Statistics Store, adds the 20 hottest expressions as virtual columns and marks them as INMEMORY. Twenty expressions is a database-wide limit; there does not seem to be a way to embiggen it.
In-Memory expression units are populated as their parent IMCUs are populated. To force the population, I will later call DBMS_INMEMORY_ADMIN.POPULATE_WAIT.
SYS@ORCLCDB > exec DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS('CURRENT');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
INMTEST@localhost/orclpdb1 28-04-2023 08:13:49> declare
2 l_out NUMBER;
3 begin
4 l_out := DBMS_INMEMORY_ADMIN.POPULATE_WAIT('NONE', force => true);
5 dbms_output.put_line('populate_wait -> ' || l_out);
6 end;
7 /
PL/SQL procedure successfully completed.
Elapsed: 00:19:30.98
Check if virtual columns/expressions have been created:
INMTEST@localhost/orclpdb1 28-04-2023 08:33:20> SELECT
2 column_name,
3 sql_expression
4 FROM
5 dba_im_expressions
6 WHERE
7 table_name = 'TRANSACTIONS';
COLUMN_NAME SQL_EXPRESSION
------------------------------ ----------------------------------------
SYS_IME0001000002843DB0 TRUNC("CREATED_TS")
SYS_IME0001000002843DB1 "INMTEST"."SLOW_ROUND"("AMOUNT",2)
And now anonymous block is much faster:
INMTEST@localhost/orclpdb1 28-04-2023 08:33:20> DECLARE
2 l_ts TIMESTAMP;
3 l_amount NUMBER;
4 l_day TIMESTAMP;
5 BEGIN
6 FOR i IN 0..200 LOOP
7 l_day := trunc(systimestamp - dbms_random.value(0, 366));
8 SELECT
9 trunc(created_ts),
10 SUM(slow_round(amount, 2))
11 INTO l_ts, l_amount
12 FROM
13 transactions
14 WHERE
15 trunc(created_ts) = l_day
16 GROUP BY
17 trunc(created_ts);
18
19 END LOOP;
20 END;
21 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:14.20
Cleaning up the virtual expressions
When DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS discovers virtual expressions are no longer part of the “20 hottest expressions” list, and it marks those virtual columns as NO INMEMORY. So it is up to the DBA to clean up these virtual columns. There are three ways to do that:
- Call
DBMS_INMEMORY.IME_DROP_EXPRESSIONS. This procedure drops the specified virtual expression or all expressions on the table if the name of the virtual column is not specified. - Call
DBMS_INMEMORY_ADMIN.DROP_ALL_EXPRESSIONS. This removes all virtual expressions in the database. - Drop the virtual columns manually. This seems to do the same thing as
DBMS_INMEMORY.IME_DROP_EXPRESSIONS: removes IMEUs and drops the column.
Fear and Loathing 3.0 