关于pl/sql中的绑定变量(r3笔记第73天)

在看关于shared pool的文档时，必定会提到绑定变量，也能够通过几个简单的例子对绑定变量带来影响有深刻的认识，但是在工作中，可能有时候我们就忘了绑定变量的影响了，其实有时候一个很小的变动就会导致性能几十几百倍的提升。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>declarecursor test_cur is select object_id,object_name from t ;beginfor i in test_cur loopinsert 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 poolSQL>alter system flush shared_pool;运行pl/sql代码如下。我们对insert语句中的两个字段值都进行了拼接。SQL>declarecursor test_cur is select object_id,object_name from t ;beginfor 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') 21539708283 6u2u7h5dwc5bv 0 1 insert into t values(3474621,'TEST_NEW_PARTITION') 23230276414 6zy5v5b08n6ty 0 1 insert into t values(3342844,'TEST') 21970938450 7hqma3durn8kk 0 1 insert into t values(3019103,'TEST_NUMBER') 23491642128 8y6pdnv81wfsh 0 1 insert into t values(3031248,'TT') 23143254570 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') 22950453625 gfyn7xkrxsqbt 0 1 insert into t values(3474618,'TEST_PAR_1IX') 23132513055 ay90xvyxbcqsz 0 1 insert into t values(3448758,'TEST_LINK') 23002753625 52xxfcytgnskt 0 1 insert into t values(2940992,'PARTITION_TEST') 22409653966 4g8u1qy7u0tqf 0 1 insert into t values(3474585,'API_DUPLICATION_1IX') 21024487769 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') 23613691527 1pda96bbq93n7 0 1 insert into t values(3484037,'TRANS_TEST') 23271204252 anttszb1gp7cw 0 1 insert into t values(3330648,'TEST_SEQ') 22154079735 g11kv860699gr 0 1 insert into t values(3107910,'SYNO_TEST') 2 480814866 3k5y5k4faj9sk 0 1 insert into t values(3365025,'TEST_DATA') 21752738811 2fmxmypn7jazv 0 1 insert into t values(3474608,'TEST_ID_SEQ') 2 167558186 787c2344ztg1a 0 1 insert into t values(3453520,'DATA2') 22399715003 bhmwk6k7hjgpv 0 1 insert into t values(3441190,'SYNC_SEQ_SUG') 21567544745 0160fa5fqxpd9 0 1 insert into t values(3474578,'API_ID_SEQ') 23535724664 3b9vn979bxs3s 0 1 insert into t values(2941000,'AC1_AUDIT_BALANCE') 22402608957 fbzc3h67m9ttx 0 1 insert into t values(3527048,'T') 21587210435 g08r2sxg9pu63 0 1 insert into t values(3031347,'SUBSCRIBER_HISTORY') 21723657026 db6r539mbtuu2 0 1 insert into t values(3474616,'TEST_PAR_1IX') 22675633632 fuvvj12grpvg0 0 1 insert into t values(2940999,'AC1_AUDIT_BALANCE') 21863777480 7x590y5rjdz68 0 1 insert into t values(3453493,'PACK_BONUS') 21719731522 fyfd8pxm821a2 0 1 insert into t values(3474623,'TEST_NEW_PARTITION') 21119291173 4mu2kr91bf1t5 0 1 insert into t values(3449163,'DATA') 23763671096 7q4rcbbh5a41s 0 1 insert into t values(3001889,'AAA') 22005604754 2j0q35xvsq6ck 0 1 insert into t values(2940991,'PARTITION_TEST') 2 157620877 954f8xh4qa6nd 0 1 insert into t values(2941001,'AC1_AUDIT_BALANCE') 23362725308 0d3nx2m46y7dw 0 1 insert into t values(3474615,'TEST_NEW_PARTITION') 2 638005548 gtwd2p0m0fc9c 0 1 insert into t values(3474611,'TEST_PAR_1IX') 21649883024 dccsqb5j5fdwh 0 1 insert into t values(3031333,'TRUE9_SERVICE_AGR_PARM_1SQ') 21869035659 9tntgntrqff4b 0 1 insert into t values(3474586,'API_DUPLICATION_1IX') 2 115953210 7n22ak03fkmju 0 1 insert into t values(3474581,'SYS_LOB0003474579C00004$$') 21097290748 082nkh90qfnzw 0 1 insert into t values(3347713,'TEST_FULL') 23571537453 4338ph3af2pjd 0 1 insert into t values(3401344,'CL1_PROPERTIES') 23330103980 5ys9uqr37uqpc 0 1 insert into t values(3107900,'T1') 2 604726191 d1w7wtck0qsxg 0 1 insert into t values(3001893,'AAAA') 21837589485 9g4wrrpqsfszd 0 1 insert into t values(2941006,'AC1_AUDIT_BALANCE') 22747754597 9nm6xgqjwfu35 0 1 insert into t values(2940998,'AC1_AUDIT_BALANCE') 21577150681 58suw71g02u6t 0 1 insert into t values(3474610,'TEST_NEW_PARTITION') 21350004733 c9dz2xj87fvzx 0 1 insert into t values(3474580,'API_DUPLICATION') 21446605926 gnkbn55b3kx36 0 1 insert into t values(2941003,'AC1_AUDIT_BALANCE') 24227169095 124djfmxzayu7 0 1 insert into t values(3474620,'TEST_PAR_1IX') 2 10715943 a74cufs0a70t7 0 1 insert into t values(3027329,'TESTTEST') 23481109295 7y69jfb7rv0tg 0 1 insert into t values(3474609,'TEST_NEW_PARTITION') 21074758855 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') 22704650921 9nknfbqhmbcp9 0 1 insert into t values(3474579,'API_DUPLICATION') 21580054001 da3hqx5g2vdgj 0 1 insert into t values(3474624,'TEST_PAR_1IX') 24075796401 9tqd2w3tfzdxj 0 1 insert into t values(2941002,'AC1_AUDIT_BALANCE') 22320349722 99h2cry54vfhu 0 1 insert into t values(2940997,'AC1_AUDIT_BALANCE') 23831089184 5ag5kr7k5mk10 0 1 insert into t values(3474582,'SYS_LOB0003474579C00004$$') 21061414023 bg9rcmwzn7t47 0 1 insert into t values(3500022,'TEST_PK') 2 41805945 5dh987817vu3t 0 1 insert into t values(2941005,'AC1_AUDIT_BALANCE') 23721390643 2xr80d7fwzujm 0 1 insert into t values(3474617,'TEST_NEW_PARTITION') 2 944762573 6bfnpn4w4zvqd 0 1 insert into t values(3347714,'TEST_PARTIAL') 22404118812 344xnfa7nrw8w 0 1 insert into t values(3474622,'TEST_PAR_1IX') 21170994238 125j6wd2wrx1y 0 1 insert into t values(3330435,'TEST_TEST') 266 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(*)---------- 4Elapsed: 00:00:00.00所以这个时候对于有重复值的sql语句，整体上走了66次硬解析，然后对于重复的记录行，因为重复记录拼接处的sql语句完全相同。所以做了2次软解析。我们来看看第3个例子。清空shared poolSQL>alter system flush shared_pool;运行如下的Pl/sqlSQL>declarecursor test_cur is select object_id,object_name from t ;beginfor i in test_cur loopexecute 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=528SQL> 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) 264Elapsed: 00:00:00.03SQL> select count(*)from t; COUNT(*)---------- 528Elapsed: 00:00:00.00所以在平时的工作中如果需要使用pl/sql的时候，可以根据具体的情况来防止sql语句的过量硬解析。