Re: Force matching and SQL plan management
- From: Abdul Mohammed <oracle.blog3@xxxxxxxxx>
- To: Dominic Brooks <dombrooks@xxxxxxxxxxx>
- Date: Sat, 23 Nov 2013 09:34:49 -0600
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@xxxxxxxxxxx>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
>
Other related posts:
