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

On Feb 22, 11:52 am, "Steve Howard" <stevedhow..._at_gmail.com> wrote:
> On Feb 22, 10:17 am, "Ben" <bal..._at_comcast.net> wrote:
>
> > 9.2.0.5 AIX5L Ent Ed
>
> > I have a table with appx 200000 rows and an indexed column that is
> > terribly skewed 3 values v1 = 80% rows, v2 = 19% and v3 = 1% of rows.
> > the table didn't have histograms, I analyzed using FOR ALL INDEXED
> > COLUMNS to generate a histogram for that column. The sql statement
> > that executes on this column uses a bind varible and the plan didn't
> > change so I'm still seeing a fts.
> > My question is how can I manually flush that sql statement from the
> > shared pool? Or is there a way to force Oracle to peek at the bind
> > variable again and change the plan that is used by that sql statement?
>
> > Thanks
>
> Hi Ben,
>
> try dbms_stats.delete_column_stats(), so the optimizer won't know
> *anything* about the data in that column. That will also force a hard
> parse, as it will invalidate the existing plan.
>
> HTH,
>
> Steve

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. Received on Thu Feb 22 2007 - 10:58:25 CST