Re: SQL Baseline not used.

From: Lothar Flatz <l.flatz_at_bluewin.ch>
Date: Sat, 22 Nov 2014 08:13:37 +0100
Message-ID: <547037A1.9010702_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
>

-- 






---
Diese E-Mail ist frei von Viren und Malware, denn der avast! Antivirus Schutz ist aktiv.
http://www.avast.com

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

Received on Sat Nov 22 2014 - 08:13:37 CET