Baselines are the best Oracle has come up with so far to lock down your plans.
If it's possible to reproduce the plan, it will (OK - there's the odd failure I
have heard reported but I've never hit one.)
- SQL Profiles tend to use OPT_ESTIMATE, which is basically advanced stats
changing cardinality ratios and as your data changes, it doesn't. I'm not a fan.
- Hints tend to be badly implemented and shouldn't be "specific" to drive a
plan but "generic" to influence how the optimizer rewrites the code. They
really should be a last resort.
Baselines allow one or more plans to be used. Any other plans that come along
with a better cost will be captured and kept but not "accepted" and therefore
not used.
A new index would indeed be ignored initially but the "better" plan would be
captured. By default, that night an autotask (SYS_AUTO_SPM_EVOLVE_TASK) will
run and auto-evolve the baseline and tomorrow you're going to be using a whole
new set of plans (the old plans will still be available to be chosen too).
Personally I would recommend you disable the autotask and run the evolve
yourself (using DBMS_SPM) so plans don't change "unexpectedly" overnight, and
only when you run an evolve manually. Adding a new index and adopting a new
plan would therefore be 1) add index 2) run SQL so it will hard parse and 3)
run a baseline evolve to accept the new plan.
Part of the real power of baselines is the ability to evolve them and have one
or more potential plans available to the optimizer, but only those which you
allow.
There is a hard parse overhead (cost is parsed with no hints and then parsed
again with hints to replicate the baseline) but unless you are really
parse-heavy, you're not going to notice.
regards
Neil C
https://chandlerdba.wordpress.com/
Date: Wed, 28 Sep 2016 10:05:19 -0400
From: JBECKSTROM@xxxxxxxxx
To: oracle-l@xxxxxxxxxxxxx; oracle-db-l@xxxxxxxxxxxxxxxxxxxx
Subject: Usage of Baselines to prevent plan changes
Over the past year, we have encountered problems where a SQL statement suddenly
starts performing badly. In the past, we would either create a SQL Profile or
alter the SQL with hints to get the old plan back. Just wondering if people
used Baselines to prevent this form occurring and how well they work? This is
an Oracle EBS system. If we use Baselines and Oracle changes the table
structure adding an index to improve performance, would the new index be
ignored because of the Baseline?
Jeffrey Beckstrom
Lead Database Administrator
Information Technology Department
Greater Cleveland Regional Transit Authority
1240 W. 6th Street
Cleveland, Ohio 44113
.
