ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Bind Variable

Bind Variable

原创 Linux操作系统 作者:OwenFei 时间:2009-03-31 15:40:35 0 删除 编辑

Metalink 70075.1

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.


Additional Search Words
-----------------------
cost based

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/16505464/viewspace-582406/,如需转载,请注明出处,否则将追究法律责任。

上一篇: 没有了~
下一篇: Historgram Information
请登录后发表评论 登录
全部评论

注册时间:2008-11-03

  • 博文量
    4
  • 访问量
    10244