Re: RULE hint better sometimes after 10.2.0.3 upgrade
- From: "조동욱" <ukja.dion@xxxxxxxxx>
- To: John.Fedock@xxxxxxxxxxxx
- Date: Wed, 9 Apr 2008 10:49:02 +0900
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@xxxxxxxxxxxx>:
>
> 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
>
>
>
Other related posts:
