Re: Optimizer Instability on 10.2.0.2

From: <mccmx_at_hotmail.com>
Date: 26 Apr 2007 00:18:01 -0700
Message-ID: <1177571881.625486.56310@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 Received on Thu Apr 26 2007 - 02:18:01 CDT