RE: Usage of Baselines to prevent plan changes
Date: Wed, 28 Sep 2016 19:55:12 +0000
Message-ID: <8CA507E7F87805479C5C3DF54AA713A780C7BD86_at_LISL-XMBS-14-PP.snaponglobal.com>
Dbms_xplan.display_cursor or dbms_xplan.display_awr will include a note indicating a SQL Plan Baseline was used, if appropriate. So that should tell you if the good plan was happening as a result of the baseline, or just by luck.
One thing I have seen that makes baselines ‘less reproducible’ is the generation of temporary table names that is common with queries that use Common Table Expressions (WITH…AS…). If the names are generated differently then the plans are different and the baseline may be non-reproducible. I don’t know if that has improved since the 11.1 environments when I saw it happening.
Mike Tefft
From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Rajesh Aialavajjala Sent: Wednesday, September 28, 2016 12:47 PM To: Dominic Brooks <dombrooks_at_hotmail.com> Cc: neil_chandler_at_hotmail.com; JBECKSTROM_at_gcrta.org; Oracle-l <oracle-l_at_freelists.org>; oracle-db-l <oracle-db-l_at_groups.ittoolbox.com> Subject: Re: Usage of Baselines to prevent plan changes
Dominic,
Thank you for your reply.
Would this - "baseline hintset not sufficient to reproduce the desired plan" - also be the cause for why the optimizer chooses the "bad" plan even when there's a profile in place ? Given of course - that the existence or application of a profile would not necessarily imply that the "good" plan will always be chosen.
So - the baseline creation itself was done using the sub-optimal PHV because the baseline hintset was insufficient - but that raises the question as to why the "good" plan is chosen 90% of the time with the profile in place ?
Is this a candidate for SPM Tracing ?
Thanks,
--Rajesh
On Wed, Sep 28, 2016 at 12:11 PM, Dominic Brooks <dombrooks_at_hotmail.com<mailto:dombrooks_at_hotmail.com>> wrote: Rajesh - I would guess that this is a case where the baseline hintset is not sufficient to reproduce the desired plan and quite possibly the same reason the COE SQL profile doesn't work using, presumably, the same hintset
Sent from my Windows Phone
From: Rajesh Aialavajjala<mailto:r.aialavajjala_at_gmail.com> Sent: 28/09/2016 16:34
To: neil_chandler_at_hotmail.com<mailto:neil_chandler_at_hotmail.com> Cc: JBECKSTROM_at_gcrta.org<mailto:jbeckstrom_at_gcrta.org>; Oracle-l<mailto:oracle-l_at_freelists.org>; oracle-db-l<mailto:oracle-db-l_at_groups.ittoolbox.com> Subject: 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<mailto: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<mailto:JBECKSTROM_at_gcrta.org> To: oracle-l_at_freelists.org<mailto:oracle-l_at_freelists.org>; oracle-db-l_at_Groups.ITtoolbox.com<mailto: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
Received on Wed Sep 28 2016 - 21:55:12 CEST