Re: SQL Profile
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: karlarao@xxxxxxxxx
- Date: Wed, 22 Jul 2009 10:38:05 -0500
Karl,
I probably wouldn't go down the path of trying to get the profile
back and try to make the optimizer use it. I'd probably try to get the
statement to do what I wanted (through whatever trickery you can come
up with) and then create a new profile or outline (or baseline if
you're on 11g). Profiles (and Outlines and Baselines even) can be
somewhat flakey in my humble opinion, mainly I think because they are
not well documented and therefor not well understood. And contrary to
popular opinion, they don't really lock plans in place. They just cut
down the optimizers options (hopefully to the point where it can only
choose the plan you want, but not always).
Randolf Geist has an excellent post here: http://oracle-randolf.blogspot.com/2009/03/plan-stability-in-10g-using-existing.html
. I borrowed some of his ideas and wrote some stuff on my blog as well.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Jul 22, 2009, at 6:10 AM, Karl Arao wrote:
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.
I know we could have made it permanent using an outline on the profile
but we were too late.
Question:
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.
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?
- Karl Arao
http://karlarao.wordpress.com
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
