Re: Optimizer

In article <8fps7f$84e$1_at_nnrp1.deja.com>, blair_kennedy_at_mydeja. com wrote:
>In article <958422110.13305.0.pluto.d4ee154e_at_news.demon.nl>,
> "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
>> I don't agree with this approach. It is quite simple to
 analyze your
 tables
>> every week by means by dbms_job.
>
>It has been my experience that you have to be careful when
 analyzing
>tables. If you don't analyze tables when they have volumes
 similar to
>the volumes during the queries on that table, Oracle may
 perform worse
>than if you did analyze at all. So if you have tables that
 fluctuate
>wildly and your DBA don't know the proper table sizes during
 production
>runs it may
>be better to guarantee the what path a query will take.
>
>> The rule based optimizer will be taken out soon, with the
 next major
>> release, and I promise you, you are going to have fun, when
 all your
 queries
>> have awful performance.
>> It's better to prepare now, than to sit back and wait for
 major
 disaster
>> occurring.
>
>Oracle has been threatening to remove the rule based optimizer
 for
>several releases. I imagine it will be around for several
 more. Once
>it is gone queries with the rule hint will just use the cost
>optimizer. I hear oracle is rewriting the cost optimizer for
 the next
>release. If it turns out to be a dog I can't see them getting
 rid of
>the rule based optimizer.
>
>> Also, the cost based optimizer is much less dependent on
 the 'correct'
>> sequence of tables and other expressions.
>> Sticking to the rule-based optimizer is definitely
 undesirable.
>
>Yes this is true, but the cost based optimizer get confused
 too. You
>will often different explain plans when you change the order of
>tables. Basically you will have to tune all poor performing
queries

Yes this is true, i had problems afer renaming a table with cost based ( FULL-TABLE-SCANS ). CBO 'dont like' or's in statements because he likes to do a 'FULL-TABLE-SCAN'. Also CBO had problems using bitmapped indexes, i had to help him wsith the INDEX_COMBINE -Hint. In somme batchprocess we developed deleting the statistics helped improving performance twice. So the world ( ORACLE-WORLD ) is not true or false it's still 'fuzzy'

>(whether they are cost or rule). My personal preference is to
 get an
>explain plan using a rule hint just so I can see which indexes
 can be
>used. I then rewrite the query using index, use_nl and other
 hints.
>
>>
>> Regards,
>>
>> Sybrand Bakker, Oracle DBA
>>
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
>
>

Bye
/Karl

Karl Reitschuster
Senior Consultant CSC Ploenzke AG
Oracle Databases, Implementation, Performance-Tuning <!Jesus is Lord!>
* Sent from RemarQ http://www.remarq.com The Internet's Discussion Network * The fastest and easiest way to search and participate in Usenet - Free! Received on Fri May 19 2000 - 00:00:00 CDT