When Oracle would Choose an Adaptive Execution Plan – General Thoughts

March 31, 2014

Adaptive Execution Plans is one of the most existing new features in Oracle 12c.
This post is not about how this feature works or its benefits, but rather about when Oracle would choose to use it.

In general, the Oracle CBO would use Adaptive Execution Plans if it is not sure which standard join (NL or HJ) is better:

  • If at SQL parse time, the Oracle CBO estimated that one of the sets to join is “significantly” smaller the other, where “significantly” is defined internally by the CBO, and there are appropriate indexes, then Oracle would opt for Nested Loops. Oracle CBO probably figured out that that the cost of NL is so much better than the cost of HJ, so it is not worth the effort of using an adaptive execution plan.
  • If one of the sets is only “slightly” smaller than the other, where “slightly” is defined internally by the CBO, then the performance of the two standard join types would be similar, so Oracle would typically decide to go with an Adaptive Plan and postpone the decision until run time. Oracle CBO probably saw that that that the cost of NL is “close” to the cost of HJ, so it is worth the effort of using an adaptive execution plan.
  • Finally, when the two sets have “similar” sizes, where “similar” is defined internally by the CBO, then Oracle would go with Hash join. Oracle CBO probably figured out that that the cost of HJ is so much better than the cost of NL, so it is not worth the effort of using an adaptive execution plan.

The figure below illustrates that behavior:

adaptive_exec_plans

Advertisements