SQL Plan Management – 12C dumb feature
08/12/2013 4 Comments
In Oracle 11G, Oracle introduced SQL Plan Management (SPM). It is excellent (I love it to bits). It allows you to create Baselines against SQL which lock-down the SQL execution plan. No more plan flips. More consistency. Perfect**.
Whenever some Baselined SQL is ran, Oracle still parses it and compares the parsed output to the accepted (Evolved) baselines. If the newly parsed plan is better, a new baseline is added to DBA_SQL_PLAN_BASELINES but is NOT accepted. This means that you need to spend time manually accepting the baseline; running the command DBMS_SPM.EVOLVE_SQL_BASELINE plan and checking the new plan.
If you want it, and/or Oracle evaluates that is it a better plan for that particular set of bind variables, the plan is accepted and becomes a candidate to be used by future execution of your SQL. Complete control over your execution plans.
So, Oracle, what’s all this about in Oracle 12C, eh?
In Oracle 12C there’s a new SPM Evolve advisor task. “By default, SYS_AUTO_SPM_EVOLVE_TASK runs daily in the scheduled maintenance window” – So, it runs every night and by default it runs DBMS_SPM.EVOLVE_SQL_BASELINE for all new baselines created today and automatically accepts the new plans.
BY DEFAULT? NO! NO! NO!
That is precisely what I don’t want from baselines – Oracle making it’s own mind up about plans without any input from me. I’m using baselines to stop Oracle changing its mind. To explicitly limit the number of paths allowed by the Optimizer to ones I know about and with which I am comfortable. Don’t introduce functionality to do the opposite.
So, immediately following the installation of 12C, I would recommend running (you need to be SYS for this):
SELECT PARAMETER_NAME, PARAMETER_VALUE AS "VALUE"
FROM DBA_ADVISOR_PARAMETERS
WHERE TASK_NAME = 'SYS_AUTO_SPM_EVOLVE_TASK' AND
PARAMETER_NAME in ('ACCEPT_PLANS','TIME_LIMIT')
ORDER BY 1;
PARAMETER_NAME VALUE
------------------------- ----------
ACCEPT_PLANS TRUE
TIME_LIMIT 3600
Then run:
BEGIN
DBMS_SPM.SET_EVOLVE_TASK_PARAMETER('SYS_AUTO_SPM_EVOLVE_TASK',
'ACCEPT_PLANS', 'false');
END;
/
OK, back where we were, with any baselines fixed in place and doing what I want them to do! Not change.
**Perfect? No. But Baselines are good and, as long as your DB structure does not change, they should keep working. If they don’t, raise an SR with Oracle as it’s probably a bug.
UPDATE 2015-11-25: This is still as true in 12.1.0.2.5 as it was in 12.0 Grrrr!!!
Neil Chandler's DB Blog 
Reduce the TIME_LIMIT as well so that it doesn’t spend time doing this ?
LikeLike
Worthwhile to minimise the impact of autoevolve running, yes.
You might want to consider disabling the autotuning autotask… Are you using the output from it?
LikeLike
Create all your baselines as FIXED – no evolution.
LikeLike
but I want to be able to evolve my baselines. I just want to do it explicitly, on my terms. Not because Oracle thinks it has a better plan, has tested it and decided that yes, it knows best. As any fule kno, it does not.
You also need to be a little careful fixing baselines. The optimizer will always pick a fixed baseline over a non fixed baseline if you have multiple baselines for a SQL statement, even if the non fixed baseline has a lower cost for the input binds. You need to be twice as careful if the recommendation is to add a SQL Profile, as that is added as a non-fixed baseline to the fixed baseline and I get all confused about what the darned thing is up to.
LikeLike