You misunderstood me. Tuning everything is, of course, important. I meant that in the long list of variables that affect performance, it is the lowest. That is because it is relatively fast. Look again at the scenario I presented. It was not presented as black-and-white. *Everything* is a matter of give and take. All things considered, re-compilation is one of the smallest times your queries will spend. I/O is *much* more important. Therefore, if you have to decide, as I tried to show, between a query that always tablescans, or one that sometimes can use an index if it has to recompile, choose the recompile option. Re-compiling is a *very* small amount of time in comparison to most database operations.

Now, no matter how you create the original statement, the statement should still be in the SGA and the plan fixed when you have to refresh the user. Therefore, it does not matter how you create the statement the first time. You can use dynamic SQL and it will still find the query the second time and still use the same, stored plan.

Given these two facts, I really don't see why you are so hung up on 'locking down' the plans. What brought you to this opinion that you had to do this? I still stick to my original example. You only have so many choices and if you decide to force a plan, you have to decide which plan, and using *either* plan *all* the time has drawbacks. Check my example again.

CURSOR_SHARING - Yeah, I thought I'd read this before. It just doesn't seem like a good idea to me, but I've never implemented it. perhaps others have differing opinions. You may want to start a different thread on that question, since it is unlikely that anyone other than us is reading this thread anymore :). Note that it is no different than using bind variables anyway. Therefore, it is a better idea to use binds where you know the data is evenly distributed and constants where it is skewed. Using CURSOR_SHARED=FORCED simply removes your second option.

Histograms - As I'm light on the admin stuff, I don't know this one. Sounds unfortunate if true, but certainly not insurmountable. And a little extra admin work is worth it if it saves you from your performance nightmare, no? I know you can export and import the regular stats by themselves. Check out this link to see if the histograms go along for the ride or not:
[url]http://technet.oracle.com/doc/server.815/a68001/dbms_sta.htm#1008494[/url]


Still don't understand the last issue - I'm being thick :). Regardless. No matter what you do in development to come up with 'better' plans, you are still trying to 'fix' these plans in place. Why? Again, refer to my example. You are hoping that there is some magic plan that will be best for all of your data. But when your data is severely skewed, that just won't happen. Look again at my example. You will either be using the index or not - for *every* value, if you 'fix' the plans. Neither choice is perfect *for all data sets*. This is where the CBO shines. And again, when you search on Y values, a tablescan will be wayyyy longer than recompiling the statement so you can use the index.

I hope I've made myself a little clearer this time. Mind you, if all your data was evenly distributed and your stats were not changing much over time, I'd be all for your idea. Tune it all, come up with the best set of plans for everything and then fix them in place to eek out that last bit of performance. But with highly skewed data, one plan will not fit all. Again, if you don't care about the smaller sets, then fix the plans for the larger sets, if you want. As long as you understand the consequences.

- Chris