Bind Variable Peeking Problems via OCI

I have spent the last few days troubleshooting an issue with an OCI application (JDEdwards Enterprise One) where Bind Variable Peeking was not taking place for some statements.  I tried to narrow down to see whether it was something specific to the environment, the SQL statement itself, or the objects being accessed but didn’t make much progress.

Dug into the 10053 trace as listed below, the first section is from a SQL statement where BVP did take place successfully, the second is from one where it didn’t.

----- Bind Info (kkscoacd) -----
Bind#0
oacdty=96 mxl=2000(510) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000010 frm=02 csi=2000 siz=2000 off=0
kxsbbbfp=7f211ceaf5c0  bln=2000  avl=36  flg=05
value=0 5c 0 5c 0 63 0 6f 0 72 0 70 0 72 0 74 0 73 0 31 0 5c 0 64 0 65 0 76 0 70 0 72 0 6e 0 31
----- Bind Info (kkscoacd) -----
 Bind#0
 oacdty=96 mxl=128(60) mxlc=00 mal=00 scl=00 pre=00
 oacflg=01 fl2=1000010 frm=02 csi=2000 siz=128 off=0
 No bind buffers allocated

Interestingly, in the Outline Data generated for the ‘problem’ case, it adds a ‘directive’ that implies that BVP has not taken place:

  Outline Data:
  /*+
    BEGIN_OUTLINE_DATA
      IGNORE_OPTIM_EMBEDDED_HINTS
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      DB_VERSION('12.1.0.2')
      OPT_PARAM('_optim_peek_user_binds' 'false')
      ALL_ROWS
      OUTLINE_LEAF(@"SEL$1")
      INDEX_RS_ASC(@"SEL$1" "F986162"@"SEL$1" ("F986162"."PDPAPT"))
    END_OUTLINE_DATA
  */

I found out that it is possible to trace OCI calls on the client side by setting the following environment variable:

EVENT_10842="server=all;user=all;stmt=all;level=15"

From analyzing the generated trace files, I found that all the ‘problem’ statements were first executed by calling OCIStmtExecute with parameter mode set to OCI_DESCRIBE_ONLY.

I was then able to reproduce the problem by writing a simple oci c program.

This exactly matches bug 9630092 which is listed as fixed, but not enabled by default. It can be enabled by running the following statement:

ALTER SYSTEM set "_fix_control"='9630092:ON';

The fix does work, a new child is generated to allow bind variable peeking to take place.  Examination of the reason field shows “User Bind Peek settings mismatch(0)”.

 

 

 

 

 

 

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s