Flushing Bad Plan - No Longer in Shared Pool

We have an environment that has been experiencing some wandering plans. While we'd like to look into the details to see why the optimizer is choosing bad plans, we have immediate needs to get statements running with good plans that have been used in the past. When we're contacted in time, we have been stopping the query, flushing the plan from the shared pool (using DBMS_SHARED_POOL.PURGE), gathering stats (where applicable) and re-running. The problem is that sometimes we're not contacted until the statement has been running for so long that it's now showing up in dba_hist_sql_plan, meaning the DBMS_SHARED_POOL.PURGE procedure will no longer flush it as an available plan for the optimizer.

We have tried setting baselines for these statements (although we'd prefer not to use them long term), but the optimizer continues to see these bad plans as cheaper options that would be a better choice.

Is there a way to completely eliminate or invalidate a past plan from being considered by the optimizer again?

Thanks,
Scott
Notice of Confidentiality: **This E-mail and any of its attachments may contain Lincoln National Corporation proprietary information, which is privileged, confidential, or subject to copyright belonging to the Lincoln National Corporation family of companies. This E-mail is intended solely for the use of the individual or entity to which it is addressed. If you are not the intended recipient of this E-mail, you are hereby notified that any dissemination, distribution, copying, or action taken in relation to the contents of and attachments to this E-mail is strictly prohibited and may be unlawful. If you have received this E-mail in error, please notify the sender immediately and permanently delete the original and any copy of this E-mail and any printout. Thank You.**

--
http://www.freelists.org/webpage/oracle-l