Re: Stored outlines Plans Not getting used...

"RaviAlluru" <ravi.alluru_at_gmail.com> wrote in message news:1132852436.922378.93920_at_g44g2000cwa.googlegroups.com...

I've written a short demonstration that might help and posted it:

    http://www.jlcomp.demon.co.uk/outline_hack.html

Critically, you have to make sure that the text you RUN to generate the outline for is the same as the text you use to generate the 'misleading' outline. This include bind variable substitutions from cursor_sharing. In real applications, you can rarely get away with

    create or replace outline ...

You also need to be a bit careful that don't end up regenerating a bad execution plan because the stored text has bind variables, and the optimizer makes one of its 'usual' bind variable errors. Ideally you need to be able to run the statement against your second environment using the same tool that ran the statement in production.

NOTE - that I did NOT have to hack the outln tables, even though they reference a username and table owner explicitly by name.

If all else fails, before dropping the index, you might try this in the produciton environment.

    rename the index you want used

    rename the index that is being used to have the     name that the desirable index is normally called.

    capture a plan for the query - which will now be     using an index which is the wrong index, but has     the right name.

    rename the indexes back the way they were

    The outline should now force the query to use     the right index, because it simply follows the name.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
Cost Based Oracle: Fundamentals

http://www.jlcomp.demon.co.uk/appearances.html
Public Appearances - schedule updated 4th Nov 2005