关于Oracle绑定变量的个人理解及使用场景

原创冯睿 2020-03-28

1718

关于Oracle绑定变量

绑定.png

摘自——Oracle 11g concept 中英文对照版

使用了绑定变量能提高性能主要是因为这样做可以尽量避免不必要的硬解析而节约了时间，同时节约了大量的CPU资源。
　　绑定变量的本质就是本来需要做Oracle 硬解析的SQL 变成软解析，以减少ORACLE 花费在SQL解析上的时间和资源。
　　简单的说，绑定变量就是拿一个变量来代替谓词常量，让Oracle每次对用户发来的SQL做hash 运算时，运算出的结果都是同样的Hash值，于是将所有的用户发来的SQL看作是同一个SQL来对象。
假如有两条SQL:
Select salary from user where name=’A’;
Select salary from user where name=’B’;

如果没有用绑定变量，那么这2条SQL 会被解析2次，因为他们的谓词部分不一样。如果我们用了绑定变量，如：
Select salary from user where name=:X;

这时，之前的2条SQL就变成了一种SQL， Oracle 只需要对每一种SQL做一次硬解析，之后类似的SQL 都使用这条SQL产生的执行计划，这样就可以大大降低数据库花费在SQL解析上的资源开销。这种效果当SQL执行的越多，就越明显。

测试如下图：

未使用绑定变量.png

使用绑定变量.png

绑定变量在OLAP系统和OLTP系统

在OLAP系统中，不需要使用绑定变量，因为整个系统的执行量很小，分析时间对于执行时间来说，可以忽略，而且可避免出现错误的执行计划。但是OLAP中可以大量使用位图索引，物化视图，对于大的事务，尽量寻求速度上的优化，没有必要像OLTP要求快速提交，甚至要刻意减慢执行的速度。

对于OLAP系统中的绑定变量，有以下原则：

（1）OLAP 系统完全没有必要绑定变量，那样只会带来负面的影响，比如导致SQL选择错误的执行，
这个代价有时是灾难性的；让Oracle对每条SQL做硬分析，确切的知道谓词条件的值，这对执行计划的选择至关重要，这样做的原因是，在OLAP系统中，SQL硬分析的代价是可以忽略的，系统的资源基本上是用于做大的SQL查询，和查询比起来，SQL解析消耗的资源显得微不足道。所以得到一个最优的执行计划就非常重要。

（2）在OLAP系统中，让Oracle确切地知道谓词的数值至关重要，它直接决定了SQL执行计划的选择，这样做的方式就是不要绑定变量。

（3）在OLAP系统中，表，索引的分析显得直观重要，因为它是Oracle 为SQL做出正确的执行计划的信息的来源和依据，所以需要建立一套能够满足系统需求的对象分析的执行Job。

结论：如果Oracle中有大量的类似sql，基本结构一样，但是条件的取值不一样，那么，应该采用绑定变量的方法，来减少sql的硬解析。因此绑定变量真正的用途是在OLTP系统中，这个系统通常有这样的特点，用户并发数很大，用户的请求十分密集，并且这些请求的SQL 大多数是可以重复使用的。

oracle sql优化

最后修改时间：2020-03-28 20:01:56

「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」

关注作者

1人已赞赏

Thomas

3年前

但这也说明一个问题，有时像insert into tb_zxp values (i);这样的语句，咋看并没有使用DYNAMIC SQL和USING VARIABLE的形式，但实际上还是视同做了绑定变量。

3年前

冯睿

3年前

在PL/SQL中这样直接调用，Oracle在预编译的时候会把变量换成绑定变量。例子中为了方便演示所以使用PL/SQL循环插入，实际生产环境中，这种情况可能不多，通常都是未使用绑定变量直接调用SQL语句，从而造成大量硬解析。

3年前

Thomas

3年前

试验了，确实如你所说。谢谢！学到不少东西。

3年前

Thomas

3年前

单从我发的如上例子看，是否用绑定变量，似乎没区别

3年前

冯睿

3年前

对比性试验，要使用“控制变量法”，使用到绑定变量的测试其中insert语句是动态SQL，那么在不使用绑定变量时，也应该使用动态SQL，可以再测一遍。execute immediate 'insert into tb_zxp values('||i||')';

3年前

冯睿

3年前

可以做个10046去看一下，你的第二个代码块insert部分“insert into tb_zxp values (i);”，已经被自动转换使用绑定变量了。因此前后两次执行结果几乎没区别。 begin for i in 1 .. 100000 loop insert into test_insert values(i); end loop; commit; end; call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 1 1.71 1.75 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 2 1.71 1.75 0 0 0 1 Misses in library cache during parse: 1 Optimizer mode: ALL_ROWS Parsing user id: 90 (FR) Elapsed times include waiting on following events: Event waited on Times Max. Wait Total Waited ---------------------------------------- Waited ---------- ------------ log file sync 1 0.00 0.00 SQL*Net message to client 1 0.00 0.00 SQL*Net message from client 1 24.70 24.70 ******************************************************************************** SQL ID: 49p2818p5b3ga Plan Hash: 0 INSERT INTO TEST_INSERT VALUES (:B1 ) call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 100000 2.21 2.25 2 1092 104066 100000 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 100001 2.21 2.25 2 1092 104066 100000

3年前

Thomas

3年前

begin for i in 1..1000000 loop execute immediate 'insert into tb_zxp values (:x)' using i; end loop; commit; end; / PL/SQL procedure successfully completed. Elapsed: 00:01:05.48 SQL> truncate table tb_zxp; Table truncated. Elapsed: 00:00:00.26 begin for i in 1..1000000 loop insert into tb_zxp values (i); end loop; commit; end; / PL/SQL procedure successfully completed. Elapsed: 00:01:06.28

3年前

关于Oracle绑定变量的个人理解及使用场景

关于Oracle绑定变量

绑定变量在OLAP系统和OLTP系统

评论

相关阅读