Re: How to tune this sql (RULE-Based) ?

In message <DQ2m9.43500$g9.124732_at_newsfeeds.bigpond.com>, Howard J. Rogers writes
>"April"wrote in message
>news:54df0379.0209301250.8363cb9_at_posting.google.com...

>> This view takes about 18 seconds to run when rule-based and 3 seconds
>> when cost-based on Oracle 7.3.
>>
>> So you say, why don't you just use cost-based...
>> well most of the other queries in the database were set up for
>> rule-based and don't work so well when table statistics exist.

Agreed - a system written and heavily tuned for RBO, and especially if you are still running 7.3 is likely to take a big hit if you switch it over to CBO.

At a guess this is an old but stable package/legacy system which would cost more to upgrade than replace because of the retuning needed?

>>
>> So how would I go about making this query work faster in the
>> rule-based environment?

>Assuming you have statistics calculated for the relevant table(s), then
>optimizer mode is capable of being set at the instance level, the session
>level AND at the statement level.
>
>You'll want that last one, which means sticking a hint in there.
>
>Something like SELECT /* +ALL_ROWS */ blah blah blah
>
>Or SELECT /* +FIRST_ROWS */ blah blah blah.
>
>Both those hints switch on the cost-based optimizer. One tends to favour
>full table scans, the other tends to favour index access.

Yes but I've seen products hit problems using 'first rows' as an indirect (and misguided in my examples) attempt to mimic RBO. A cynic might suggest that this is related to the previous version having been written on RBO and the new shiny code bearing a staggering resemblance to the old releases. Me, I wouldn't know.

>You'll probably
>want to experiment to see which one suits you best.

Looking at the query I'd be surprised if some of those tables were not involved in other RBO tuned queries. Stats gathered on tables in most environments will cause the systems to default to using CBO whatever you specify. That may help one query and screw up all the others. Considering it is running 7.3 (where the RBO still worked better anyway most of the time) and tuned to RBO there may be more mileage in working on the query itself under RBO.

I have shed many pints of blood trying to work around RBO tuned SQL on releases of Oracle from the first releases with CBO, up to and including 9i.

9i you ask? Yep, at least one major vendor of a major product plans to go to 9i on RBO before being dragged onto CBO in 9i 6months later.

>
>(By the way, the rule-based optimizer is dead in version 10. You probably
>knew that.)

RBO is dead, long live...

-- 

Karen                              Live Faust, die Jung.