Re: CBO doesn't work!

Another shoot : in your queries you give to the optimizer all the possible ways (for example : if you have in the FROM clause tables A, B and C, in WHERE you wrote :

  WHERE A.cond = B.cond
    AND B.cond = C.cond
    AND C.cond = A.cond

It´s redundant, but this way you know the optimizer WILL consider all the possible paths .
 Other points : and about your stats ? You ESTIMATE it or COMPUTE full ? In what frequency ?

 Try to compare an EXPLAIN PLAN generated by RBO versus an EXPLAIN generated in RBO mode.

[]s

     Chiappa

In article <93i1ff$v2i$1_at_nnrp1.deja.com>,   zpayne_at_my-deja.com wrote:
> I appreciate your help in giving me a starting point.
> I did look at the parameters you suggested, but they are actually set
> quite low.
> db_file_multiblock_read_count =8
> sort_area_size = 65536
> hash_area_size = 131072
>
> We have generated histograms, and the values that are passed in for
> am_vend_id (304,2876) are parameters passed in by the application, and
> the RBO seems to outperform the CBO for any values passed in. So it
> seems that I am still stumped.
> Thanks for the help.
>
> zp
>
> In article <979085948.15666.1.nnrp-12.9e984b29_at_news.demon.co.uk>,
> "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> >
> > Look in particular at your values in
> > the init.ora file for:
> >
> > db_file_multiblock_read_count
> > sort_area_size
> > hash_area_size
> >
> > You may have persuaded the CBO that it
> > is very cheap to scan and sort large tables
> > then hash the results.
> >
> > Also (for this case) are 304,2876 values for
> > AM_VEND_ID which have a particularly small
> > set of results compared to other values for
> > AM_VEND_ID. If so are you generating column
> > histograms to let Oracle know about special
> > cases ?
> >
>
> Sent via Deja.com
> http://www.deja.com/
>

Sent via Deja.com
http://www.deja.com/ Received on Wed Jan 10 2001 - 11:03:38 CST