Adaptive mayhem

So you run a query and it gives you a plan with a note that says “This is an adaptive plan”.

So you run it again and the plan changes,  with a note that says “Statistics feedback used for this statement”

So you pause to think for a bit, then run the query again and the plan changes, with a note that says “One SQL Directive used, dynamic statistics used”. You waited too long and the internal re-optimization hints (v$sql_reoptimization_hints) got flushed down into an SQL directive (dba_sql_plan_directives).

So you decide to think about it the following morning when you’re feeling bright and fresh, and when you run it you get another plan because overnight the automatic stats job gathered stats on the critical table and created a column group (dba_stat_extensions) that was indicated by the (now “redundant”) directive.

Happy optimisation!

See also:

• MoS: Bug 19450314 : UNNECESSARY INVALIDATIONS IN 12C
• Chris Antognini’s note on bug 19450314

Update 1:

This just in from fellow Oak Table member Stefan Koehler:If the automatic stats collection task doesn’t finish inside its window that you may get unlucky and find that Oracle has created extended stats on a table, failed to complete the stats collection, and dropped the extended stats. Bug 19450314 mentioned above avoids the problems of unnecessary invalidation when the extended stats are added and collected – it doesn’t deal with the problem if the stats are dropped.

Update 2(Sep 2016):

Fresh news from OpenWorld 2016 on 12.2, courtesy of DBI Services’ Franck Pachot: The parameter contolling adaptive optimisation has been split into two parts, one for the adaptive plans the other for adaptive statistics – so you can officially switch of the bit that’s more likely to cause unexpected side effects while leaving the bit that’s more likely to be beneficial active.

Update 3 (Nov 2016):

The latest news from Stefan Koelher on Bug 19450314 (see update 1 above) is that the unfinished bits of the fix for this bug are now known as bug 21418655. “Not done were the following: MODIFY, RENAME and DROP column (including work on Drop_Extended_Stats).”

Update 4 (Dec 2016)

And now a potential threat (about column groups in general) described in a series of three blog posts from Magnus Johansson. With reference to a relevant MoS Doc Id and patch.

Update 5 (May 2017)

By now it’s probably fairly common knowledge that 12.2 splits the adaptive features into two area (adaptive execution and adapative statistics) that can be enabled or disabled separately. (A good source of information with several related links is here in Mike Dietrich’s blog). But if you’ve already acquired a load of column groups that you don’t want you might want to check Mauro Pagano’s blog for a simple script to eliminate them.

Update 6 (Jan 2018)

Adding insult to injury – if Oracle has silently created some column groups (or other extended stats) on an unpatched version of 12.1 then you’ll find that you can’t rename any of the referenced columns, and will raise Oracle error ORA-54032 if you try.  The error is actually about virtual columns (“column to be renamed is used in a virtual column expression”) rather than extended stats explicitly, but interestingly wouldn’t apply if you had created a function-based index to achieve the same effect. This is MoS bug 21070487 : CAN’T RENAME COL WHEN REFERENCED IN VC’S EXPR; CAN WHEN IN EXPR FOR INDEX (marked as fixed in 12.2).