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

Jonathan Lewis wrote:
>
> The two main features are that Oracle does not
> distinguish between a buffer get and a disk read
> in terms of cost, and does not distinguish between
> a single block disk read and a multiblock disk read
> in terms of cost.
>
> Consequently the CBO tends to over-price paths
> based on indexed access paths, even when the
> DBA can see that most of the data is buffered,
> and therefore more of the nominal disk reads
> will actually be buffered and therefore a couple
> of hundred times quicker than Oracle allows
> them to be.

And for precisely this reason, SQL tuning purely from cost data is futile. The best way to tune a query is to minimize logical reads. People need to drop this obsession with the meaning of "cost" and Oracle's internal algorithms.

Oracle calculates cost so it can come up with the best reasonable guess as to an optimal plan. When that plan is sub-optimal, particularly for a key query, the developer or DBA needs to intervene. Rather than try to reverse-engineer Oracle's logic - which will change in twenty seconds anyway - hunt for a plan that minimizes logical reads and forget the other stuff.

Logical reads are physical reads plus buffer gets.

• ricky