Re: CBO calculates lower cost, but runs slower...

Paul Moore <paul.moore_at_atosorigin.com> wrote in message news:<aacavt8v1vsri2at0t01q5g770s9kv981e_at_4ax.com>...
> I'm having touble with the cost-based optimizer. And as usual, Oracle
> support are about as much help as a chocolate teapot :-(
>
> I have a database where we recently switched on the CBO, and a large
> proportion of our (adhoc) queries started going slower.
>
> On investigation, it turned out that (to take a particular query as an
> example) the CBO was picking a plan with a cost of X, and was running in
> about 20 minutes. When I hinted the query, saying FIRST_ROWS (not what I
> wanted to achieve, but it demonstrated the principle), the calculated
> cost for the best plan went up to 2*X, but the executaion time to get
> all of the rows went *down* to under 20 *seconds*!!!
>
> Clearly, the CBO's costing calculation is failing (drastically!) to
> reflect reality. But I am at a loss to understand why.
>
> I have done a number of things, with little effect.
>
> - I changed the DB_FILE_MULTIBLOCK_READ_COUNT parameter, which
> was set completely wrongly (it was far too high, resulting in
> too high a weighting for full table scans) to a sensible value.
> No significant effect on the problem.
> - I modified OPTIMIZER_INDEX_CACHING and OPTIMIZER_INDEX_COST_ADJ,
> based on an article I found on the web. Again, this changes some
> things, but not enough to make a significant difference.
>
> As I understand it, the CBO's calculations are based on logical read
> counts, translated into estimated physical reads using the above
> parameters. So a possible cause for the slow times would be if the
> Oracle process was actually CPU-bound - is that right? But I see no sign
> of CPU contention, or of low memory.
>
> I'm completely at a loss as to why the CBO is so ineffective here - can
> anybody help? (Even just by suggesting things I should be looking at - I
> don't know where to go next).
>
> This is an 8.1.6 system running on HP-UX 11.0, if it makes a difference.
>
> Thanks in advance for any help,
> Paul Moore.

1. you say you just switched to the CBO -- did you run statistics for your tables/indexes?
2. did you just estimate statistics (or compute them)? If you estimated them did you choose a high enough sampling of the data (number, percentage of rows)?
3. you didn't post the explain plans or anything about the query itself, perhaps you need to go further and create histograms for some of you columns so the optimizer has more to work with
4. upgrade to a more current (and supported) release -- CBO improvements are made all the time
5. please take it easy on support, i know it can be frustrating sometimes, but there are so many variables that go into query performance (including platform, release levels, patches, etc.) that it is hard for them to help you without asking a lot of sometimes obvious questions. Also, imho, they are not there to teach people how to use oracle (that's what books, classes and peers are for), they can be most helpful when systems crash and alert logs are filling with various ORA-600 messages.