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> 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 <r.aialavajjala_at_gmail.com>
> Sent: ‎28/‎09/‎2016 16:34
> To: neil_chandler_at_hotmail.com
> Cc: JBECKSTROM_at_gcrta.org <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
>
> 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