A new feature call expression tracking was added in Oracle Database 12c Release 2. It allows the Oracle Optimizer keep track of what expressions are used in SQL statements and how often they are used. Why does the optimizer do that? I’ll cover that at the end of the post, but before I do, let’s take a look a quick look under the hood.

Consider the following query:

select value_of_sale - cost_of_sale
from   sales
where  transaction_id = 10;

The query evaluates an expression: “value_of_sale - cost_of_sale” and it accesses a number of columns. We can take a look at what’s tracked using a data dictionary view:

exec dbms_stats.flush_database_monitoring_info  -- do this or 
                                                -- wait 15 minutes!

select expression_text,
       fixed_cost, 
       evaluation_count, 
       snapshot 
from   user_expression_statistics 
where  table_name = 'SALES' 
order by snapshot

EXPRESSION_TEXT                          FIXED_COST EVALUATION_COUNT SNAPSHOT
---------------------------------------- ---------- ---------------- ---------- "VALUE_OF_SALE"                          1.3170E-08                1 LATEST 
"COST_OF_SALE"                           1.3170E-08                1 LATEST
"TRANSACTION_ID"                         1.3170E-08                1 LATEST
"VALUE_OF_SALE"-"COST_OF_SALE"           6.5849E-07                1 LATEST

This expression data is persisted to the data dictionary every 15 minutes, so it will be available post shutdown/startup.  This is done without any need to configure or gather anything, but you can pre-empt persistence by executing flush_database_monitoring_info (if you’re running a test and impatient like me).

Notice that there are evaluation_count and fixed_cost columns. The fixed cost is an estimate of the computational cost per evaluation. The evaluation count is self explanitory but it's also an estimate.

The snapshot column tells you if the value you’re seeing is from the last 24 hours (LATEST), or if it's a long-term cumulative figure (CUMULATIVE).

A few more things to bear in mind:

• If you take at the dictionary view definition, you will see that there is also a column called dynamic_cost. This is currently not implemented so expect to see NULL or zero in this column.
• The optimizer tracks expressions that include columns from a single table only. For example, “t1.col1 + t1.col2”, but not “t1.col1 + t2.col2”.
• Currently, there are some limitations on inline select statements such as: select a.sal, (select sal+9999 from empt b where b.empno = a.empno) from ...

What is this feature used for? You can imagine that it is useful for optimization in general, but right now it has a single “client”: Oracle Database In-Memory, where it’s referred to as the Expression Statistics Store (ESS). It’s used to support In-Memory Expressions, so if you've not heard about that already then you should definitely go and check it out.