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

If the column only has 3 values and they are highly skewed then don't use a bind variable for that column. Use a literal instead. This will give you three statements in cache with the appropriate plan for each value.

JR Received on Thu Feb 22 2007 - 12:16:54 CST