Oracle 12c – Adaptive Execution Plans

Oracle 12c – Adaptive Execution Plans

Having not done a great deal with Oracle 12c, I was quite excited today to see an adaptive execution plan arise from a very simple example. An adaptive plan is quite simply a situation where the optimizer chooses what it believes to be the most optimal plan at parse time, however upon execution uses runtime information to validate that was indeed the correct plan to use, and if not, can switch to using an alternative plan instead. This is different from pre-12c, where the explain plan might be different from the actual plan used – in 12c the optimizer actually starts out using one plan, but then changes “mid-execution”.

Maria Colgan (who pre Oracle In-Memory was known as the optimizer lady) describes this in her post here.

In the example I was using, I have a very simple query:

Select /*+gather_plan_statistics*/ *   From hr.employees emp,        hr.departments dept  Where dept.department_id = emp.department_id    And emp.last_name Like 'Whalen%'; 

Which when doing an “explain plan” yields the following (taken from Toad as that’s what I was using):

Plan 1

However looking at what plan was actually used, we see something different.


Plan 2

See that magic line at the bottom? The optimizer started doing one thing but then decided mid-execution that there was in fact a better (more cost-effective) plan given the data it had already retrieved, and so switched to using that plan instead. The Index Access and Hash Join on DEPARTMENTS has gone, in favour of a Nested Loops Index access.

Another reason for not using “Explain Plan”. 🙂

Leave a Reply

Your email address will not be published. Required fields are marked *

one × two =

This site uses Akismet to reduce spam. Learn how your comment data is processed.