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

Paul,

Did you ANALYZE your table(s), it really help to improve performance when you set to CBO, on top of SQL statement tuning, it provides statistics information to the CBO optimizer.

    ANALYZE TABLE TABLE_NAME COMPUTE STATISTICS; But, make sure you rebuild your table's indexes before you analyze it, otherwise the execution can go very bad (speaking from my experience), if later on you find out the performance is getting worst, you can remove the statistics information by command:

    ANALYZE TABLE TABLE_NAME DELETE STATISTICS; Regards,
Vance

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.
Received on Fri Nov 16 2001 - 18:55:36 CST