Re: RBO to CBO

One particular detail to watch out for is the value of db_file_multiblock_read_count.

RBO uses this only at execution time, which often makes DBAs set it as high as possible.

CBO uses it (with some adjustment) for calculating the cost of a tablescan - on the assumption that a single block read will take exactly the same time as a multi-block read. An unsuitable value will throw the CBO into doing excessive number of tablescans.

As Daniel Morgan hints, fiddling with optimizer_index_cost_adjust is a way of having a high db_file_multiblock_read_count and avoiding excessive tablescans. But see my article "Oh I see a problem" on www.dbazine.com for a word of caution.

Apart from that, there are likely to be a number of cases where a whole new feature of Oracle makes you wonder what on earth the optimizer is up to.

You may also be able to drop a few indexes as the CBO may decide (correctly) that they are a complete waste of space.

Be aware that a FEW columns may need histograms.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk/faq/ind_faq.html
The Co-operative Oracle Users' FAQ

http://www.jlcomp.demon.co.uk/seminar.html
Public Appearances - schedule updated Dec 23rd 2004






"Oradba Linux" <techiey2k3_at_comcast.net> wrote in message 
news:JwKAd.304006$HA.119900_at_attbi_s01...


> Any major/minor issues to watch out for during the conversion especially

> with regards to sql written specifically to RBO

>

>