在看關於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語句的過量硬解析。

關於pl/sql中的繫結變數

相關文章