Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question

3 Answers 3

up vote 2 down vote accepted

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.

share|improve this answer
    
Is there any way to do it without the tuning pack? –  rfusca Aug 6 '12 at 17:58
    
SQL baselines don't require the Tuning Pack, only Enterprise Edition. SQL Tuning Advisor does require the Tuning Pack. See oracle.com/technetwork/database/focus-areas/bi-datawarehousing/… –  Leigh Riffel Aug 6 '12 at 19:53
    
@Leigh - baselines don't require it. But pulling a plan history from the AWR into a baseline seems like it requires an intermediate step of putting it in sql tuning set. –  rfusca Aug 6 '12 at 21:38

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.

share|improve this answer
    
We're on EE 11g r2, so baselines are an option - but not after the fact. –  rfusca Aug 6 '12 at 21:36
    
You can use Outlines after the fact, but you do have to do the work of determining how to hint the optimizer into the old plan. If you really meant to say baselines, then the comment does not apply to this post. –  Leigh Riffel Aug 7 '12 at 13:01

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.

share|improve this answer
    
We don't have the tuning pack. –  rfusca Aug 6 '12 at 17:40
    
In that case study the behaviour of the tables. BTW: since you do use dba_hist_sqlstat: check your DBA_FEATURE_USAGE_STATISTICS view. I might be mistaken but using the AWR views does require a performance tuning pack or diagnostics pack license. Study the behaviour of your tables. Do they have a grow/shrink effect (I call it ping/pong) ? –  ik_zelf Aug 6 '12 at 18:58
    
Thanks. I was told we do have the license for AWR, but not tuning. None the the tables 'ping pong'. –  rfusca Aug 6 '12 at 19:11
    
I would restore the stats. –  ik_zelf Aug 6 '12 at 19:49

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.