Re: SQL Baseline not used.

From: Mohamed Houri <mohamed.houri_at_gmail.com>
Date: Sat, 22 Nov 2014 08:31:49 +0100
Message-ID: <CAJu8R6gmYdC03zL+Fxg596XxT-qQ5G59ixv=C+XjxdPkO5Tjeg_at_mail.gmail.com>

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

Received on Sat Nov 22 2014 - 08:31:49 CET