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 @MarisElsins <https://twitter.com/MarisElsins> www.facebook.com/maris.elsins On Sat, Nov 22, 2014 at 9: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 >> >> >> >> -- >> >> >> >> >> >> >> >> ------------------------------ >> <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> > >