Re: stored outlines used but wrong plan executed

"Christian Antognini" <christian.antognini_at_trivadis.com> wrote in message news:<414dabf0$1_at_post.usenet.com>...
> **** Post for FREE via your newsreader at post.usenet.com ****
>
>
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote in message
> news:cijr2d$o32$1_at_hercules.btinternet.com...
> [...]
> > I would be very surprised if there were no differences
> > between the test and production systems that made
> > the hints from imported outline illegal on arrival.
>
> Of course there must be a difference! But in this case there was no evident
> difference (at least for me):
> - no difference in documented and un-documented parameters
> - same objects (same names as well)
> - and, of course, same database version
>
> The only difference I could recognize was that the two databases had a
> different "live" and slightly different statistics (unfortunately I was not
> able to export/import the statistics to find out if this was the problem).
> For example they were patched differently (I don't remember exactly, but
> e.g. prod: 9.2.0.1.0 -> 9.2.0.4.0, test: 9.2.0.1.0 -> 9.2.0.3.0 ->
> 9.2.0.4.0) and that on test the objects where dropped/created in a different
> order and/or more times in test (AFAIK the CBO doesn't use the timestamp of
> the objects like the RBO does to make a choice in case of equivalency, I'm
> wrong?). Therefore I concluded that some non-evident difference in the data
> dictionary lead to a non-used outline.
>
>
> Chris
>
>
>

Hi Jonathan,

Following your note, I started fresh and this time I modified the outline with the Outline Editor. The end result was the same, the outline was used and the wrong execution plan was picked. I verified the fact that it has been used in user_outlines, where the used column changed to "USED" and in v$sql the OUTLINE_CATEGORY column contained the category in which I created the outline.

Thanks,
Arik Received on Mon Sep 20 2004 - 15:55:39 CDT