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
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
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
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;
SELECT * FROM emp WHERE deptno = :bindvar;
Bind variable selectivities:
See Note 68992.1
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
o Balance the use of bind variables and shareable code with the need to
produce accurate plans.
Additional Search Words
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/16505464/viewspace-582406/，如需转载，请注明出处，否则将追究法律责任。