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
I can't explain completely business problem because it would take too
long, i think that here is not he place
I would underline that my problem is more technical. I'm DBA for a
Independend Software Vendor. We have a
program that is configurable by the client. With some clients it
happens the situation that i've reported.
One day my boss says me: "we have to install our application with
Oracle 10g" this our application
has been developed with oracle 9.2 with RULE optimizer and has alwais
worked fine. Now my boss
asks me: "why the customer has been blocked?" And i've no other answer
than "Oracle 10.2 is worst than oracle 9.2 and 10.1".
That's not an answer. I can't easily reproduce the problem and when it
appens i've no explanation the cost in v$sql_plan is higher than
optimal . i make a "alter system flush shared_pool" and the system
restarts working well. Our system is interactive, and rapid
response time is important.
Received on Tue Mar 13 2007 - 11:15:35 CDT
