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.)