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 -> Re: Bind Peeking with java application

Re: Bind Peeking with java application

From: Cristian Cudizio <cristian.cudizio_at_yahoo.it>
Date: 13 Mar 2007 03:55:09 -0700
Message-ID: <1173783309.398248.193360@n33g2000cwc.googlegroups.com>


On Mar 12, 11:07 pm, DA Morgan <damor..._at_psoug.org> wrote:
> Cristian Cudizio 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
>
> I think you are deriving conclusions for which I can not think of
> any supporting information and are fixated on bind peeking when it
> is not necessarily relevant to the issue.
>
> And your statement "I think that also index as that suggested by Daniel
> sometimes may fail." ridiculous but I am willing to listen to why you
> think this is the case.
>
> I suggest that you stop looking at a specific issue/solution and focus
> on the business problem. What is it?
> --
> Daniel A. Morgan
> University of Washington
> damor..._at_x.washington.edu
> (replace x with u to respond)
> Puget Sound Oracle Users Groupwww.psoug.org

We user OCI JDBC driver 10g.
To use the index
CREATE INDEX <index_name>
ON <table_name> (DECODE(col1, -1, NULL, col1);

We would change the query and that is not possible. The solution is similar to that suggested in Thomas Kyte book "Expert Oracle" on chapter "INDEXES".

I don't care the index space occupation, the programmer says me:"if all values or the 99% of the values of the column are -1 and i search the value 2 this value is high selective, with index access path the query is fast". If you make "set autotrace on" in SQL*Plus he returns a plan with a full table scan because he does not make bind peeking, it sees by index statistics
that index is not selective and so decides to make a full table scan. With literal values (that is as during hard parse with bind peeking) Oracle use Histograms and make the correct choice: use index. If i create a functional index and then i use bind variables i would expect same "nonsense" problem. What i've seen is that every 10-15 day customer call me and says "The system is blocked!" and i see tha query is making full table scan (with higher cost!). Really we have solved business problem but if the problem is what i suspect that some times during hard parse bind peeking does not occur, such a problem may occur on other queries. I repeat on another bigger installation, but with Oracle 10.1.0.3 such problem has never occurred. Received on Tue Mar 13 2007 - 05:55:09 CDT

Original text of this message

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