In our previous post on In-Memory Expressions (IM expressions) we described what an IM expression is and showed the potential for additional high-performance acceleration using the feature. We said that there are two types of IM expressions, user-defined IM virtual columns and automatically detected IM expressions which are stored as hidden virtual columns. This post will focus on automatically detected IM expressions and how they work.
In Oracle Database 12c Release 2 (12.2) a new feature called expression tracking was added. You can read more about this feature in an Optimizer blog post available here. The bottom line is that the Optimizer tracks expressions and stores them in a repository called the Expression Statistics Store (ESS). Along with the expression and other information, the number of times the expression was evaluated and the cost of evaluating the expression are tracked. This is exposed in the ALL|DBA|USER_EXPRESSION_STATISTICS view and Database In-Memory uses this information to determine the 20 most frequently accessed expressions in either the past 24 hours (i.e. LATEST snapshot) or since database creation (i.e. CUMULATIVE snapshot). The following is an example for the LINEORDER table in my test system for the LATEST snapshot:
Notice that I’ve highlighted the expression from Part 1 of this series, as it shows up as one of the tracked expressions.
In order to populate automatically detected IM expressions the INMEMORY_EXPRESSIONS_USAGE parameter must be set to either ENABLE (the default) or DYNAMIC_ONLY. In my test system it is set to the default value:
To capture and populate expressions tracked by the ESS for objects populated in the IM column store we need to run two procedures. The first will “capture” the expressions by identifying the top 20 expressions for objects populated in the IM column store and will create hidden virtual columns for the expression(s) in the appropriate tables. Note that because automatically detected IM expressions are implemented as hidden virtual columns, they must also meet the restrictions for virtual columns. The virtual columns for automatically detected IM expressions will begin with the string “SYS_IME”. The following will show the capture for the LATEST, or CURRENT snapshot (note that the ALL|DBA|USER_EXPRESSION_STATISTICS view uses LATEST and the DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS procedure uses CURRENT):
Note that I first executed the DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO procedure to flush the expression tracking data to the ESS. Normally this is done automatically every 15 minutes, but I wanted to make sure that I didn’t miss any data. Once this has been run we will see that the top 20 in-memory expressions will have been stored in their respective tables as hidden virtual columns. In my test system, the expression for the LINEORDER table that we saw above was identified as one of the top 20 and was added to the LINEORDER table. We can see this in the view USER_TAB_COLS:
Notice that a hidden, virtual column named SYS_IME000100000025B970 has been created and the DATA_DEFAULT attribute shows the expression. How do we know that the column has been enabled for inmemory? We can query the view DBA|USER_IM_EXPRESSIONS views. In this case I queried the user_im_expressions view:
Now we have to re-populate the LINEORDER table to take advantage of the newly added IM expression. This will be done as IMCUs are re-populated based on DML changes, or the DBMS_INMEMORY_ADMIN.IME_POPULATE_EXPRESSIONS procedure can be used to force the population of all expressions captured in the latest invocation of the DBMS_INMEMORY_ADMIN.IME_CAPTURE_EXPRESSIONS procedure. The following forces the population of any expressions that were added:
Since IM expressions are stored in the IM column store they do consume memory. How much will depend on the data. You can see this in the V$INMEMORY_AREA and V$IM_SEGMENTS views. We will talk about this plus other internal details of IM expressions in the final part of this series.
Original publish date: August 29, 2017