tell oracle which plan is optimal?
It's easy enough in 10g to get a query's history of execution plans and execution statistics, and so it's often easy to tell the bad plan from the good plan especially when user complaints coincide with the plan that uses many more gets per execution. I can see the sql_id, I can see the plan_hash_value for the efficient plan, why can't I just run a function that says "associate this plan with this sql_id always until I tell you otherwise?" Yes, I know there might be more than one optimal plan for some queries, but I have seen plenty of cases where there's one obviously better plan for all inputs - when plan B is used there are no complaints of slowness, when plan C is used users call the help desk. All I need is to get oracle to understand that sql_id A works best with plan B.
Maybe something like:
dbms_stats.create_profile_from_existing_plan(sql_id=> x, plan_hash_value => y );
would get the details from dba_hist*.
This can't be that difficult to implement, can it? Why isn't it provided/supported? How hard would it be to implement using calls to oracle functions?
Thanks.