Re: switch from rule to cost based optimizer

Hi Thorsten,

I'll start the ball rolling ...

Point 1 (and perhaps the most important). Ensure the statistics on all the objects in the database are current and accurate (enough). You don't mention database version (naughty) so I'll just mention the DBMS_STATS package and it's various procedures and as probably the way to go. Make sure you refresh the stats frequently enough to keep them current.

Point 2. If you have any columns which are commonly referenced in WHERE conditions, which are indexed and which have a uneven distribution of data, then you need to create histograms on these columns else the CBO could make errors. Note though that the use of bind variables will negate the use of histograms which might/might not cause issues.

Point 3. There are various flavours of CBO (all_rows, first_rows, first_rows_n) so consider which is most appropriate.

Point 4. Once you make the swap, some execution plans will remain unchanged, some may change for the better, some may change for the worse. You need to carefully monitor the performance of your applications and for any that now appear sluggish (or just downright slow), you need to suz out their execution plans and determine what the CBO is doing. Complex queries especially may need a little hint or two but don't forget the factors mentioned above.

Point 5. The CBO can take advantage of lots of nice features (bitmap indexes, partitioning, MV, etc.) so investigate if any of them could be used for the betterment of your applications.

Point 6. Check out the Performance Tuning manual which discusses the CBO and related issues.

Point 7. Good Luck ;)

Richard

"Thorsten M. Wahl" <twahl3_at_compuserve.de> wrote in message news:anrvn3$1s3$1_at_nntp-m01.news.aol.com...
> Hello.
>
> I have to switch a database which still uses the rule based optimizer for
> its sql queries to the (new) cost based optimizer.
>
> Is there any information on this topic available?
>
> What do I have to take into account if I try this.
>
> TIA
>
> --
> Thorsten M. Wahl
> Douglas Informatik & Service GmbH
> Germany
>
>
Received on Mon Oct 07 2002 - 09:30:13 CDT