Re: Optimizer Instability on 10.2.0.2

<mccmx_at_hotmail.com> wrote in message
news:1177571881.625486.56310_at_n35g2000prd.googlegroups.com...
>> If the optimizer couldn't see the bind variable - there
>> were a couple of bugs that used to cause this with
>> the JDBC thin driver, for example - for the clause
>> where I01.AUDIT_STAMP > :1
>> then Oracle is going to use the default 5% calculation,
>> which could explain the tablescan.
>
> Do you happen to know if that bug was intermittent...? It appears
> that in our case the optimizer can't see the bind variables 50% of the
> time:
>
> *******************************************
> Peeked values of the binds in SQL statement
> *******************************************
> kxscoacd
> Bind#0
> No oacdef for this bind.
> Bind#1
> No oacdef for this bind.
> Bind#2
> No oacdef for this bind.
>
> and then it can see them on other occasions:
>
> *******************************************
> Peeked values of the binds in SQL statement
> *******************************************
> kkscoacd
> Bind#0
> oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
> oacflg=01 fl2=1000000 frm=00 csi=00 siz=136 off=0
> kxsbbbfp=0c45b374 bln=07 avl=07 flg=05
> value="4/18/2007 18:16:39"
> Bind#1
> oacdty=01 mxl=128(91) mxlc=00 mal=00 scl=00 pre=00
> oacflg=01 fl2=1000000 frm=01 csi=871 siz=0 off=8
> kxsbbbfp=0c45b37c bln=128 avl=07 flg=01
> value="TMMF001"
> Bind#2
> No oacdef for this bind.
>
> I wonder if it is the client program (sqr.exe) which is not sending
> the bind variables rather than Oracle not seeing them. i.e. if the
> binds are NULL would we see this behaviour..?
>
> Thanks very much for your feedback...
>
> Matt
>
>
>

The bug was not intermittent.

Do you have just one connection, or many connections - which may be using different versions of intermediate layers ? If not, it may be that there are a couple of different ways that the application addresses the database, and one of them fails to pass in binds.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html