Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Bind Peeking with java application

Bind Peeking with java application

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: 12 Mar 2007 02:24:35 -0700
Message-ID: <1173691475.342293.284860@n33g2000cwc.googlegroups.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US