关于pl/sql中的绑定变量(r3笔记第73天)
关于pl/sql中的绑定变量(r3笔记第73天)
在看关于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语句的过量硬解析。