在OLTP系统中,应该尽可能的使用绑定变量。在OLAP系统中,绑定变量不是必须的,相反,如果使用了反而可能带来副作用。
下面的例子对比了一下一条sql被执行10000次时,绑定变量和非绑定变量在资源消耗上的情况:
SQL> alter session set sql_trace = true; 会话已更改。 SQL> begin 2 for x in 1 .. 10000 loop 3 execute immediate 'select * from t where object_name = :X' using x; 4 end loop; 5 end; 6 / PL/SQL 过程已成功完成。 SQL> alter session set sql_trace = false; 会话已更改。
******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.02 0.02 0 0 0 0 Execute 2 0.62 0.70 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 0.64 0.72 0 0 0 1 Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.00 0.00 0 0 0 0 Execute 10000 0.30 0.20 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 10001 0.30 0.21 0 0 0 0 Misses in library cache during parse: 1 3 user SQL statements in session. 0 internal SQL statements in session. 3 SQL statements in session. ********************************************************************************
可以看见使用绑定变量的各种数据统计如下:
执行时间:0.72+0.21=0.93
CPU时间:0.64+0.30=0.94
分析次数:2+1=3
执行次数:2+10000=10002
SQL> alter session set sql_trace = true; 会话已更改。 SQL> begin 2 for x in 1 .. 10000 loop 3 execute immediate 'select * from t where id = ' || x; 4 end loop; 5 end; 6 / PL/SQL 过程已成功完成。 SQL> alter session set sql_trace = false; 会话已更改。
******************************************************************************** OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 2 0.02 0.02 0 0 0 0 Execute 2 2.10 2.45 0 0 0 1 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 4 2.12 2.47 0 0 0 1 Misses in library cache during parse: 1 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 10000 6.25 6.46 0 0 0 0 Execute 10000 1.01 0.41 0 0 0 0 Fetch 0 0.00 0.00 0 0 0 0 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- total 20000 7.27 6.87 0 0 0 0 Misses in library cache during parse: 10000 10002 user SQL statements in session. 0 internal SQL statements in session. 10002 SQL statements in session. ********************************************************************************
可以看见不使用绑定变量的各种数据统计如下:
执行时间:2.47+6.87=9.34
CPU时间:2.12+7.27=9.39
分析次数:2+10000=10002
执行次数:2+10000=10002
更重要的是通过做实验,可以看见使用绑定变量的跟踪文件里面只有一条sql:select * from t where object_name = :X
不使用绑定变量里面有10000条sql:select * from t where id = 1 一直到到 select * from t where id = 10000 更容易理解两者的区别!!!