Re: SQL Baseline not used.

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Sun, 23 Nov 2014 09:29:56 +0100
Message-ID: <CAJu8R6hcZ3uyJ_-CY8wKN=dodWfOQXxdUk1F+db+m5bxdYrRPA_at_mail.gmail.com>



Thanks Maris for this important correction. You are right. SPM does not offer the same force matching capability as Profile does. This is why *exact *matching signature is the right word in this context

Thanks again for pointing out this mistake

Best regards
Mohamed Houri

2014-11-22 23:49 GMT+01:00 Maris Elsins <elmaris_at_gmail.com>:

> Mohamed,
>
> Isn't it the *exact* matching signature that's used to look up the
> matching baseline? (you mentioned force matching signature couple of time
> in the previous email)
>
> ---
> Maris Elsins
> _at_MarisElsins <https://twitter.com/MarisElsins>
> www.facebook.com/maris.elsins
>
>
>
> On Sat, Nov 22, 2014 at 9:31 AM, Mohamed Houri <mohamed.houri_at_gmail.com>
> 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_at_bluewin.ch>:
>>
>>> 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
>>>
>>>
>>>
>>> --
>>>
>>>
>>>
>>>
>>>
>>>
>>>
>>> ------------------------------
>>> <http://www.avast.com/>
>>>
>>> Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus
>>> <http://www.avast.com/> Schutz ist aktiv.
>>>
>>>
>>
>>
>> --
>>
>> Houri Mohamed
>>
>> Oracle DBA-Developer-Performance & Tuning
>>
>> Member of Oraworld-team <http://www.oraworld-team.com/>
>>
>> Visit My - Blog <http://www.hourim.wordpress.com/>
>>
>> Let's Connect - <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
>> Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*
>>
>> My Twitter <https://twitter.com/MohamedHouri> - MohamedHouri
>> <https://twitter.com/MohamedHouri>
>>
>>
>

-- 

Houri Mohamed

Oracle DBA-Developer-Performance & Tuning

Member of Oraworld-team <http://www.oraworld-team.com/>

Visit My         - Blog <http://www.hourim.wordpress.com/>

Let's Connect -
<http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*Linkedin
Profile <http://fr.linkedin.com/pub/mohamed-houri/11/329/857/>*

My Twitter <https://twitter.com/MohamedHouri>      - MohamedHouri
<https://twitter.com/MohamedHouri>

--
http://www.freelists.org/webpage/oracle-l
Received on Sun Nov 23 2014 - 09:29:56 CET

Original text of this message