Insights into Statistics, Query Optimization and the Oracle Optimizer
- August 10, 2017
Expression Tracking
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.