Re: Strange optimizer problem

I agree with Srivenu.

The plan has changed because the computed cardinality estimate will be much more attractive with the m.vi_k_nr like '45481812%' predicate. It has nothing to do with upgrading your instance.

I would like to add one more thought...

Because the selectivity of the slower query is much better now, the access plan may actually be correct. Your problem may be that your are running out of hash memory. (Check to make sure you have enough memory allocated to hash_area_size.)

Mark Simmons
Sr. Oracle DBA
Sabre-Holdings, Southlake, TX

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0403040105.68bf83b2_at_posting.google.com>...
> Gerold,
> The difference between the slow and the fast query is that the driving
> table has changed.
> This is because of the difference in cardinality estimates between the
> clauses,
> m.vi_k_nr like '45481812%' and m.vi_k_nr like '4%'.
> If you dont have column level statistics, the optimizer estimates the
> selectivity of like 'A%' (NDV/72) is more than that of like 'AB%'
> (NDV/9342).
> Likewise the selectivity of like 'AB%' is more than that of like
> 'ABC%' (NDV/19044).
> I have seen that the selectivity remains the same after 3 characters
> or more.
> i.e selectivity of like 'ABC%' is same as like 'ABCD%'
>
> Also if you dont have column level statistics, the clauses like 'A%'
> and like 'B%' are considered the same.
> So in your case the optimizer is changing the driving table due to the
> selectivity and hence cardinality changes for the table VIS_P501_MLT
> due to the clause m.vi_k_nr like '45481812%.
>
> Try getting column level statistics for the column vi_k_nr in the
> table VIS_P501_MLT and see if the plan changes.
> regards
> Srivenu
Received on Fri Mar 05 2004 - 18:43:46 CST