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

Did you calculate statistics?

I'm also dubious about suddenly "switching on the CBO". If the code has been optimised for rule-based, that's just about the worst thing you can do. As a classic example, try calculating statistics for the data dictionary tables, and watch a perfectly healthy database grind to a screeching halt.

Regards
HJR

--
Resources for Oracle: http://www.hjrdba.com
===============================


"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.

>