Re: Bind Peeking with java application

On Mar 12, 1:02 pm, "Cristian Cudizio" <cristian.cudi..._at_yahoo.it> wrote:
> On Mar 12, 4:46 pm, DA Morgan <damor..._at_psoug.org> wrote:
>
>
>
> > Cristian Cudizio wrote:
> > > Hello,
> > > we have a strange problem. We have a java application that make a
> > > query like this:
> > > select * from table t where col1 = :bind1
> > > Were bind value is a number > 0.
> > > Table t has over 20 milion records, all record have value -1 for
> > > column col1. That means
> > > that above query returns always 0 record (why this i a long history).
> > > We hava an installation on Oracle 10.1.0.3 and 10.1.0.5 that has
> > > always worked correctly,
> > > with an index on column col1 that means fast.
> > > With another installation on Oracle db 10.2.0.2, with default
> > > statistics
> > > (on col1 there are histograms calculated). Sometimes Oracle makes a
> > > full table scan on table t.
> > > It suffices to do a "alter system flush shared pool" that Oracle
> > > recalculates the correct
> > > execution plan using index on column col1. This is like Oracle does
> > > not make Bind peeking, in fact,
> > > an explain plan with bind variable makes a full scan on table t, but
> > > a real execution plan with
> > > a bind value uses the indexes.
> > > Is possible that randomly, sometimes Oracle does not make BIND
> > > PEEKING ?
> > > Programmers have assured me that variable ":bind1" is never -1 that is
> > > value not selective, they
> > > assured me that ":bind1" is always > 0 that is a value highly
> > > selective. So the only
> > > answer for me i that Oracle sometimes does not make bind peeking. Is
> > > that possible, have
> > > somebody heard about a bug?
>
> > Other than the obvious bad design why not do this?
>
> > CREATE INDEX <index_name>
> > ON <table_name> (DECODE(col1, -1, NULL, col1);
>
> > If the value is always -1 Oracle is never going to use the index
> > to find a value of -1 so it is irrelevant that the value is not
> > indexed. The resulting index will be very small and contain every
> > non -1 value: According to you none at all.
> > --
> > Daniel A. Morgan
> > University of Washington
> > damor..._at_x.washington.edu
> > (replace x with u to respond)
> > Puget Sound Oracle Users Groupwww.psoug.org
>
> I'm DBA not developer, it seems that application cannot be changed, on
> some installations
> there are other values.
> The problem is that i think that oracle does not make bind peeking
> during hard parse,
> i know that bind peeking occurs only first time.
> If i user litteral values the execution plan is always optimal
> I think that also index as that suggested by Daniel sometimes may
> fail. If oracle
> does Hard parse without bind peeking it does not now that value is <>
> -1 and so
> oracle choose to make the full table scan.
> So i suspect that on eavy load Oracle during hard parse does not make
> bind peeking

What JDBC driver version are you using? Thin or OCI .. 9i or 10g??

Anurag Received on Mon Mar 12 2007 - 14:32:13 CDT