Re: Stored Outlines.

> Does anybody out there use stored outlines? If so what benefit do they add,
> when would you use one, why would you use one, and what would you use it
> for?

In Oracle's own words (from Chapter 10 of the Tuning manual):

"If your application was developed using the rule-based optimizer, then a considerable amount of effort may have gone into manually tuning the SQL statements to optimize performance. You can use plan stability to leverage the effort that has already gone into performance tuning by preserving the behavior of the application when upgrading from rule-based to cost-based optimization.

By creating outlines for an application before switching to cost-based optimization, the plans generated by the rule-based optimizer can be used, while statements generated by newly written applications developed after the switch use cost-based plans."

Never used "plan stability" features myself, but I understand, from a lecture I attended, that another motivation was to satisfy vendors who deliver oracle-databased products. Vendors often go to some lengths to optimize their SQL statements, perhaps based on some volume of data, optimizer hints, whatever; then when their products get installed at customer sites, the performance of their carefully tuned systems was going all to pot, because oracle's optimizer thought it knew best. Well, it may know best in some cases, but it was driving the vendors nuts the rest of the time, and they wanted a way to "freeze" the execution plans.

Cheers
Bill