Re: SQL Baseline not used.

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>

>

>


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