Re: risks of using cost based optimizer

joel-garry_at_home.com (Joel Garry) wrote in message news:<91884734.0211071556.581a36dd_at_posting.google.com>...
> bpathakis_at_yahoo.com (Brent) wrote in message news:<1736c3ae.0211070753.179812c5_at_posting.google.com>...
> > vbauzac_at_capgemini.fr (Vincent BAUZAC) wrote in message news:<85372317.0211060830.13d446ef_at_posting.google.com>...
> > > Hi,
> > >
> > > We have a production database (Oracle 8i) still using the 'RULE'
> > > optimizer mode.
> > > Since we have some performance troubles, we would like to switch it
> > > into the 'CHOOSE' optimizer mode and compute the statistics, every
> > > weeks for example.
> > >
> > > The Oracle documentation advises to use the statistics, but we have
> > > heard there is some risks in doing this; the performance of some
> > > request could be badly affected.
> > >
> > > Is it true ?? What kind of request could be badly affected ??
> > >
> > > Thanks for your help,
> > >
> > > Vincent
> >
> > If it was written and optimized for the rule based optimizer, then
> > switching to CBO could cause serious performance problems.
> >
> > What I would do (in a test environment or during downtime):
> >
> > * Make sure the optimizer mode is choose.
> > * Gather statistics on all tables / indexes.
> >
> >
> > I would also modify the settings (using alter session to test, and
> > change them if in your init.ora if they work):
> >
> > * optimizer_index_cost_adj - usually needs to between 10-30,
> > defaults to 100
> > * optimizer_index_caching - I would start with 50, and experiment.
> >
> >
> > And test your apps / queries to see how they perform. If they don't do
> > well
> > with the CBO, delete the statistics and it will go back to using rbo.
>
> Be forewarned that with CHOOSE, if any of your tables get analysed,
> that can make for some very bizarre performance "issues." This can
> happen if, for example, you export a table with stats, and then import
> it later. Or even if you just miss the stats when you delete them.
> Or...
>
> jg

Brent pointed it out that a system written for RBO could have problems and this is very true IF it was well written, but I had a vendor package written for RBO and it was performing very poorly. We were set to the default optimizer setting of choose so I analyzed the tables and performance improved greatly. Since we did not have access to the source it was an all or nothing deal meaning if even one important SQL ran badly under CBO we would have to back the change off, but we got lucky.

If you have access to the source you can hint any SQL that was running well under RBO that goes to lunch under CBO then you can hint it back to rule if necessary. It would be better to try to rearrange the SQL into CBO friendly structure rather rule structure (see below) and see what the CBO does but as a quick fix the RULE hint will work.

Under the CBO if you list your tables in desired join order the hint, ORDERED, can be used to make the CBO join the tables in that order (where possible) while under RBO when a choice between tables had to be made the right-most table was chosen. So there are techniques to how you code your SQL that do affect the plan thought the CBO has improved with every release and get fairly crafty sometimes.

Anyway, the CBO has worked pretty well for us. It does make mistakes and sometimes it needs help. Your best bet is to use dbms_stats to fully compute small tables and sample larger tables.

Static tables do not need to be analyzed on a regular basis while a rapidly growing table with a sequence as a key that has range scans performed on the key should be analyzed regularly least Oracle make very bad guesses about how much data it is seeking. Beware of analyzing report/work tables that have greatly varying numbers of rows when they have little to no data.

HTH -- Mark D Powell -- Received on Sun Nov 10 2002 - 04:37:33 CET