Re: CBO doesn't work!

I should have asked about block size too. If the block size is large - e.g. 16K - then the 8 block multi read may be encouraging Oracle to go for tablescans. Since the sort/hash values are then (relatively) small, the resulting sort/merge and hash joins could be taking excessive time due to I/O. (But it shouldn't really be like that).

You say the am_vend_id appear as parameters - does this mean bind variables ? In this case, the histogram data is ignored by the CBO, so this may be contributing to the problem.

Clearly there is a good path that Oracle should be able to find, since the rule based path is quicker. One 'quick and dirty' is to set the optimizer_mode (or hint) to FIRST_ROW, as this encourages Oracle to use indexes. But this is not a 'proper' solution, and may be ignored anyway because of the 'distinct'.

--
Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:  Addison-Wesley
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i
More reviews at: http://www.jlcomp.demon.co.uk/book_rev.html



zpayne_at_my-deja.com wrote in message <93i1ff$v2i$1_at_nnrp1.deja.com>...


>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

>