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)”.