关于pl/sql中的绑定变量

在看关于shared pool的文档时，必定会提到绑定变量，也能够通过几个简单的例子对绑定变量带来影响有深刻的认识，但是在工作中，可能有时候我们就忘了绑定变量的影响了，其实有时候一个很小的变动就会导致性能几十几百倍的提升。
简单用跟一个实例来说明。

我们先清空shared pool,排除其它的运行语句带来的影响。
SQL>alter system flush shared_pool;

然后我们创建一个表t，使用cats的方式创建，只有2个字段。
SQL>create table t as select object_id,object_name from user_objects where object_id is not null and rownum<100;
Table created.

然后我们使用如下的pl/sql来尝试从表t中取出数据然后重新插入t中。
SQL>declare
cursor test_cur is  select object_id,object_name from t ;
begin
for i in test_cur loop
insert into t values(i.object_id,i.object_name);
end loop;
commit;
end;
/
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.04

运行完成之后，我们来看看sql语句的执行情况。
SQL>select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like ‘INSERT%‘
HASH_VALUE SQL_ID                                  CHILD_LATCH VERSION_COUNT SQL_TEXT                                                     PARSE_CALLS
---------- --------------------------------------- ----------- ------------- ------------------------------------------------------------ -----------
1681598159 c0rddkpk3q9qg                                     0             1 INSERT INTO T VALUES(:B2 ,:B1 )                                       66

可以看到使用到了绑定变量，没有重复的进行硬解析。生成的sql_id只有一个。至于parse_calls是66，我们可以断定表t中应该有66*2=132条数据。因为pl.sql是基于66条数据的基础上做了一次insert.
SQL> select count(*)from t;
  COUNT(*)
----------
       132

然后我们来看看使用execute immediate来拼接sql语句的时候，绑定变量的情况。
清空shared pool
SQL>alter system flush shared_pool;
运行pl/sql代码如下。我们对insert语句中的两个字段值都进行了拼接。
SQL>declare
cursor test_cur is  select object_id,object_name from t ;
begin
for i in test_cur loop
--dbms_output.put_line( ‘insert into t  values(‘||i.object_id||‘,‘||chr(39)||i.object_name||chr(39)||‘)‘);
execute immediate ‘insert into t  values(‘||i.object_id||‘,‘||chr(39)||i.object_name||chr(39)||‘)‘;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09

我们来查看一下sql语句的执行情况。特别注意的是sql_text中的insert是小写。而上面的例子里面insert是大写。
这条语句进行了大量的硬解析。
SQL>select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like ‘insert into t%‘
HASH_VALUE SQL_ID                                  CHILD_LATCH VERSION_COUNT SQL_TEXT                                                               PARSE_CALLS
---------- --------------------------------------- ----------- ------------- ---------------------------------------------------------------------- -----------
 943984187 7n25q8hw483jv                                     0             1 insert into t  values(3453492,‘PACK_BONUS‘)                                      2
1539708283 6u2u7h5dwc5bv                                     0             1 insert into t  values(3474621,‘TEST_NEW_PARTITION‘)                              2
3230276414 6zy5v5b08n6ty                                     0             1 insert into t  values(3342844,‘TEST‘)                                            2
1970938450 7hqma3durn8kk                                     0             1 insert into t  values(3019103,‘TEST_NUMBER‘)                                     2
3491642128 8y6pdnv81wfsh                                     0             1 insert into t  values(3031248,‘TT‘)                                              2
3143254570 02hhxyfxpnhja                                     0             1 insert into t  values(3474587,‘AR9_TEMP_PAYMENT_DISCOUNT‘)                       2
 629297184 6avss4hks4n10                                     0             1 insert into t  values(2941004,‘AC1_AUDIT_BALANCE‘)                               2
 939020580 175gnjwvzhn94                                     0             1 insert into t  values(3474584,‘SYS_IL0003474579C00004$$‘)                        2
  32789608 12ab1f80z8p38                                     0             1 insert into t  values(2940994,‘PM9_CRDT_LMT_NOTIFICATION_PK‘)                    2
2950453625 gfyn7xkrxsqbt                                     0             1 insert into t  values(3474618,‘TEST_PAR_1IX‘)                                    2
3132513055 ay90xvyxbcqsz                                     0             1 insert into t  values(3448758,‘TEST_LINK‘)                                       2
3002753625 52xxfcytgnskt                                     0             1 insert into t  values(2940992,‘PARTITION_TEST‘)                                  2
2409653966 4g8u1qy7u0tqf                                     0             1 insert into t  values(3474585,‘API_DUPLICATION_1IX‘)                             2
1024487769 g7k53xwyj0wat                                     0             1 insert into t  values(2940996,‘AC1_AUDIT_BALANCE‘)                               2
 447906867 4q0x9pcdb511m                                     0             1 insert into t  values(3474619,‘TEST_NEW_PARTITION‘)                              2
  75663950 0qdtj1c2852kf                                     0             1 insert into t  values(2940995,‘AC1_AUDIT_BALANCE‘)                               2
3613691527 1pda96bbq93n7                                     0             1 insert into t  values(3484037,‘TRANS_TEST‘)                                      2
3271204252 anttszb1gp7cw                                     0             1 insert into t  values(3330648,‘TEST_SEQ‘)                                        2
2154079735 g11kv860699gr                                     0             1 insert into t  values(3107910,‘SYNO_TEST‘)                                       2
 480814866 3k5y5k4faj9sk                                     0             1 insert into t  values(3365025,‘TEST_DATA‘)                                       2
1752738811 2fmxmypn7jazv                                     0             1 insert into t  values(3474608,‘TEST_ID_SEQ‘)                                     2
 167558186 787c2344ztg1a                                     0             1 insert into t  values(3453520,‘DATA2‘)                                           2
2399715003 bhmwk6k7hjgpv                                     0             1 insert into t  values(3441190,‘SYNC_SEQ_SUG‘)                                    2
1567544745 0160fa5fqxpd9                                     0             1 insert into t  values(3474578,‘API_ID_SEQ‘)                                      2
3535724664 3b9vn979bxs3s                                     0             1 insert into t  values(2941000,‘AC1_AUDIT_BALANCE‘)                               2
2402608957 fbzc3h67m9ttx                                     0             1 insert into t  values(3527048,‘T‘)                                               2
1587210435 g08r2sxg9pu63                                     0             1 insert into t  values(3031347,‘SUBSCRIBER_HISTORY‘)                              2
1723657026 db6r539mbtuu2                                     0             1 insert into t  values(3474616,‘TEST_PAR_1IX‘)                                    2
2675633632 fuvvj12grpvg0                                     0             1 insert into t  values(2940999,‘AC1_AUDIT_BALANCE‘)                               2
1863777480 7x590y5rjdz68                                     0             1 insert into t  values(3453493,‘PACK_BONUS‘)                                      2
1719731522 fyfd8pxm821a2                                     0             1 insert into t  values(3474623,‘TEST_NEW_PARTITION‘)                              2
1119291173 4mu2kr91bf1t5                                     0             1 insert into t  values(3449163,‘DATA‘)                                            2
3763671096 7q4rcbbh5a41s                                     0             1 insert into t  values(3001889,‘AAA‘)                                             2
2005604754 2j0q35xvsq6ck                                     0             1 insert into t  values(2940991,‘PARTITION_TEST‘)                                  2
 157620877 954f8xh4qa6nd                                     0             1 insert into t  values(2941001,‘AC1_AUDIT_BALANCE‘)                               2
3362725308 0d3nx2m46y7dw                                     0             1 insert into t  values(3474615,‘TEST_NEW_PARTITION‘)                              2
 638005548 gtwd2p0m0fc9c                                     0             1 insert into t  values(3474611,‘TEST_PAR_1IX‘)                                    2
1649883024 dccsqb5j5fdwh                                     0             1 insert into t  values(3031333,‘TRUE9_SERVICE_AGR_PARM_1SQ‘)                      2
1869035659 9tntgntrqff4b                                     0             1 insert into t  values(3474586,‘API_DUPLICATION_1IX‘)                             2
 115953210 7n22ak03fkmju                                     0             1 insert into t  values(3474581,‘SYS_LOB0003474579C00004$$‘)                       2
1097290748 082nkh90qfnzw                                     0             1 insert into t  values(3347713,‘TEST_FULL‘)                                       2
3571537453 4338ph3af2pjd                                     0             1 insert into t  values(3401344,‘CL1_PROPERTIES‘)                                  2
3330103980 5ys9uqr37uqpc                                     0             1 insert into t  values(3107900,‘T1‘)                                              2
 604726191 d1w7wtck0qsxg                                     0             1 insert into t  values(3001893,‘AAAA‘)                                            2
1837589485 9g4wrrpqsfszd                                     0             1 insert into t  values(2941006,‘AC1_AUDIT_BALANCE‘)                               2
2747754597 9nm6xgqjwfu35                                     0             1 insert into t  values(2940998,‘AC1_AUDIT_BALANCE‘)                               2
1577150681 58suw71g02u6t                                     0             1 insert into t  values(3474610,‘TEST_NEW_PARTITION‘)                              2
1350004733 c9dz2xj87fvzx                                     0             1 insert into t  values(3474580,‘API_DUPLICATION‘)                                 2
1446605926 gnkbn55b3kx36                                     0             1 insert into t  values(2941003,‘AC1_AUDIT_BALANCE‘)                               2
4227169095 124djfmxzayu7                                     0             1 insert into t  values(3474620,‘TEST_PAR_1IX‘)                                    2
  10715943 a74cufs0a70t7                                     0             1 insert into t  values(3027329,‘TESTTEST‘)                                        2
3481109295 7y69jfb7rv0tg                                     0             1 insert into t  values(3474609,‘TEST_NEW_PARTITION‘)                              2
1074758855 3vqvghp00z167                                     0             1 insert into t  values(3474612,‘TEST_PAR_1IX‘)                                    2
 764780617 74xnxgwqtb829                                     0             1 insert into t  values(3474588,‘AR9_TEMP_PAYMENT_DISCOUNT_1IX‘)                   2
 964799097 2ajavkwws3amt                                     0             1 insert into t  values(2940993,‘PM9_CRDT_LMT_NOTIFICATION_PK‘)                    2
2704650921 9nknfbqhmbcp9                                     0             1 insert into t  values(3474579,‘API_DUPLICATION‘)                                 2
1580054001 da3hqx5g2vdgj                                     0             1 insert into t  values(3474624,‘TEST_PAR_1IX‘)                                    2
4075796401 9tqd2w3tfzdxj                                     0             1 insert into t  values(2941002,‘AC1_AUDIT_BALANCE‘)                               2
2320349722 99h2cry54vfhu                                     0             1 insert into t  values(2940997,‘AC1_AUDIT_BALANCE‘)                               2
3831089184 5ag5kr7k5mk10                                     0             1 insert into t  values(3474582,‘SYS_LOB0003474579C00004$$‘)                       2
1061414023 bg9rcmwzn7t47                                     0             1 insert into t  values(3500022,‘TEST_PK‘)                                         2
  41805945 5dh987817vu3t                                     0             1 insert into t  values(2941005,‘AC1_AUDIT_BALANCE‘)                               2
3721390643 2xr80d7fwzujm                                     0             1 insert into t  values(3474617,‘TEST_NEW_PARTITION‘)                              2
 944762573 6bfnpn4w4zvqd                                     0             1 insert into t  values(3347714,‘TEST_PARTIAL‘)                                    2
2404118812 344xnfa7nrw8w                                     0             1 insert into t  values(3474622,‘TEST_PAR_1IX‘)                                    2
1170994238 125j6wd2wrx1y                                     0             1 insert into t  values(3330435,‘TEST_TEST‘)                                       2

66 rows selected.
Elapsed: 00:00:00.04

对于上面的结果。可以这么来看，在插入数据前，已经有132条数据了，但是运行Pl/sql之后为什么只有66条硬解析的记录呢？
我们抽取一条数据来简单验证一下。我们抽取最后一条记录。可以看到在表t中重复的记录有4条。这样的话，可以判定在插入之前已经有2条是重复的了，然后又插入了2条。
SQL> select count(*)from t where object_id=3330435;
  COUNT(*)
----------
         4
Elapsed: 00:00:00.00
所以这个时候对于有重复值的sql语句，整体上走了66次硬解析，然后对于重复的记录行，因为重复记录拼接处的sql语句完全相同。所以做了2次软解析。

我们来看看第3个例子。
清空shared pool
SQL>alter system flush shared_pool;

运行如下的Pl/sql
SQL>declare
cursor test_cur is  select object_id,object_name from t ;
begin
for i in test_cur loop
execute immediate ‘insert into t  values(:a,:b)‘  using i.object_id,i.object_name;
end loop;
commit;
end;
/

PL/SQL procedure successfully completed.
Elapsed: 00:00:00.11
查看sql语句的执行情况。可以看到只有1条记录，毫无疑问是走了软解析。对于软解析的次数264，我们可以反推出表t中在数据插入之后的记录应该是264*2=528
SQL> select hash_value,sql_id,child_latch,version_count,sql_text,parse_calls from v$sqlarea where sql_text like ‘insert into t%‘
HASH_VALUE SQL_ID                                  CHILD_LATCH VERSION_COUNT SQL_TEXT                                                               PARSE_CALLS
---------- --------------------------------------- ----------- ------------- ---------------------------------------------------------------------- -----------
3816494843 89bfm0gjrq5rv                                     0             1 insert into t  values(:a,:b)                                                   264

Elapsed: 00:00:00.03
SQL> select count(*)from t;
  COUNT(*)
----------
       528
Elapsed: 00:00:00.00

所以在平时的工作中如果需要使用pl/sql的时候，可以根据具体的情况来防止sql语句的过量硬解析。