Re: flush share pool for one sql to force new plan

On Feb 22, 11:58 am, "Ben" <bal..._at_comcast.net> wrote:
> Do plans get invalidated when statistics are gathered? If so then CBO
> should have peeked at this statement and seen that it was using ' '
> and started using the index.
>
> If I delete the stats on that column, then that will also delete the
> histogram on that column, no? I am trying to achieve the use of the
> histogram so that my selects that bind on that column actually use the
> index.- Hide quoted text -
>
> - Show quoted text -

Yes, on 9i, plans are invalidated when statistics are collected for objects that are referenced in the plans.

How do you know that Oracle is not generating a new plan, and still deciding that a full table scan costs less than an index lookup? How you checked a 10053 trace of the session?

You can force Oracle to reparse a query by adding a space, or by changing the capitalization of one or more characters in the SQL statement. You can force Oracle to use an index by using a hint, but it would probably be best to determine why Oracle believes that an index lookup is more expensive.

What have you set for optimizer_features_enable and compatible? Have you explicitly disabled bind variable peeking?

Charles Hooper
PC Support Specialist
K&M Machine-Fabricating, Inc. Received on Thu Feb 22 2007 - 11:24:36 CST