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.