RE: SQL Profile
- From: "Christian Antognini" <Christian.Antognini@xxxxxxxxxxxx>
- To: <karlarao@xxxxxxxxx>
- Date: Thu, 23 Jul 2009 08:58:15 +0200
Hi Karl
> We have this SQL with a good SQL profile.. then suddenly something
> changed on the optimizer and the good profile was not used anymore on
> its next execution.
Mhmm... I'm puzzled. Do you mean that the execution plan has changed or
that the SQL profile is no longer used? These are two completely
different things. In fact, by design, the SQL profile doesn't force a
specific execution plan. And, therefore, it is normal that the execution
plan can change.
> 1) Can I force the SQL to use the old SQL profile or SQL profile's
> plan? I don't see on the DBMS_SQLTUNE any option to use the old
> profile or plan.
Again, there is no old or new SQL profile... Or you recreate the SQL
profile regularly? I guess it's not the case. Therefore, there is only
an old or new execution plan. If the profile is still there and,
therefore, only the execution plan has changed, to "see" the old
execution plan you have to bring the old execution environment back. For
example, if the object statistics have changed, you have to restore them
from the history...
> 2) If I have a backup of the profile from a table using
> DBMS_SQLTUNE.CREATE_STGTAB_SQLPROF and
> DBMS_SQLTUNE.PACK_STGTAB_SQLPROF
> and apply it again using DBMS_SQLTUNE.UNPACK_STGTAB_SQLPROF. Is there
> a way to use the good old plan of that profile?
Again, the SQL profile doesn't force to use a specific execution plan.
Hence, you need exactly the old execution environment to have the old
execution plan.
HTH
Chris Antognini
Troubleshooting Oracle Performance, Apress 2008
http://top.antognini.ch
--
//www.freelists.org/webpage/oracle-l
Other related posts:
