Revert to old plan in Oracle

Question

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

Answer 1

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.

Answer 2

If you have a tuning pack license - really recommended - you can use the dbms_sqltune to get a better plan and create a profile for it. If an sql_id has a profile for it, the database will use that, if it is enabled. It also works perfectly together with SQL Plan Stability; when you work with SQL Plan Baselines, a baseline is auto created and accepted for a profile's plan.

On the other hand, it does look like you statistics do have a problem. Most likely there is a problem with tables that have a kind of a temporary content, that is emptied and filled regularly. If the gather routine runs on a 'wrong' moment, it gives - for example - 0 rows for such a table because it was just emptied on the gather moment and filled with any number of rows > 1 to cause havoc on your performance. With 0 or 1 rows in it, a scan or even a Cartesian product might be chosen, that work real bad in a join there appear to be more data in it than what the stats try to tell.

Easiest is to just restore the statistics for the participating tables.

Answer 3

If you are on Standard Edition, look into Stored Outlines. The feature is deprecated on 11g, but is the only plan stability option available with that edition. Here is a good example.