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@xxxxxxxxx>: > 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> >> >> > -- 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>