RE: SQL Profile

> 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

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