v$sql_shared_cursor中的BIND_MISMATCH

轉自網路

The advantage of bind variables is that they allow the sharing of cursors in the library cache
and that way avoid hard parses and the overhead associated with them. The following example,
which is an excerpt of the output generated by the script. bind_variables.sql, shows three
INSERT statements that, thanks to bind variables, share the same cursor in the library cache:
SQL> variable n NUMBER
SQL> variable v VARCHAR2(32)
SQL> execute :n := 1; :v := 'Helicon';
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
SQL> execute :n := 2; :v := 'Trantor';
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
SQL> execute :n := 3; :v := 'Kalgan';
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
 
SQL> SELECT sql_id, child_number, executions
2 FROM v$sql
3 WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj 0 3
There are, however, situations where several child cursors are created even with bind variables.
The following example shows such a case. Notice that the INSERT statement is the same
as in the previous example. Only the maximum size of the VARCHAR2 variable has changed (from
32 to 33).
SQL> variable v VARCHAR2(33)
SQL> execute :n := 4; :v := 'Terminus';
SQL> INSERT INTO t (n, v) VALUES (:n, :v);
SQL> SELECT sql_id, child_number, executions
2 FROM v$sql
3 WHERE sql_text = 'INSERT INTO t (n, v) VALUES (:n, :v)';
SQL_ID CHILD_NUMBER EXECUTIONS
------------- ------------ ----------
6cvmu7dwnvxwj 0 3
6cvmu7dwnvxwj 1 1
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 can be confirmed by
querying the view v$sql_shared_cursor, is because of the bind variables.
SQL> SELECT child_number, bind_mismatch
2 FROM v$sql_shared_cursor
3 WHERE sql_id = '6cvmu7dwnvxwj';
CHILD_NUMBER BIND_MISMATCH
------------ -------------
0 N
1 Y
What happens is that the database engine applies the 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 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 datatype NUMBER are graduated
to their maximum length, which is 22 bytes. As the following example shows, the view
v$sql_bind_metadata 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 = '6cvmu7dwnvxwj'
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
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.