Databases Reference
In-Depth Information
Bind Variables and Shared SQL
As we've mentioned, Oracle's shared SQL is a key feature for building high-performance
applications. In an OLTP application, similar SQL statements may be used repeatedly,
but each SQL statement submitted will have different selection criteria contained in the
WHERE clause to identify the different sets of rows on which to operate. Oracle can
share SQL statements, but the statements must be absolutely identical.
To take advantage of this feature for statements that are identical except for specific
values in a WHERE clause, you can use bind variables in your SQL statements. The
values substituted for the bind variables in the SQL statement may be different, but the
statement itself is the same.
Consider an example application for granting raises to employees. The application sub‐
mits the following SQL:
UPDATE emp SET salary = salary * (1 + 0.1)
WHERE empno = 123;
UPDATE emp SET salary = salary * (1 + 0.15)
WHERE empno = 456;
These statements are clearly different; they update different employees identified by
different employee numbers, and the employees receive different salary increases. To
obtain the benefits of shared SQL, you can write the application to use bind variables
for the percentage salary increase and the employee numbers, such as:
UPDATE emp SET salary = salary * (1 + :v_incr)
WHERE empno = :v_empno;
UPDATE emp SET salary = salary * (1 + :v_incr)
WHERE empno = :v_empno;
These statements are recognized as identical and would therefore be shared. The ap‐
plication would submit different values for the two variables,
:v_incr
and
:v_empno
—
a percentage increase of 0.1 for employee 123 and 0.15 for employee 456. Oracle sub‐
stitutes these actual values for the variables in the SQL. The substitution occurs during
the phase of processing known as the
bind phase
, which follows the
parse phase
and
optimize phase
. For more details, see the relevant Oracle guide for your development
language.
Oracle Database 10
g
and more recent versions include tuning tools that can easily spot
this type of potential application optimization.
Scalability
Both the shared server and Database Resource Manager help Oracle support larger or
mixed user populations.
