O'Reilly logo
  • Nand Kishore Sagi thinks this is interesting:

The new child cursor (1) is created because the execution environment between the first three INSERT statements and the fourth has changed. The mismatch, as shown in the following example, can be confirmed by querying the v$sql_shared_cursor view. Note that the bind_length_upgradeable column exists as of version 11.2 only. In previous releases, this information is provided by the bind_mismatch column:


Cover of Troubleshooting Oracle Performance, Second Edition


As of Oracle 11gR2 this happens only if the actual literal value is greater than 32 characters. Just by changing the max length of the data type Oracle will not create a new child. A new child is created only when the actual data passed for :v is greater than 32 characters