Re: CBO performance between 7.1.4 and 7.3.2.1

fuocor_at_novachem.com wrote:

>I just installed oracle ver 7.3.2.1 on a dec unix box. 7.1.4 already
>resides on the same box. I created an identical database for each
>version from one of our production systems. Both databases has stats for
>all tables with choose as the optimizer mode. I took three sql
>statements from the sql cache on our prod system and ran them through the
>explain and tkprof on the two versions of oracle. The 7.1.4 database
>performed approx 50 % better than the 7.3.2.1. The explain showed that
>the 7.1.4 databased used different indexes than the 7.3.2.1.
 

>I was always told that CBO does not work properly in any release prior to
>7.3. Has anyone else noticed a performance loss on there applications
>after migrating to 7.3 from 7.1? In my situation 7.1.4 is obviously
>making better decisions on an execution path than 7.3.2
 

>note: both databases have the same init parameters and is using the same
>hardware and both have new stats generated
 

>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

But what about the gathered statistics? The Cost Based Optimizer is supposed to look at statistics gathered by the 'ANALYZE' command. If you haven't updated the stats in a while, then the Optimizer may choose the wrong indexes.

I have a batch job run every night to perform an 'ANALYZE' with an ESTIMATE based on 20 percent.

• Doug Anderson Oracle Database Administrator DAtheDBA_at_mindspring.com