Key Concepts - Troubleshooting Oracle Performance - page 29

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:

SQL> SELECT child_number, bind_length_upgradeable
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '6cvmu7dwnvxwj';

CHILD_NUMBER BIND_LENGTH_UPGRADEABLE
------------ -----------------------
0 N
1 Y

What happens is that the database engine uses a feature called bind variable graduation . The aim of this feature is
to minimize the number of child cursors by graduating bind variables (which vary in size) into four groups depending
on their size. The first group contains the bind variables with up to and including 32 bytes, the second contains the
bind variables between 33 and 128 bytes, the third contains the bind variables between 129 and 2,000 bytes, and the
last contains the bind variables of more than 2,000 bytes. Bind variables of NUMBER datatype are graduated to their
maximum length, which is 22 bytes. As the following example shows, the v$sql_bind_metadata view displays the
maximum size of a group. Notice how the value 128 is used, even if the variable of child cursor 1 was defined as 33:

SQL> SELECT s.child_number, m.position, m.max_length,
2 decode(m.datatype,1,'VARCHAR2',2,'NUMBER',m.datatype) AS datatype
3 FROM v$sql s, v$sql_bind_metadata m
4 WHERE s.sql_id = '&sql_id'
5 AND s.child_address = m.address
6 ORDER BY 1, 2;

CHILD_NUMBER POSITION MAX_LENGTH DATATYPE
------------ ---------- ---------- --------
0 1 22 NUMBER
0 2 32 VARCHAR2
1 1 22 NUMBER
1 2 128 VARCHAR2

■ the example shows that there's a bind mismatch when bind variables of different groups are used. this occurs
only when a bind variable is associated to a new group having a greater maximum size than the original. In fact, if you
carefully review the example, the size of the bind variables always increases. If they decreased, all executions could
share a single child cursor. In fact, child cursors created with VARCHAR2 bind variables of the maximum size support any
VARCHAR2 bind variables having a smaller size.

Note

It goes without saying that each time a new child cursor is created, an execution plan is generated. Whether this
new execution plan is equal to the one used by another child cursor also depends on the value of the bind variables.
This is described in the next section.