On Feb 22, 1:34 pm, "Ben" <bal..._at_comcast.net> wrote:
> On Feb 22, 1:16 pm, pound..._at_nyc.rr.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
>
> > 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
>
> I don't believe this is an option either. As the statement is
> generated in our ERP system, and I believe it uses bind variables for
> everything.- Hide quoted text -
>
> - Show quoted text -
Using the histogram for that column probably makes the optimizer think
that fts is better, especially when the bound value belongs to the 80%
rows having the same value. Try removing the histogram and see what
the result is and if it is worse put it back - it may prove that
Oracle was right FTS faster than using the index or that you don't
need the histogram (see Note: 1031826.6 on Metalink).
Received on Thu Feb 22 2007 - 13:33:13 CST
