Bind variables in queries

Purpose
~~~~~~~
This article is intended to provide more information on the usage of bind
variables in queries.

Scope & Application
~~~~~~~~~~~~~~~~~~~
This article is aimed at application designers/users who may not understand
some of the implications for them and the database in the usage of bind
variables.

Bind variables
~~~~~~~~~~~~~~

Bind variables are place holders for query input values. They are a pointer to
a memory location where data value(s) will be placed.

Note that the prescence of bind variables has no effect on queries that are
optimised using the RBO. They only affect CBO query optimization because the
CBO attempts to use column value information to determine the optimal access
path for the query.
When no values are supplied, the CBO may make a sub-optimal plan choice.

Advantages of bind variables:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

o When a bind variable as opposed to a hardcoded value is placed in a query,
the query code does not have to change each time the query is run. This means
that the code does not need to be reparsed and can be shared between sessions
and you do not need to maintain a copy of the statement for each value used
in the query. The effect of this is to reduce the amount of space used in the

shared pool to store almost identical copies of sql statements.

NB sharing also depends on other factors e.g.
o identical objects and object owners must be referenced
o bind variables must have the same datatype
o etc.

Disadvantages of bind variables:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

o When a SQL statement is optimized, the optimizer is unable to use the current

bind value. If it did then the plan chosen for that value may be excessively
poor for other values. Also the plan chosen would depend on which value was
supplied first. Because of this the optimizer must either choose the average
selectivity fo that column (the density) or use defaults. This may result in
the generation of a sub-optimal plan.

The CBO is unable to determine accurate selectivities for range predicate
containing bind variables. The CBO uses column value data to adjust
selectivities. If it does not have any data values to do this with
(such as if bind variables are used) then this is not possible and assumptions

have to be made.
For queries with range predicates using bind variables, we have no way of
calculating the selectivity, so we use a hardcoded default value of 5%
This is true irrespective of histograms as CBO does not know the value of
the bind variable.

Selectivity for bind variables with 'like' predicates defaults to 25%

Range Predicate Example:
~~~~~~~~~~~~~~~~~~~~~~~~
SELECT ename FROM emp WHERE empno > 9999;
SELECT ename FROM emp WHERE empno > :bind1;

Assuming the table has been analyzed, CBO knows the HIGH and LOW values for
empno and that the values are evenly distributed between these points.
For the first statement, CBO can determine the selectivity for the
where clause 'where empno >9999' - it uses the assumption that values
are evenly distributed to enable it to estimate the number of values between
the supplied value and the HIGH value.

For the second statement, it does not know what the value of :bind1 is,
so it is unable to use the same assumption and uses the default selectivity
of 5%.

It is possible to test the affect of using bind variables as opposed to literals
by setting up a variable in sqlplus. You can also assign a value to the
variable.
Setup details for a numeric bind variable called bindvar:

variable bindvar number;
begin
:bindvar:=10;
end;
/
SELECT * FROM emp WHERE deptno = :bindvar;

Bind variable selectivities:
~~~~~~~~~~~~~~~~~~~~~~~~~~~~
See Note 68992.1

Advice
~~~~~~

o Use bind variables for queries which are executed many times
e.g. OLTP environments
o Use literals where queries are not repeatedly executed and where the query
is sensitive to column sectivities.
o If bind variables are being used then use hints to force the desired access
path.
o Balance the use of bind variables and shareable code with the need to
produce accurate plans.