One of the new features in Oracle 11g was SQL Plan Baselines, whereby good execution plans are preserved even if the underlying object statistics are updated. Plans may evolve if analysis shows that a better one is available. However it should not happen that a query suddenly goes from good to poor performance just because statistics have changed, or the database has been upgraded. So after some experimentation, I used to switch on automatic capture of baselines for the whole database; after all, the documentation for this parameter in the Reference manual doesn’t say this is a bad idea.
ALTER SYSTEM SET optimizer_capture_sql_plan_baselines=TRUE SCOPE=BOTH;
However with the release of Oracle 12c, which included features such as bind sensitivity and cardinality feedback, I started to have misgivings about capturing baselines for all SQL. Would this capture a baseline before the new features had a chance to refine the execution plan?
I started to give this more attention when after patching a development database to 18.104.22.168 I got occasional errors caused by LOB index corruption in the XML column of the table that stores baselines. What was more worrying was that the usual procedure to drop an affected baseline would fail with the same error, and I needed to delete the row from the table in SYS manually before the procedure would work to tidy up the rest!
So with all this in mind, it was by chance that two interesting articles popped into my blog roll:
- First, Dom Brooks expresses nicely a number of the concerns that had been on my mind: https://orastory.wordpress.com/2015/05/01/strategies-for-minimising-sql-execution-plan-instability/ The quote from Jonathan Lewis about histograms and bind variables was especially interesting, although is true whether or not you are thinking about baselines.
- Secondly, an article from the optimizer team tells me that my configuration to collect all plans is indeed a bad idea! https://blogs.oracle.com/optimizer/entry/what_you_need_to_know
As a result of all this, I’ve decided to change tack with my development databases, and see how a new approach works out:
For a start, I’ve switched off capturing baselines by default:
ALTER SYSTEM RESET optimizer_capture_sql_plan_baselines SCOPE=SPFILE;
From now on I will only capture a baseline when a query runs poorly and none of the other automatic mechanisms can deal with it. Clearly this doesn’t meet the goal of ultimate plan stability, so for a production system I would expect the occasional incident where the plan has changed for the worse. However a proactive approach of capturing baselines for critical statements would mitigate the risk involved.
Bind variables vs. Histograms is an interesting question. Most of my work involves writing PL/SQL. PL/SQL encourages the use of bind variables, and in fact makes it awkward to convert parameters to literals; you need to code dynamic SQL. Therefore I am switching off histograms across the board:
exec dbms_stats.set_global_prefs('METHOD_OPT','FOR ALL COLUMNS SIZE 1')
This has the advantage of making stats collection take less time, and less space is used in the data dictionary to hold unnecessary histograms.
When investigating poorly performing statements, I expect some will be the result of skewed data distribution. In these cases I would set a table preference to collect histograms for the specific columns that require them. I would also look to change the PL/SQL to use dynamic SQL if it is necessary to convert a bind variable to a literal.
For my development databases this has all worked well so far, though I will reserve judgement a little longer before I consider doing the same on a production system!