Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Bind Peeking with java application
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?
Received on Mon Mar 12 2007 - 04:24:35 CDT