Cristian Cudizio wrote:
> 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
>
> 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".
What Joel said ... your initial assumption is highly likely to be incorrect.
--
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org
Received on Tue Mar 13 2007 - 14:41:19 CDT
