Here is a zip of two examples I built as I’m learning about the new adaptive plans features of Oracle 12c: zip
The first example has the optimizer underestimate the number of rows and the adaptive plans feature switches the plan on the fly from nested loops to hash join.
In the second example the optimizer overestimates the number of rows and the adaptive plans feature switches the plan from merge join to nested loops.
I ran the same scripts on 12c and 11.2.0.3 for comparison.
Example 1 11g:
Plan hash value: 2697562628 ------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 18 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 18 | | 2 | NESTED LOOPS | | 1 | | 8 |00:00:00.01 | 18 | | 3 | NESTED LOOPS | | 1 | 1 | 8 |00:00:00.01 | 17 | |* 4 | TABLE ACCESS FULL | T1 | 1 | 1 | 8 |00:00:00.01 | 14 | |* 5 | INDEX RANGE SCAN | T2I | 8 | 1 | 8 |00:00:00.01 | 3 | | 6 | TABLE ACCESS BY INDEX ROWID| T2 | 8 | 1 | 8 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------
Example 1 12c:
----------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | ----------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 6 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 6 | | | | |* 2 | HASH JOIN | | 1 | 1 | 8 |00:00:00.01 | 6 | 2168K| 2168K| 1/0/0| |* 3 | TABLE ACCESS FULL| T1 | 1 | 1 | 8 |00:00:00.01 | 3 | | | | | 4 | TABLE ACCESS FULL| T2 | 1 | 1 | 16 |00:00:00.01 | 3 | | | | -----------------------------------------------------------------------------------------------------------------
Example 2 11g
--------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | O/1/M | --------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 16 | | | | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 16 | | | | | 2 | MERGE JOIN | | 1 | 4 | 1 |00:00:00.01 | 16 | | | | | 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 16 | 2 |00:00:00.01 | 2 | | | | | 4 | INDEX FULL SCAN | T2I | 1 | 16 | 2 |00:00:00.01 | 1 | | | | |* 5 | SORT JOIN | | 2 | 4 | 1 |00:00:00.01 | 14 | 73728 | 73728 | | |* 6 | TABLE ACCESS FULL | T1 | 1 | 4 | 1 |00:00:00.01 | 14 | | | | ---------------------------------------------------------------------------------------------------------------------------
Example 2 12c
------------------------------------------------------------------------------------------------ | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | ------------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | | 1 |00:00:00.01 | 5 | | 1 | SORT AGGREGATE | | 1 | 1 | 1 |00:00:00.01 | 5 | | 2 | NESTED LOOPS | | 1 | | 1 |00:00:00.01 | 5 | | 3 | NESTED LOOPS | | 1 | 4 | 1 |00:00:00.01 | 4 | |* 4 | TABLE ACCESS FULL | T1 | 1 | 4 | 1 |00:00:00.01 | 3 | |* 5 | INDEX RANGE SCAN | T2I | 1 | | 1 |00:00:00.01 | 1 | | 6 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 1 | 1 |00:00:00.01 | 1 | ------------------------------------------------------------------------------------------------
The output of the plans for the 12c examples end with this line:
Note ----- - this is an adaptive plan
So, that tells me it is the adaptive plan feature that is changing the plan despite the wrong estimate of the number of rows.
– Bobby
Hi Bobby,
I have a doubt regarding adaptive plans/adaptive reoptimization.
The join method could switch from NL to HJ or vice versa as a result of
– adaptive plans during first execution of the statement itself
or
– adaptive reoptimization on second or subsequent executions of the statement
What criteria is used by the optimizer to decide whether te result is adaptive plan / reoptimization ?
Thanks and regards
Anju Garg
Anju,
Thank you for your comment and your question. The examples in the blog post demonstrate adaptive plans which change join methods during the first execution of the SQL query.
The SQL tuning manual says that adaptive plans can change a query’s join method during its first execution, but that it can’t change things like the plan’s join order.
The manual also says that adaptive optimization can occur after the query completes its first execution and the optimizer finds that the number of rows actually returned differs from the number expected. On the next run the query’s plan will be completely recreated from scratch using the observed row counts so things like join order can be changed.
– Bobby
Thanks Bobby for your reply.
My question is still unanswered. In some joins, statistics collector appears which might lead to plan switching during first execution of the SQL itself. While in other cases, statistics collector is not there and hence query might be optimized on subsequent executions if optimizer estimates differ considerably from the actual rows returned by the query. The question is why statistics collector is not present in case of adaptive reoptimization?
Hope I am able to express my question clearly.
Regards
Anju Garg
Anju,
Thanks again for your question. I think you are asking how the optimizer chooses when to add the statistics collector into the plan. I checked the documentation that I’ve been studying and this is all it says:
“An optimizer statistics collector is a row source inserted into a plan at key points to collect run-time statistics.”
“At key points” is kind of vague. I would like to know the answer to your question also, but I don’t. To figure it out I would build a series of small test cases and come up with a theory based on the results. I have a feeling that the optimizer checks for certain simple situations to insert the statistics collector such as where the first rowsource in the join is a table instead of the result of a join and where the first rowsource in the join has conditions which are likely to result in wrong row count estimates that can be corrected by adaptive plans.
– Bobby
Thanks Bobby for your reply.
Please do share the results of the test cases you will conduct.
Regards
Anju Garg