Re: Force matching and SQL plan management

From: Wolfgang Breitling <breitliw_at_centrexcc.com>
Date: Sat, 23 Nov 2013 09:01:42 -0700
Message-Id: <3398E0D9-2B77-4D34-92D6-2F8E1AA5B16A_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

>

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

Received on Sat Nov 23 2013 - 17:01:42 CET