Re: Usage of Baselines to prevent plan changes

Not to try and hijack this thread - but since the subject of this was around baselines - I thought I would tack on a question as relates to baseline behavior.

I have a SQL statement that seems to "flop" between 2 PHVs'

good plan_hash_value=>461486239
bad plan_hash_value => 952719881

A baseline was created using the statement "exec :cnt:=dbms_spm.load_plans_from_cursor_cache(sql_id=>'1hzgfq62vat5h',plan_hash_value=>461486239);"

As of 2016-09-15 11:47 in SBD629:
Plan hash value: 461486239
SQL Tuning Set Name: 1hzgfq62vat5h_461486239 SQL profile "coe_1hzgfq62vat5h_461486239" used for this statement The SQL Plan Baseline that was created in on 2016-09-15 11:47 with MODULE=and OPTIMIZER_COST=29920 has the following attributes: Plan hash value: 952719881
Plan name: SQL_PLAN_ac64bzwy69srx1ee46c4c Plan id: 518286412 SQL handle: SQL_a6188bff3c64e2fd

Note that the baseline appears to be using the "bad" PHV inspite of the fact that it was created using "load_plans_from_cursor_cache" - is there a reason for this behavior ?

Or am I missing something very obvious ?

I'd appreciate your thoughts...

Thanks,

--Rajesh

On Wed, Sep 28, 2016 at 11:24 AM, Neil Chandler <neil_chandler_at_hotmail.com> wrote:

> 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_at_gcrta.org
> To: oracle-l_at_freelists.org; oracle-db-l_at_Groups.ITtoolbox.com
> 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
>
>
> .
>
>

--
http://www.freelists.org/webpage/oracle-l