动态SQL及绑定变量

一. 绑定变量：

绑定变量是解决硬解析的首要利器，能解决OLTP系统中library cache的过度耗用以提高性能。减少解析。

在 SQL 语句中，绑定变量是一个占位符。例如，为了查询员工号为 123 的员工的信息，可以查询：

1 ） select * from emp where empno=123;

 

另外，也可以查询：

2 ） select * from emp where empno=:empno 。

 

 

     那么每次查询都是一个新查询，即在数据库共享池中以前没有过的查询。每次查询必须经过分析、限定（名称解析）、安全检查、优化等等，简单地说，执行的每条语句在每次执行时都将必须经过编译。

 

在第二个查询 2 ）中使用了绑定变量 :empno ，它的值在查询执行时提供。查询经过一次编译后，查询方案将存储在共享池中，可以用来检索和重用。在性能和可伸缩性方面，这两者的差异是巨大的，甚至是惊人的

 

 

       从上所述，很明显看出，分析一个带有硬编码量的语句将比重用一条已分析过的查询方案花费更长的时间和消耗更多的资源，不明显的是前者将减少系统所能支持的用户数量。很明显，部分原因是由于增加资源消耗量，但更主要的因素是在解析 sql 语句的过程中对共享池中锁存器（ latch ）的争抢

 

       通过使用绑定变量，应用程序提交的相似的 sql 语句只需要解析一次，就可以重复使用，这非常有效，这也是Oracle 数据库要求使用的工作方式。不仅使用较少的资源，而且可以减少锁存（ latch ）时间，降低锁存（ latch ）次数，这将提高应用系统性能，并且大大提高可伸缩性。

了解绑定变量，就不得不了解硬解析与软解析。硬解析简言之即一条SQL语句没有被运行过，处于首次运行，则需要对其进行语法分析，语
义识别，跟据统计信息生成最佳的执行计划，然后对其执行。而软解析呢，则是由于在library cache已经存在与该SQL语句一致的SQL语句文本
、运行环境，即有相同的父游标与子游标，采用拿来主义，直接执行即可。软解析同样经历语法分析，语义识别，且生成hash value ，接下来
在library cache搜索相同的hash value ，如存在在实施软解析。有关更多的硬解析与软解析以及父游标，子游标请作如下参考：
   
    有关硬解析与软解析，请参考： oracle中的软解析和硬解析
    有关父游标、子游标，请参考： 父游标、子游标及共享游标
   
    绑定变量
      首先其实质是变量，有些类似于我们经常使用的替代变量，替代变量使用&占位符，只不过绑定变量使用:
      替代变量使用时为&variable_para，相应的绑定变量则为:bind_variable_para
      通常一个SQL语句包含动态部分和静态部分，占位符实质是SQL语句中容易发生变化的部分，通常为其条件或取值范围。动态部分在一般情
      况下(数据倾斜除外)，对执行计划的生成的影响是微乎其微的。故同一SQL语句不同的动态部分产生的执行计划都是相同的。

绑定变量的优缺点及使用场合
    优点：
        可以在library cache中共享游标，避免硬解析以及与之相关的额外开销
        在大批量数据操作时将呈数量级来减少闩锁的使用，避免闩锁的竞争
     
    缺点：
        绑定变量被使用时，查询优化器会忽略其具体值，因此其预估的准确性远不如使用字面量值真实，尤其是在表存在数据倾斜(表上的数
        据非均匀分布)的列上会提供错误的执行计划。从而使得非高效的执行计划被使用。
   
    使用场合：
        OLTP
            在OLTP系统中SQL语句重复执行频度高，但处理的数据量较少，结果集也相对较小，尤其是使用表上的索引来缩小中间结果集，其
            解析时间通常会接近或高于执行时间，因此该场合适合使用绑定变量。
       
        OLAP
            在OLAP系统中，SQL语句执行次数相对较少，但返回的数据量较大，因此多数情况下倾向于使用权标扫描更高效，其SQL语句执行时
            间远高于其解析时间，因此使用绑定变量对于总响应时间影响不大。而且增加生成低效执行计划的风险。即在在OLAP系统中使用字
            面量的性能高于使用绑定变量。
   
    注意：
        对于实际的数据库对象，如(表，视图，列等)，不能使用绑定变量替换，只能替换字面量。如果对象名是在运行时生成的，则需要对其
        用字符串拼接，同时，sql只会匹配已经在共享池中相同的对象名

二、动态SQL

说动态SQL之前先来说下静态SQL

 

静态SQL语句

 

语句中主变量的个数与数据类型在预编译时都是确定的，我们称这类嵌入式SQL语句为静态SQL语句。

 

与之相对应的就是动态SQL

 

动态SQL方法允许在程序运行过程中临时“组装”SQL语句。

 

那么他们之间的区别是什么呢？

 

静态sql的执行计划（DB2称存取路径）是在运行前就确定好的

 

动态sql的执行计划（DB2称存取路径）是在运行时动态生成的。由于是在运行时动态生成执行计划，因此生成的执行计划（DB2称存取路径）相对更优，但考虑到生成执行计划（DB2称存取路径）的开销，有可能应用程序的运行时间相对会比静态sql长些 。

总结： 动态SQL在运行时编译，所以执行计划更优，但相对于静态编译的静态SQL或者存储过程更耗性能。

绑定变量可以重复利用相似的SQL，使效率更高。