CBO calculates lower cost, but runs slower...

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. Received on Fri Nov 16 2001 - 09:42:08 CST