In our previous two posts on In-Memory Expressions (IM expressions) we discussed the two types of IM expressions, user-defined In-Memory virtual columns and automatically detected In-Memory expressions. Now let’s visit some of the details of IM expressions.
Probably the first thing to note is that since IM expressions are stored in the IM column store, their use is going to require more memory to store the IM expressions. We can see this when we populate IM expressions. In Part 1 of our series, we populated a user-defined IM virtual column for an expression in the LINEORDER table. Let’s take a look at the space this required. Prior to creating the virtual column, the IM column store had these segments and their sizes populated in my test system based on V$IM_SEGMENTS:
Notice the INMEMORY_SIZE value for the LINEORDER table. Then we created a virtual column for the expression that we had identified and re-populated the table. The following were the new values for INMEMORY_SIZE in V$IM_SEGMENTS:
It seems fairly obvious that if we are going to materialize expressions and store them in the IM column store that they are going to take up additional space. How much will depend on the data created. What’s more interesting to note though, is that IM expressions are stored in a new structure called the In-Memory Expression Unit (IMEU). When an IM expression is created for a table, one IMEU is created for each In-Memory Compression Unit (IMCU). Recall that an IMCU is a read-only container that stores the columnar data for an object, and each object populated in the IM column store will be composed of one or more IMCUs. Just as you can see information about IMCUs in the views V$IM_HEADER and V$IM_COL_CU there are corresponding views with information for IMEUs called V$IMEU_HEADER and V$IM_IMECOL_CU.
Now that we’ve set up IM expressions, how do we tell which objects have them and whether they’re populated in the IM column store? Since IM expressions are based on virtual columns, all of the normal procedures to identify virtual columns applies to IM expressions. In other words, we can use the view ALL|DBA|USER_TAB_COLS to display any virtual columns and their attributes. Let’s take a look at what our LINEORDER table looked like from Part 1 of the series:
Note the column V1 and that it is a virtual column and the expression is visible as the default value for the column.
Now that a virtual column is defined, how do we know if it is enabled for inmemory? Note that this is where user-defined IM virtual columns and automatically detected IM expressions begin to differ in behavior. To determine if a user-defined virtual column is enabled for in-memory storage we need to query the view V$IM_COLUMN_LEVEL:
Note that we see the virtual column V1 in this view. Also note the value of UNSPECIFIED for the INMEMORY_COMPRESSION attribute. I will describe this in more detail.
To determine if an automatically detected IM expression is enabled for in-memory storage we need to query the view DBA|USER_IM_EXPRESSIONS. It shows us the automatically detected IM expressions that are enabled for in-memory storage. The following shows the automatically detected IM expression from Part 2 of our series:
Note that the name starts with SYS_IME and recall that since it is an automatically detected IM virtual column that it is implemented as a hidden virtual column.
Other behavioral changes to note involve the population of the two types of IM expressions. The initialization parameter INMEMORY_VIRTUAL_COLUMNS only controls the population behavior of user-defined virtual columns. For automatically detected IM expressions the initialization parameter INMEMORY_EXPRESSIONS_USAGE controls the population behavior. The other big difference is that if a table has user-defined IM virtual columns and it is marked as NO INEMMORY and then re-enabled with INMEMORY, the population is still controlled by the INMEMORY_VIRTUAL_COLUMNS parameter. However, if a table with automatically detected IM expressions is marked as NO INMEMORY and then re-enabled with INMEMORY, the currently defined SYS_IME virtual columns will not be re-enabled. They will have been marked internally as disabled by the NO INMEMORY command. This is done to avoid populating automatically detected IM expressions that might no longer be among the most frequently accessed expressions.
It is also possible to view the compression level for user-defined IM virtual columns in the view V$IM_COLUMN_LEVEL. As we noted above, there was a value of “UNSPECIFIED” in the INMEMORY_COMPRESSION field for the V1 virtual column. This means that the table’s default MEMCOMPRESS level will be used (i.e. the column compression value was unspecified). If we set the compression to a different level then that level will be used and will be reflected in the view:
Note that the setting of the initialization parameter INMEMORY_VIRTUAL_COLUMNS is set to ENABLE in this environment. If it was set to MANUAL then we would also have to explicitly enable the column for in-memory as well. The command would need to be the following:
ALTER TABLE lineorder INMEMORY INMEMORY MEMCOMPRESS FOR QUERY HIGH(v1);
We’ve shown how to determine if an IM expression is enabled for in-memory storage. But we asked, is there a way to tell if the IM expression has actually been populated? There is another view, V$IM_IMECOL_CU that shows the columns populated and number of IMEUs they occupy. In the case of the V1 IM virtual column from Part 1 we see:
Which shows us that the V1 IM virtual column occupies 133 IMEUs. Recall that there is at least one IMEU that corresponds to every IMCU.
To conclude, this series on In-Memory Expressions has described the two types of IM expressions, user-defined IM virtual columns and automatically detected IM expressions, it has shown the performance potential of one of the high-performance features of Database In-Memory, it has shown how the Optimizer and the ESS help automate the detection of frequently used expressions and their population into the IM column store, and then we’ve described some of the details of IM expressions so you can understand how they are implemented.
Original publish date: September 28, 2017