RE: Date bind variable being peeked, but not used for cardinality estimate

From: "Allen, Brandon" <Brandon.Allen@xxxxxxxxxxx>
To: "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
Date: Tue, 22 Sep 2009 09:30:38 -0700

I ran a 10053 trace and it looks like Oracle is not really peeking at the 
variables for some reason even though they are shown in the 10046 trace and 
v$sql_bind_capture.  Here's what it shows for the bind variables in the 10053 
trace file:

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=00 fl2=1000000 frm=00 csi=00 siz=8 off=0
  No bind buffers allocated  <-------------------------------WHY NOT???
<snip>

I searched Metalink and Google to see what the known causes are for this, but 
all I could find was this thread from the oracle-l archive:

//www.freelists.org/post/oracle-l/No-bind-buffers-allocated-and-optim-peek-user-binds-true

The above thread mentions problems with JDBC and statistics_level=all, but I'm 
working with Oracle Forms, not JDBC (at least I don't think so, but maybe I'm 
wrong about that?), and I have statistics_level=typical so those don't appear 
to be causing my problem.  I also traced some other application queries to 
verify that they are doing bind variable peeking and they are, so it doesn't 
look like a client/server config issue - just a problem with this one query.  
I've already verified that _optim_peek_user_binds=TRUE also.  Does anyone know 
of anything else that could prevent bind variable peeking like this?

Thanks,
Brandon



________________________________
Privileged/Confidential Information may be contained in this message or 
attachments hereto. Please advise immediately if you or your employer do not 
consent to Internet email for messages of this kind. Opinions, conclusions and 
other information in this message that do not relate to the official business 
of this company shall be understood as neither given nor endorsed by it.

References:
- Date bind variable being peeked, but not used for cardinality estimate
  - From: Allen, Brandon

RE: Date bind variable being peeked, but not used for cardinality estimate

Other related posts: