Re: SQL Baseline not used.
- From: Lothar Flatz <l.flatz@xxxxxxxxxx>
- To: krishna000@xxxxxxxxx
- Date: Sat, 22 Nov 2014 08:13:37 +0100
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
Other related posts:
