Re: stored outlines used but wrong plan executed

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<414b112d_at_post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
>
> "Arik" <arik_samson_at_excite.com> wrote in message
> news:699f12fe.0409161337.6ec7abec_at_posting.google.com...
> > I wonder if anybody has stumbled upon the same problem:
> > Trying to optimize a query in version 9.2.0.4 with a stored outline. I
> > manipulated the outline to have the right hints, altered session to
> > use the outline, but when I run the query, although the outline is
> > used, the wrong plan is executed.
> >
> > Thanks,
> > Arik
>
> Hi Arik
>
> I already had similar problems. Since outlines are hints and, like any
> hints, sometimes the CBO decide not using them, it happens that an outline
> simply doesn't work :-(
>
> How did you modify the outline? If you used the Outline Editor, did you get
> the "correct" plan when you tried to validate the changes?
>
> Chris
>
>
>

Hi Chris,

The way I dit it was first create outline on the original query with "alter session set create_stored_outlines=<category>. Then I created another outline in the same category with "create or replace outline...." on the same query but with a hint. Afterwords I swapped the ol$hints lines between the two outlines so that the original query ended up with the hints from the modified query. The second query (with the hint) picks up an index and runs in a jiffy. The one without the hint does a full table scan and it keeps scanning even with the outline.

Arik Received on Sat Sep 18 2004 - 21:43:26 CDT