Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

tell oracle which plan is optimal?

user447327Jan 19 2009 — edited Jul 21 2011
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.
This post has been answered by Randolf Geist on Mar 26 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Aug 18 2011
Added on Jan 19 2009
11 comments
4,694 views