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

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 Received on Thu Feb 22 2007 - 10:52:38 CST