Re: 7.3 Optimizer diffs from 7.2

In article
<19971114012801.UAA00980_at_ladder02.news.aol.com>,   crazylongs_at_aol.com (Crazylongs) wrote:
>
> We just upgraded to 7.3 from 7.2. Complex queries
run the same or even
faster
> than with 7.2. However, primary keys/indexes
 seem to be ignored, and do ing
> FT scans. We either have to force the rule based
optimizer in init.ora
(which
> makes complex queries run slow) or force it by
deleting the statistics
before
> loading (which does the primary key lookups) and
 then re-estimating after.
> What did Oracle do to the optimizer to have this
effect, has anyone else
seen
> it, and does anyone have any other suggestions?
>
> Note: The first thing we did was compute
 (estimate) statistics for every
> object (using 30% of rows), thinking they should
 be re-estimated for the new
> optimizer, but this had little or no effect.

I too, have seen different access paths chosen by the cost based optimizer
between 7.2 and 7.3. I haven't looked into whether this involves the primary
key of a table or not.

One occurence I do recall is where we have a hint in a statement to use a
particular index in one table in the join of three tables. The hint improved
the response time by convincing oracle to use a particular access path. When
we moved the same database to 7.3, oracle used a different access path, and the
statement ran longer. We did compute statistics on both databases too. I
don't know the reason for the difference.

Another problem we encountered was when we estimated statistics using the
default # of rows (1064 I believe) on the 7.2 database. We discovered the hard
way that the statistics generated this way can really throw off how the
optimizer chooses its access paths. Once we ran a compute statistics, the
entire system seemed to run much better. I don't know if looking at 30% of the
rows might cause the same problem, but I assume that it might if some of your
data is very skewed.

John Reimer
Database Administrator
Worthington Steel

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