BIND_MISMATCH reason for not sharing cursor

BIND_MISMATCH reason is another one from the long list of possible reasons why not to share a cursor. General reason here is that bind variables between two executions differ too much for the database. The details are stored in the REASON column of the V$SQL_SHARED_CURSOR view. Here is how it looks like:


<childnode>
<childnumber>1</ChildNumber>
<id>40</ID>
<reason>Bind mismatch(22)</reason>
<size>4x4</size>
<bind_position>157</bind_position>
<original_oacflg>1</original_oacflg>
<original_oacmxl>32</original_oacmxl>
<upgradeable_new_oacmxl>128</upgradeable_new_oacmxl>
</ChildNode>




Whole thing is quite easy to decrypt. There is one node per an existing cursor child. The REASON tag provides one of reasons the database provides when it does not use an existing cursor child, but instead creates another one. The bind position indicates the position of the bind variable within a SQL text (every entry has a unique position per SQL even if we bind the same variable).
The ORIGINAL_OACFLG tag IMHO is the same entity as the OACFLG (or first FLG) in a bind section of a 10046 event raw trace and of course there is more equivalents.

The bind variables use some standard size buffers, which depend on data type. For varchar2 there are several sizes (due to varying nature of the type) and first size amounts to 32, so the minimum buffer length for bind variable of varchar2 is 32 bytes. The next values are 128 bytes, then 2000 bytes, and so on.

The change between different values of a bind variable may become a reason for not sharing a cursor . This happens when a buffer size of a variable changes - for example from smaller to larger.

Some details about bind section of the 10046 event trace

In general very good description of the 10046 event trace interpretation is contained on MOS as the article with 39817.1 id.
However with time there showed new entries not really mentioned in the article indicated above.
Let's see an example of the bind section:

Bind#2
oacdty=96 mxl=32(02) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=02 csi=2000 siz=0 off=56
kxsbbbfp=7fb04313fe80 bln=32 avl=02 flg=01
value=0 54

oac prefix I simply ignore and not sure what it stands for.

• dty is an id for data type
• mxl is a maximum length of bind variable (in parentheses there is provided a private maximum length - for me this is current bind variable length)
• mxlc is not sure for me, but I saw it always equal to 00
• mal is a maximum array length
• scl stands for scale
• pre stands for precision
• flg is a flag with bind options
• fl2 is a continuation of flag
• frm stays unknown
• csi is character set id, which can be decrypted with nls_charset_name function; the nls_charset_id function, working in an opposite way, is available as well
• siz is an amount of memory allocated for this chunk
• off is a memory offset of a buffer for this variable within the chunk
• kxsbbbfp is a bind addres
• bln is a bind buffer length and I suppose usually it is equal to mxl, unless data does not fit into one chunk
• avl is a current bind value length (or array length)
• flg is another flag (this time for bind status)
• value - finally current value