Re: 7.3 Optimizer diffs from 7.2

In article <347159C4.14F_at_bhp.com.au>,
Connor McDonald <mcdonald.connor.cs_at_bhp.com.au> wrote:
>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.
>
>One of the new thingies in 7.3 is HASH JOINS which basically introduce
>another "scanning" candidate as opposed to index access.
>
>Any explain plan output will list if HASH JOINS are being used - they
>are good in some cases - not so great in others... (It can be disabled
>at SQL or instance level)

I had one case where I went over and over the plan, and tried all sorts of stuff. Turned out that a hint was at fault, although not according to the explain plan. Removing the (entirely appropriate) hint made the (critical) query go from minutes to sub-second, without changing the plan. Unfortunately, I don't have the exact situation anymore, as we ascribed it to the mysteries of the ages, as so many other optimizer problems. Hazy memory says something about a unique descending sort, table access by rowid, index range scan.

Simply removing the hint fixed the problem, although gave different results (because of the rowid range). This was acceptable to the developer, and we went on with our lives. But is certainly hasn't given me warm fuzzies about the optimizer. Solaris 2.5, Oracle 7.3.2.1.

jg

-- 
These opinions are my own and not necessarily those of Information Quest
jgarry@eiq.com                           http://www.informationquest.com
http://ourworld.compuserve.com/homepages/joel_garry
"See your DBA?"  I AM the @#%*& DBA!