Last week I indicated a problem of poor performance with Oracle11.2’s cardinality feedback.
Oracle12 expands upon this cardinality feedback to do ‘adaptive plan management’ or the ability to change the execution plan on the fly while executing.
Oracle12 also saves the real row counts from the first execution (cardinality feedback) in the form of SQL Directives, to be used in place of dynamic sampling and other issues where missing stats can come into play…
I’ve always been suspect of how this adaptive plan management will pay out because what Oracle does is create 2 plans…that it can dynamically change to.
Yes, my SQL Tuner (and Active SQL) programs are Oracle12 ready!
Notice the DBMS_XPLAN display above…Oracle12 is set to use Hash Joins but the Nested Loops are ready to go but commented out with a ‘-‘. Notice line 7 of the Explain Plan with its ‘Statistics Collector’. This part is what does the monitoring. When Oracle12 sees that a different plan might work better (ie: Nested Loops work better when you join Larger to Smaller (driving table first is the larger of the 2…) and Hash Joins work better going smaller to larger…see prior blogs on SQL hard parse processing…or by my book: Oracle SQL Tuning: A Close Look at the Cost-based Optimizer available today at Amazon.com), it will switch to the commented out plan.
Well, why I was waiting to see how this played out is because in my 30 years of Oracle SQL tuning, I’ve not once seen where changing a Hash Join to a Nested Loop or visa versa produced a better executing explain plan.
The optimizer group did not ask me what I thought…
So, it’s not working out the best for some folks. The feedback I’m getting is when this is used, again, performance is worse so people have been turning this feature off.
You can use one of these methods to turn the Adaptive Plan Management feature off in Oracle12 using init.ora settings:
optimizer_features_enable < 22.214.171.124…
optimizer_adaptive_reporting_only to true
Thank you for the syntax Robert.
Dan HotkaOracle ACE DirectorAuthor/Instructor/CEO
I have seen in less than a couple of months several NESTED LOOP execution plans running for more than 1 day and completing in a couple of minutes when forced to go via a HASH JOIN execution path :-)