Re: SQL Baseline not used.
- From: Andrew Kerber <andrew.kerber@xxxxxxxxx>
- To: "mohamed.houri@xxxxxxxxx" <mohamed.houri@xxxxxxxxx>
- Date: Mon, 24 Nov 2014 11:52:59 -0600
Uh guys, force matching is a yes/no value that tells the optimizer whether or
not to replace static conditions in the query with bind variables when
determining if this query has a sql profile it should use. it is not actually
a signature.
Sent from my iPad
> On Nov 22, 2014, at 1:31 AM, Mohamed Houri <mohamed.houri@xxxxxxxxx> wrote:
>
> When a SQL statement is protected by a SQL plan baseline, the next time you
> run the same sql statement (the same force matching signature) this SQL
> statement will end up by using the SQL plan baseline provided that this one
> is still reproducible. And there are many reasons that make a SQL plan
> baseline not anymore reproducible including dropping an index, renaming an
> index, changing the index type, using a different nls_sort than the one used
> at SQL plan baseline creation time, etc....
>
> In your case, there might be two reasons for not using the Baselined plan:
>
> 1) the force matching signature of your current statement is not the same as
> the one used to compile the SQL plan baseline execution plan
> 2) the SQL plan baseline is not anymore reproducible
>
> When you issue your SQL statement the optimizer will parse its execution plan
> as if there was no SQL plan baseline. It is only when it has finished to
> parse the new plan that it will start to see if the new parsed plan exists in
> the SQL plan baseline or not. If not found in the SPM plan Baseline then all
> existing SPM plan baselines (for the same force matching signature) will
> enter in competition and the best re-costed one will be used. This is why the
> suggestion to have multiple SPM plans for a single force matching signature
> might not be always a good solution.
>
> I have a series of article about SPM you can find here below
>
> http://hourim.wordpress.com/category/sql-plan-managment/
>
> Best regards
> Mohamed Houri
> www.hourim.wordpress.com
>
>
>
> 2014-11-22 8:13 GMT+01:00 Lothar Flatz <l.flatz@xxxxxxxxxx>:
>> Hi Bala,
>>
>> to answer your questions:
>>
>> 1.If the cached SQL in shared pool flushes out will it not use created SQL
>> Baseline anymore?
>>
>> It should still use the same SQL baseline when it is reparsed. The
>> prerequisite is that the statement is written in the same way and is
>> identified as identical to the one the baseline was created for. Check if
>> that is a dynamically generated sql that varies in Details.
>> If that happens the sql id will change as well.
>> I have never had this issue thus i don't know if there are any kind of
>> subtle changes could cause a baseline to be neglected.
>>
>> 2.I have to fix the plan permanently irrespective of Gather Stats ran on the
>> underlying tables weekly or whatever the reason i'd like to fix the plan
>> that has given the proven results.
>>
>> The baseline should over rule statistics.
>>
>> 3.How do i retrieve the plan and fix it for permanent use.
>>
>> That the depends on the issue that causes the baseline to be neglected. If
>> it is a dynamically generated statement in most cases the where clause will
>> vary a bit. Then you can generate multiple baselines, one for each variation.
>> You could also try to fix the underlying root cause of the bad plan, which
>> will fix every variation of the statement. For example the root cause is
>> often a bad estimate caused by a statistical relationship in the data that
>> is not represented well in database statistics.
>> Extended statistics could help.
>>
>> Regards
>>
>> Lothar
>>
>>
>> Am 22.11.2014 03:29, schrieb Bala Krishna:
>>> Hi All,
>>>
>>> I've created a tuning task
>>>
>>> dbms_sqltune.create_tuning_task(task_name=>'&&Task_name',
>>> sql_id=>'&sql_id', time_limit=>&time_limit);
>>>
>>> Tuning Task reported to create a sql plan baseline, i've created that but
>>> only for the first time it used and executed fast. For the next run it
>>> created a different plan_hash_value and may be because of that Sql Baseline
>>> not used.
>>>
>>> I've not fixed Sql Baseline that i have created it.
>>>
>>> Can somebody please answer me following questions.
>>>
>>> 1.If the cached SQL in shared pool flushes out will it not use created SQL
>>> Baseline anymore?
>>> 2.I have to fix the plan permanently irrespective of Gather Stats ran on
>>> the underlying tables weekly or whatever the reason i'd like to fix the
>>> plan that has given the proven results.
>>> 3.How do i retrive the plan and fix it for permanent use.
>>>
>>> Regards
>>> Bala
>>
>>
>> --
>>
>>
>>
>>
>>
>>
>>
>> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
>> Schutz ist aktiv.
>>
>
>
>
> --
> Houri Mohamed
> Oracle DBA-Developer-Performance & Tuning
> Member of Oraworld-team
> Visit My - Blog
> Let's Connect - Linkedin Profile
> My Twitter - MohamedHouri
Other related posts:
