Revert to old plan in Oracle

A weekly, scheduled, schema wide stats update was run on our Oracle 11R2 RAC cluster on Sunday. One of the queries had no apparent data or code change but now runs at 3 hours instead of 20 minutes. Looking at dba_hist_sqlstat , its running the same SQL as before, but with a different plan. The stats seem to have negatively affected it. Because its a large query that touches a few dozen tables, they already decided that restoring the stats on the affected tables isn't an option.

I've got the old (better) and new (terrible) plans by executing:

select * from TABLE(DBMS_XPLAN.DISPLAY_AWR('sqlid'));

But they're so different that its not a minor manual tweak to get them back.

Is there a way to force it back to the old plan, given that the database still has much of that information available to it?
(And we don't have the Tuning Pack.)

You need to use SQL baselines to force the execution plan.

This blog describes the steps involved. You'll need the sql_id for the statement and the plan_hash_value of the old plan.

StackExchange Database Administrators Q#22051, answer score: 3