Re: Usage of Baselines to prevent plan changes

From: Mauro Pagano <mauro.pagano_at_gmail.com>
Date: Wed, 28 Sep 2016 13:05:28 -0400
Message-ID: <CAAnDMS1Xmmek_n6iAgxW0G030LYxr7tzwA5n3a1-EiQnKR7Btw_at_mail.gmail.com>



My 2cents, get rid of the custom SQL Profile and create a baseline, then open a SR with Oracle.
If the SQL doesn't fall into one of those categories that de-activate SPM (for example, distributed SQL) then SPM must reproduce the plan, and if not Oracle has ownership of the issue.
That means if the hintset is somehow not enough Oracle has to fix it, it already happened for USE_CONCAT, UNNEST and MERGE hints.

As to why the good plan is chosen, it's probably because the baseline (or custom SQL Profile) never fully worked so the few decisions left to the CBO sometimes lead to the good plan, sometime the bad one :-)

On Wed, Sep 28, 2016 at 12:47 PM, Rajesh Aialavajjala < r.aialavajjala_at_gmail.com> wrote:

> 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=>'1hzgfq6
>> 2vat5h',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
Received on Wed Sep 28 2016 - 19:05:28 CEST

Original text of this message