Re: Bind Peeking with java application

On Mar 12, 10:24 am, "Cristian Cudizio" <cristian.cudi..._at_yahoo.it> 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?

IIRC bind variable peeking only occurs at the *first* execution when cursor_sharing is force or similar.
If the statement never returns any rows, why don't you remove it from the code? Want to keep the server busy and/or demonstrate Oracle is 'slow'?

--
Sybrand Bakker
Senior Oracle DBA