Database Reference
In-Depth Information
Advantage
The advantage of bind variables for performance is that they allow the sharing of parent 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
bind_variables_graduation.sql
script, 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
