Oracle Bind Variable(绑定变量)就其本质来说就是把本来需要Oracle做硬解析的SQL变成了软解析，以减少Oracle花费在SQL解析上的时间和资源。
我们设想一个银行ATM机系统，这是一个比较典型的OLTP系统，用户分别在不同的ATM机上做操作，其实所有的用户做的操作基本上是相同的，主要是这样3种，查询，取款，存款。
对于查询，发出的查询大概就是这样的一条SQL：
Select account_value from account_info where acount_name = 'myname';
而对于取款和存款，对于数据库来说是一样的，不过是修改当前账户上余额的一个数值：
update account_info set account_value = new_value where account_name = 'myname';
比如我们有两个用户A和B都分别查询了自己的余额，并且各自取了500块钱和1000块钱，那么他们各自发出的SQL分别是：
用户A：
1 select account_value from account_info where acount_name = 'A'; 2 update account_info set account_value = account - 500 where account_name = 'A';
用户B：
1 select account_value from account_info where acount_name = 'B'; 2 update account_info set account_value = acount_value - 1000 where account_name = 'B';
我们看到，其实他们两个人发出的2条SQL，除了各自的谓词条件不同之外，其他的都相同，包括语法，操作的对象，权限等，所以如果我们拿一个共同的东西来代替他们的谓词条件，那么这条SQL岂不是就变成了1条SQL，比如：
1 select account_value from account_info where acount_name = :X; 2 update account_info set account_value = acount_value - 1000 where account_name = :Y;
通过上面的一个变量替代，我们就将两条SQL变成了1条SQL了，这样的好处在于，Oracle只需对每一种SQL做一次硬解析，后续类似的SQL(指的是只有谓词条件不同的SQL)都使用这条SQL产生的执行计划，这样就可以大大降低数据库花费在SQL解析上的资源开销。
下面的例子对比了一下一条SQL被执行10000次时，绑定变量和非绑定变量在资源消耗上的情况：
1 SQL> show parameter cursor_sharing; 2 3 NAME TYPE VALUE 4 ------------------------------------ ----------- ------------------------------ 5 cursor_sharing string EXACT 6 SQL> create table jack as select * from dba_objects; 7 8 Table created. 9 10 SQL> alter session set sql_trace = true; 11 12 Session altered. 13 14 SQL> begin 15 2 for x in 1..10000 loop 16 3 execute immediate 'select * from jack where object_id=:x' using x; 17 4 end loop; 18 5 end; 19 6 / 20 21 PL/SQL procedure successfully completed. 22 23 SQL> alter session set sql_trace = false; 24 25 Session altered. 26 27 SQL> @showtrace; 28 29 trace_file_name 30 -------------------------------------------------------------------------------- 31 /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/yft2_ora_5865.trc 32 33 [oracle@node2 ~]$ tkprof /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_5865.trc out.txt sys=no 34 35 TKPROF: Release 11.2.0.1.0 - Development on Sat Feb 2 15:33:16 2013 36 37 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 38 39 ----trace文件信息----
40 [oracle@node2 ~]$ cat out.txt 41 42 TKPROF: Release 11.2.0.1.0 - Development on Sat Feb 2 15:33:16 2013 43 44 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 45 46 Trace file: /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_5865.trc 47 Sort options: default 48 49 ******************************************************************************** 50 count = number of times OCI procedure was executed 51 cpu = cpu time in seconds executing 52 elapsed = elapsed time in seconds executing 53 disk = number of physical reads of buffers from disk 54 query = number of buffers gotten for consistent read 55 current = number of buffers gotten in current mode (usually for update) 56 rows = number of rows processed by the fetch or execute call 57 ******************************************************************************** 58 59 SQL ID: 1hgzr5xxpmt7h 60 Plan Hash: 0 61 alter session set sql_trace = true 62 63 64 call count cpu elapsed disk query current rows 65 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 66 Parse 0 0.00 0.00 0 0 0 0 67 Execute 1 0.00 0.00 0 0 0 0 68 Fetch 0 0.00 0.00 0 0 0 0 69 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 70 total 1 0.00 0.00 0 0 0 0 71 72 Misses in library cache during parse: 0 73 Misses in library cache during execute: 1 74 Optimizer mode: ALL_ROWS 75 Parsing user id: 87 76 ******************************************************************************** 77 78 begin 79 for x in 1..10000 loop 80 execute immediate 'select * from jack where object_id=:x' using x; 81 end loop; 82 end; 83 84 call count cpu elapsed disk query current rows 85 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 86 Parse 1 0.00 0.00 0 0 0 0 87 Execute 1 1.05 1.07 0 0 0 1 88 Fetch 0 0.00 0.00 0 0 0 0 89 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 90 total 2 1.06 1.07 0 0 0 1 91 92 Misses in library cache during parse: 1 93 Optimizer mode: ALL_ROWS 94 Parsing user id: 87 95 ******************************************************************************** 96 97 SQL ID: ba3vgakkn7zz5 98 Plan Hash: 949574992 99 select * 100 from 101 jack where object_id=:x 102 103 104 call count cpu elapsed disk query current rows 105 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 106 Parse 1 0.00 0.00 0 0 0 0 107 Execute 10000 0.07 0.09 0 1 0 0 108 Fetch 0 0.00 0.00 0 0 0 0 109 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 110 total 10001 0.07 0.09 0 1 0 0 111 112 Misses in library cache during parse: 1 113 Optimizer mode: ALL_ROWS 114 Parsing user id: 87 (recursive depth: 1) 115 116 Rows Row Source Operation 117 ------- --------------------------------------------------- 118 0 TABLE ACCESS FULL JACK (cr=0 pr=0 pw=0 time=0 us cost=287 size=2484 card=12) 119 120 ******************************************************************************** 121 122 SQL ID: brjr07qkfmgww 123 Plan Hash: 4286693666 124 SELECT /* OPT_DYN_SAMP */ /*+ ALL_ROWS IGNORE_WHERE_CLAUSE 125 NO_PARALLEL(SAMPLESUB) opt_param('parallel_execution_enabled', 'false') 126 NO_PARALLEL_INDEX(SAMPLESUB) NO_SQL_TUNE */ NVL(SUM(C1),0), NVL(SUM(C2),0) 127 FROM 128 (SELECT /*+ IGNORE_WHERE_CLAUSE NO_PARALLEL("JACK") FULL("JACK") 129 NO_PARALLEL_INDEX("JACK") */ 1 AS C1, CASE WHEN "JACK"."OBJECT_ID"=:B1 THEN 130 1 ELSE 0 END AS C2 FROM "JACK" SAMPLE BLOCK (6.000000 , 1) SEED (1) "JACK") 131 SAMPLESUB 132 133 134 call count cpu elapsed disk query current rows 135 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 136 Parse 1 0.00 0.00 0 0 0 0 137 Execute 1 0.00 0.00 0 0 0 0 138 Fetch 1 0.01 0.01 265 60 0 1 139 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 140 total 3 0.02 0.02 265 60 0 1 141 142 Misses in library cache during parse: 1 143 Misses in library cache during execute: 1 144 Optimizer mode: ALL_ROWS 145 Parsing user id: 87 (recursive depth: 2) 146 147 Rows Row Source Operation 148 ------- --------------------------------------------------- 149 1 SORT AGGREGATE (cr=60 pr=265 pw=0 time=0 us) 150 3817 TABLE ACCESS SAMPLE JACK (cr=60 pr=265 pw=0 time=71104 us cost=19 size=128650 card=5146) 151 152 ******************************************************************************** 153 154 SQL ID: 988n7wn97ptgf 155 Plan Hash: 0 156 alter session set sql_trace = false 157 158 159 call count cpu elapsed disk query current rows 160 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 161 Parse 1 0.00 0.00 0 0 0 0 162 Execute 1 0.00 0.00 0 0 0 0 163 Fetch 0 0.00 0.00 0 0 0 0 164 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 165 total 2 0.00 0.00 0 0 0 0 166 167 Misses in library cache during parse: 1 168 Optimizer mode: ALL_ROWS 169 Parsing user id: 87 170 171 172 173 ******************************************************************************** 174 175 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 176 177 call count cpu elapsed disk query current rows 178 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 179 Parse 2 0.00 0.00 0 0 0 0 180 Execute 3 1.05 1.07 0 0 0 1 181 Fetch 0 0.00 0.00 0 0 0 0 182 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 183 total 5 1.06 1.07 0 0 0 1 184 185 Misses in library cache during parse: 2 186 Misses in library cache during execute: 1 187 188 189 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS 190 191 call count cpu elapsed disk query current rows 192 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 193 Parse 3 0.00 0.00 0 0 0 0 194 Execute 10017 0.08 0.09 0 1 0 0 195 Fetch 17 0.01 0.01 265 92 0 1 196 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 197 total 10037 0.10 0.11 265 93 0 1 198 199 Misses in library cache during parse: 3 200 Misses in library cache during execute: 2 201 202 10004 user SQL statements in session. 203 16 internal SQL statements in session. 204 10020 SQL statements in session. 205 ******************************************************************************** 206 Trace file: /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_5865.trc 207 Trace file compatibility: 11.1.0.7 208 Sort options: default 209 210 1 session in tracefile. 211 10004 user SQL statements in trace file. 212 16 internal SQL statements in trace file. 213 10020 SQL statements in trace file. 214 6 unique SQL statements in trace file. 215 60178 lines in trace file. 216 70 elapsed seconds in trace file.
从上面的trace文件信息中可以得到：
执行时间：1.17+0.11=1.18秒
CPU时间：1.06+0.10=1.16秒
分析次数：5次
执行次数：10020次
上面是一个绑定变量的SQL的执行情况，下面是一个未绑定变量的SQL：
1 SQL> alter system flush shared_pool; 2 3 System altered. 4 5 SQL> alter system flush shared_pool; 6 7 System altered. 8 9 SQL> alter session set sql_trace = true; 10 11 Session altered. 12 13 SQL> begin 14 2 for x in 1..10000 loop 15 3 execute immediate 'select * from jack where object_id = '||x; 16 4 end loop; 17 5 end; 18 6 / 19 20 PL/SQL procedure successfully completed. 21 22 SQL> alter session set sql_trace = false; 23 24 Session altered. 25 26 SQL> @showtrace 27 28 trace_file_name 29 -------------------------------------------------------------------------------- 30 /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/yft2_ora_5959.trc 31 32 [oracle@node2 ~]$ tkprof /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_5959.trc out.txt sys=no 33 34 TKPROF: Release 11.2.0.1.0 - Development on Sat Feb 2 15:51:09 2013 35 36 Copyright (c) 1982, 2009, Oracle and/or its affiliates. All rights reserved. 37 38 ----trace文件信息----
39 SQL ID: f1d5d4pw64fw9 40 Plan Hash: 949574992 41 select * 42 from 43 jack where object_id = 1 44 45 46 call count cpu elapsed disk query current rows 47 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 48 Parse 1 0.00 0.01 0 2 0 0 49 Execute 1 0.00 0.00 0 0 0 0 50 Fetch 0 0.00 0.00 0 0 0 0 51 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 52 total 2 0.00 0.01 0 2 0 0 53 54 Misses in library cache during parse: 1 55 Optimizer mode: ALL_ROWS 56 Parsing user id: 87 (recursive depth: 1) 57 58 Rows Row Source Operation 59 ------- --------------------------------------------------- 60 0 TABLE ACCESS FULL JACK (cr=0 pr=0 pw=0 time=0 us cost=287 size=2484 card=12) 61 62 ******************************************************************************** 63 64 SQL ID: 60g8qk8gv4363 65 Plan Hash: 949574992 66 select * 67 from 68 jack where object_id = 2 69 70 71 call count cpu elapsed disk query current rows 72 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 73 Parse 1 0.00 0.00 0 1 0 0 74 Execute 1 0.00 0.00 0 0 0 0 75 Fetch 0 0.00 0.00 0 0 0 0 76 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 77 total 2 0.00 0.00 0 1 0 0 78 79 Misses in library cache during parse: 1 80 Optimizer mode: ALL_ROWS 81 Parsing user id: 87 (recursive depth: 1) 82 83 Rows Row Source Operation 84 ------- --------------------------------------------------- 85 0 TABLE ACCESS FULL JACK (cr=0 pr=0 pw=0 time=0 us cost=287 size=2484 card=12) 86 87 ******************************************************************************** 88 89 SQL ID: 1833z5rb1h922 90 Plan Hash: 949574992 91 select * 92 from 93 jack where object_id = 3 94 95 96 call count cpu elapsed disk query current rows 97 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 98 Parse 1 0.00 0.00 0 1 0 0 99 Execute 1 0.00 0.00 0 0 0 0 100 Fetch 0 0.00 0.00 0 0 0 0 101 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 102 total 2 0.00 0.00 0 1 0 0 103 104 Misses in library cache during parse: 1 105 Optimizer mode: ALL_ROWS 106 Parsing user id: 87 (recursive depth: 1) 107 108 Rows Row Source Operation 109 ------- --------------------------------------------------- 110 0 TABLE ACCESS FULL JACK (cr=0 pr=0 pw=0 time=0 us cost=287 size=2484 card=12) 111 112 ******************************************************************************** 113 114 。。。。。。 115 116 ******************************************************************************** 117 118 SQL ID: d8xbnmha6p8b7 119 Plan Hash: 949574992 120 select * 121 from 122 jack where object_id = 10000 123 124 125 call count cpu elapsed disk query current rows 126 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 127 Parse 1 0.00 0.00 0 1 0 0 128 Execute 1 0.00 0.00 0 0 0 0 129 Fetch 0 0.00 0.00 0 0 0 0 130 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 131 total 2 0.00 0.00 0 1 0 0 132 133 Misses in library cache during parse: 1 134 Optimizer mode: ALL_ROWS 135 Parsing user id: 87 (recursive depth: 1) 136 137 Rows Row Source Operation 138 ------- --------------------------------------------------- 139 0 TABLE ACCESS FULL JACK (cr=0 pr=0 pw=0 time=0 us cost=287 size=2484 card=12) 140 141 ******************************************************************************** 142 143 SQL ID: 988n7wn97ptgf 144 Plan Hash: 0 145 alter session set sql_trace = false 146 147 148 call count cpu elapsed disk query current rows 149 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 150 Parse 1 0.00 0.00 0 0 0 0 151 Execute 1 0.01 0.01 0 0 0 0 152 Fetch 0 0.00 0.00 0 0 0 0 153 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 154 total 2 0.01 0.01 0 0 0 0 155 156 Misses in library cache during parse: 1 157 Optimizer mode: ALL_ROWS 158 Parsing user id: 87 159 160 161 162 ******************************************************************************** 163 164 OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS 165 166 call count cpu elapsed disk query current rows 167 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 168 Parse 2 0.00 0.00 0 0 0 0 169 Execute 3 2.08 2.21 0 257 0 1 170 Fetch 0 0.00 0.00 0 0 0 0 171 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 172 total 5 2.09 2.22 0 257 0 1 173 174 Misses in library cache during parse: 2 175 Misses in library cache during execute: 1 176 177 178 OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS 179 180 call count cpu elapsed disk query current rows 181 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 182 Parse 20073 11.90 12.28 0 10001 0 0 183 Execute 20141 0.54 0.59 0 0 0 0 184 Fetch 10506 12.23 12.69 1 600411 0 11215 185 ------- ------ -------- ---------- ---------- ---------- ---------- ---------- 186 total 50720 24.69 25.58 1 610412 0 11215 187 188 Misses in library cache during parse: 10020 189 Misses in library cache during execute: 20 190 191 20003 user SQL statements in session. 192 141 internal SQL statements in session. 193 20144 SQL statements in session. 194 ******************************************************************************** 195 Trace file: /u01/app/oracle/diag/rdbms/yft2/YFT2/trace/YFT2_ora_5959.trc 196 Trace file compatibility: 11.1.0.7 197 Sort options: default 198 199 1 session in tracefile. 200 20003 user SQL statements in trace file. 201 141 internal SQL statements in trace file. 202 20144 SQL statements in trace file. 203 10020 unique SQL statements in trace file. 204 161613 lines in trace file. 205 83 elapsed seconds in trace file.
上面的trace文件中得到：
执行时间：2.22+25.58=27.8秒
CPU时间：2.09+24.69=26.78秒
分析次数：20075次
执行次数：20144次
两种情况对比的结果显示，绑定变量SQL的资源消耗要远远少于未绑定变量SQL的资源消耗，SQL执行的次数越多，这种差距将越明显，未绑定变量SQL的资源主要消耗在产生的递归SQL上，这些SQL主要是在对SQL语句做硬分析时使用的。
如果我们让所有的用户在数据库操作时传给Oracle的都是这样一个由变量代替常量的SQL，那么Oracle只需要硬分析最早的一条SQL就可以了，其它后续的SQL都可以直接使用第一条SQL的执行计划来执行，只在SQL执行的时候，Oracle再使用每个用户的实际谓词条件来替换变量，这样就省却了前面很耗资源的硬分析过程。
试想，当一个数据库有成千上万或者更多的用户同时执行这样的SQL，而Oracle只做一次硬分析，之后只对每个SQL做执行操作，那必将极大地减轻数据库资源开销。
这就是变量绑定的由来，它并不神秘，不过是拿一个变量来代替谓词常量，让Oracle每次对用户发来的SQL做Hash运算时，运算出的结果都是同样的Hash值，于是将所有的用户发送的SQL看作是同一个SQL来对待而已。
最后可以看一下哪种环境下适合绑定变量：《关于为什么说OLTP必须要求变量绑定而OLAP不应该绑定变量的原因》
806
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
