Re: Cost Based Optimizer - Chooses slowest plan?

Hi everybody;

I apologize if someone should feel misunderstood, BUT:

Recommending the RULE hint can be very dangerous as far as performance is concerned. Rule based optimization always deducts the driving table form the physical sequence of table names in the from clause. When your number of rows are large enough you will experience excessive performance degradation.

In Rule based optimization the last table in the FROM clause is the driving table. It should always be the table with the smallest number of rows or with the smallest and best indexed result set.

And in cost based it is normally just the other way around!

Generally spoken, if your SQL code is "good" code for cost based optimization and you deal with significant table sizes just applying the RULE hint will probably wreck your performance.

And the INDEX hint isn't always honored either. In a large Sql performance tuning project I was involved in we had execution plans where the CBO ignored that hint based on the % of rows to be retrieved. I am not certain here, but it appeared to be based on % of rows to be retrieved and on the absolute number of rows. The larger the table the smaller the % from which CBO went to Full Table Scan... This was 7.3.4

Regards
Eike

Mark Membrino wrote:

> Sometimes the cost based analyzer choses poorly. The plan with the lowest
> cost is not always the fastest. In these cases you can use the 'RULE' hint
> to override the cost based analyzer. For Example:
>
> select /* RULE */
> emp_no
> from emp
> where emp_no > 20;
>
> You can also force it to use an index using the index hint. The following
> example will force the use of an index from the company table.
>
> select /*+ INDEX(COMPANY) */
> name, city, state
> from company
> where city = 'Boston'
> and State = 'MA';
>
> CJ <djjr_at_ix.netcom.com> wrote in message
> news:37C1C28E.CC65764E_at_ix.netcom.com...
> > I'm running a 7.3.4 database in choose mode. At one point the optimizer
> > chose to use indexes for my sql which
> > I know executes faster than doing a full table scan. I've actully seen
> > the performance degredation when I run it
> > with out using the indexes. Now its chose not to use indexes. The cost
> > for the sql using the index is actually higher than the sql not using
> > the index
> > even though the sql using the index is faster. I thought the fastest
> > sql would also have the lowest cost? I've analyzed
> > the tables and indexes in question, so the statistics are there. Are
> > there problems with the optimizer in 7.3.4? Are there
> > additional parameters that need to be set for the optimizer to choose a
> > better(faster) plan?
> >
> > TIA
> >

Received on Wed Aug 25 1999 - 15:26:06 CDT