Re: Optimizer question after upgrade

From: Greg Rahn <greg_at_structureddata.org>
Date: Thu, 19 Mar 2009 17:48:34 -0700
Message-ID: <a9c093440903191748w281687b1j18bbc763813b8ec7_at_mail.gmail.com>

On Thu, Mar 19, 2009 at 5:05 PM, Jack van Zanen <jack_at_vanzanen.com> wrote:
> We have just upgraded our development environment from 9.2 to 10.2
>
> Can someone can help us identify why 10.2.0.4 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 :
>
> INSERT INTO ...
> SELECT /*+ ALL_ROWS */ ...
> WHERE 1=1
>  AND ET.INSTALL_ID BETWEEN :B2
>    AND :B1 AND EH.DEVICE_NO = ET.DEVICE_NO AND EH.VALID_TO = ET.VALID_TO
>  AND EQ.EQUIPMENT_NO = EH.EQUIPMENT_NO
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:
http://www.oracle.com/technology/products/bi/db/10g/pdf/twp_bidw_optimizer_10gr2_0208.pdf

-- 
Regards,
Greg Rahn
http://structureddata.org
--
http://www.freelists.org/webpage/oracle-l

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