A new feature called 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:
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.