Re: RULE hint better sometimes after 10.2.0.3 upgrade

As you are already well aware of, query optimization consists of 2 major steps.

• transformation step: transform query to more optimized form (10g introduced cost based transformation)
• optimization step: calculate cost of each access pattern and find optimal execution plan based on system statistics and segment statistics

Both two steps can make plan distortion. The best way to identify this is that comparing the result of 10053 trace as suggested above.
Under assumption of statistics not being changed, I suspect that 10.2.0.3introduced enhanced query transformation feature and that make abnormal plan
distortion.
Yes, this is just a guesswork. You should find it out yourself.

2008/4/9, Fedock, John (KAM.RIC) <John.Fedock_at_us.kline.com>:
>
> We just upgraded our HP-UX 2 node RAC database from 10.1.0.5 to 10.2.0.3.
>
>
>
> So far, I have come across 2 queries where performance died. On both
> queries, running it with the RULE hint brought performance back. There were
> no hints on the queries before. Also, on one of those queries, deleting all
> stats on the object greatly improved performance (but on the other it made
> no difference).
>
>
>
> So, did the optimizer change that much between 10.1.0.5 and 10.2.0.3?
>
>
>
> I know the RULE isn't supported in 10g, but it can be used.
>
>
>
> Am I missing anything obvious?
>
>
>
> Also, on this upgrade the JVM crapped out, although the status and all was
> valid. After reinstalling it, all was fine.
>
>
>
>
>
>
>
>
>
>
>
>
>
> *John Fedock*
>
> "K" Line America, ISD Department
>
>
>

--
http://www.freelists.org/webpage/oracle-l