Re: Optimizer question after upgrade

From: Greg Rahn <>
Date: Thu, 19 Mar 2009 17:48:34 -0700
Message-ID: <>

On Thu, Mar 19, 2009 at 5:05 PM, Jack van Zanen <> wrote:
> We have just upgraded our development environment from 9.2 to 10.2
> Can someone can help us identify why optimizer taking double the
> time to execute the same statement for exact same number of rows ( 3803685 )
> We can see, it is picking up a different plan, NL is 9.2 against HJ in 10.2.
> We ran statistics the same way we ran them in Oracle9

What is your exact dbms_stats command? Do you recognize that some defaults have changed in10g, so the "same way" may not really be the "same way"?

> !! We already had a look at note 295819.1  which mentions the two parameters
> "_optimizer_cost_based_transformation" and "_gby_hash_aggregation_enabled"

I would hold off mucking with parameters at this point. That should be a last resort and only under the recommendation of Oracle Support.

> This SQL is part of a long running batch job which ran about 20 minutes
> faster than on 9i, but this step doubled in time so we could potentially
> gain some more.
> SQL Statement :
> SELECT /*+ ALL_ROWS */ ...
> WHERE 1=1
I see 2 potential issues with this SQL.
- It has a hint. I would recommend that you remove it see what the optimizer plan is
- It uses bind variables. You may be mixing bind variables and bind peeking with histograms. That can be a nasty cocktail, if you know what I mean. I would see if CCS.CCS_EASTL.DEVICE_NO has a histogram on it. You may not want one if it does.

This might be a useful read as well:

Greg Rahn
Received on Thu Mar 19 2009 - 19:48:34 CDT

Original text of this message