RE: Force matching and SQL plan management

From: max scalf <oracle.blog3_at_gmail.com>
Date: Tue, 26 Nov 2013 07:13:57 -0800
Message-ID: <2029555687809185217_at_unknownmsgid>

  Great article Mohammed houri. Thank you all for sharing your expertise.

Sent from my Windows Phone

---

From: Mohamed Houri <mohamed.houri_at_gmail.com> Sent: 11/23/2013 10:47 AM
To: breitliw_at_centrexcc.com
Cc: oracle.blog3_at_gmail.com; Dominic Brooks <dombrooks_at_hotmail.com>; oracle-l_at_freelists.org
Subject: Re: Force matching and SQL plan management

Hi Abdul,

I have investigated a little bit how baseline and CBO interacts in this article

 http://hourim.wordpress.com/2013/05/05/spm-baseline-selection-how-it-works/

In which I wrote the following conclusion

When using SPM baseline to guarantee plan stability, be warn that when you have several enabled and accepted plan for the same SQL matching signature and, if for any reason those plans become non reproducible, you might pay a parsing time penalty because the CBO will use two rounds trying to reproduce *all* SPM plans – even though they are impossible to reproduce–

Best regards
Mohamed Houri
www.hourim.wordpress.com

2013/11/23 Wolfgang Breitling <breitliw_at_centrexcc.com>

> Don't confuse profiles and baselines. Profiles can be used with force
> matching signature. Baselines only with exact match on SQL_id
>
> Sent from my iPhone. Typing errors may have occurred.
>
> On Nov 23, 2013, at 8:34, Abdul Mohammed <oracle.blog3_at_gmail.com> wrote:
>
> hi Dominic,
>
> Just a follow up question on Baseline. You mentioned having baseline
> increase the parsing overhead...are we talking hard parse here or
> softparse? As the baseline already keeps track some of the plans(which are
> not accepted), dose it do the parsing each time the SQL is execute...maybe
> i was under the wrong impression, when you have baseline in place i thought
> oracle by passes the oracle plans(not accepted) and just goes directly to
> the one fixed....
>
>
> On Sat, Nov 23, 2013 at 12:19 AM, Dominic Brooks <dombrooks_at_hotmail.com>wrote:
>
>>
>> 1. Would putting this in place avoid hard parsing or would it still go
>> though the hard parse phase(even with profile is set). Do note the
>> signature(force_matching_signature is same for this type of SQL)
>>
>>
>> You'd probably want to be looking at an outline-style profile with
>> force_match set to true. See Oracle-supplied script coe_xfr_sql_profile.sql
>> available from Oracle Support website.
>>
>> 2. Can this be done with SQL Plan Baseline, as i rather use Baseline
>> instead of profile. As Baseline seems to work for me in the past and
>> profile sometimes do not work.
>>
>>
>> Baselines do not offer the same force matching capability, at least not
>> in 11g.
>> They also have the potential to increase the parsing overhead as they
>> always generate the best cost plan first before trying to reproduce the
>> baselined plan with multiple strategies if unsuccessful.
>>
>> Cheers,
>> Dominic
>>
>
>

-- 
Bien Respectueusement
Mohamed Houri

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

Received on Tue Nov 26 2013 - 16:13:57 CET