转自网络
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 := ; SQL> INSERT INTO t (n, v) VALUES (:n, :v); SQL> execute :n := 2; :v := ; SQL> INSERT INTO t (n, v) VALUES (:n, :v); SQL> execute :n := 3; :v := ; SQL> INSERT INTO t (n, v) VALUES (:n, :v); SQL> SELECT sql_id, child_number, executions 2 FROM v$sql 3 WHERE sql_text = ; 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 := ; SQL> INSERT INTO t (n, v) VALUES (:n, :v); SQL> SELECT sql_id, child_number, executions 2 FROM v$sql 3 WHERE sql_text = ; SQL_ID CHILD_NUMBER EXECUTIONS ------------- ------------ ---------- 6cvmu7dwnvxwj 0 3 6cvmu7dwnvxwj 1 1 The 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 = ; CHILD_NUMBER BIND_MISMATCH ------------ ------------- 0 N 1 Y What happens is that the database engine applies the bind variable graduation. The aim of 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 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,,2,,m.datatype) AS datatype 3 FROM v$sql s, v$sql_bind_metadata m 4 WHERE s.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 It goes without saying that each time a child cursor is created, an execution plan is generated. Whether 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.
