Re: Cost Based Optimizier

Richard Hu wrote:
>
> We have a 7.1.3.2.1 production system and have just ANALYZED all the
> tables and indexes.
> Since we have done this we are suffering with response times from queries
> that join two and more tables.
> When EXPAINed/TKPROFed we have discovered that some selects are
> performing full tables scans.
> It appears that the execution plans worked out by the optimizer are not
> the best.
> (I have checked that all the tables and indexes are analyzed -
> the statistics were ESTIMATEd on all but one table which was be
> COMPUTEd.)
> To try and solve this we have put in HINTS but to one avail.
>
> Is there anyone out there who has knowledge of the COST based
> optimizer or who could offer some advise ?
>
> Has anyone had similar problems ? and may be gone back to using the
> RULE based optimizer ?
>
> If your hints do not work, how do you know COST BASED isnt right? What do you
> have to comapre results with? Cost based optimizer disables indexes when
> it senses the query will return > 5% of the total# rows of a
> table...it does this
> correctly since index select benefits actually erode beyond the 5%
> retrieve mark over
> full table scan due to more drive head movements..
>
> Midas well learn the intricasies of cost-based now...in the future,
> rules-based will
> go away all-together.
>
> Ric
> Spectrum Group

In principle the Cost based optimizer is a ood idea and is always worth a try (given how easy it is to disable it, ie. analyze table ... delete statistics;). However, like all tuning features one needs to evaluate it's use on a case by case basis.

1. I have never seen any benifit to using the computed option.
2. Try increasing the % analyzed to 30%
3. Later versions of the database have much more efficient algorithims
4. If all else fails delete your statistics and go back to Rule until you upgrade the RDBMS. This is why they provide both options.