Re: Query CBO Cost - Hints

"Anurag Varma" <avoracle_at_gmail.com> wrote:
> There is a debate I'm having on metalink about a case where OP had the
> following problem:
>
> Oracle 9.2.0.5, CBO used with very low value of
> optimizer_index_cost_adj (1):
>
> delete from table; shows a cost of 81 with a bitmap index being used.
>
> delete /*+ index(table btree_idx) */ from table; shows a cost of 1 with
> a
> b*tree index being used.
>
> Now I understand that the optimizer_index_cost_adj value is too low ..
> and that a full table scan might be better.
>
> However, I'm of the opinion that the CBO itself should have
> accounted for the b*tree index in its costs and since it appears to
> be of a lower cost, the b*tree index should have been used in the first
> instance (i.e. without hinting).

I would agree with that, the CBO *should* have picked up on it without a hint.

>
> To which a subsequent poster refers me to the following post on Tom's
> site:
>
> http://asktom.oracle.com/pls/ask/f?p=4950:8:5440244398791053756::NO::F495
> 0_P8_DISPLAYID,F4950_P8_CRITERIA:313416745628
>
> I agree to Tom's points there. However, I'm not sure if my deduction
> is flawed even after reading the thread.

I'm not sure I agree with Tom's point or not. Of course the cost can *usually* be used to compare different execution plans of the same query using the same goal stats. That is why it is computed. However, one generally only investigates cases in which the CBO is suspected of making a poor choice. Why is the CBO making a poor choice? Often, because it is coming up with inaccurate costs. So if you don't trust the CBO's choice, you probably shouldn't trust its costs in that specific case, either.

>
> * Does the CBO show lower numbers for the cost if I hint the query ..
> vs
> the same access plan non-hinted?

I think that only happens if the hint is one of the ones that changes the statistics used by the CBO. Access hints (Join order, index, full, etc.) should not change the costs if they don't change the plan.

> Internally the CBO might be lowering the cost to respect the hint.

I've never seen evidence of this for access hints. It respects the hint by not considering access paths that are incompatible with the hint. (But I've never played with 10g)

> However,
> should the OP have seen a lower cost after the index hint?

I don't know. Did it actually run faster with the index hint? :)

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB