在看关于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 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 vsqlareawheresqltextlike′insertintotHASHVALUESQLIDCHILDLATCHVERSIONCOUNTSQLTEXTPARSECALLS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−9439841877n25q8hw483jv01insertintotvalues(3453492,′PACKBONUS′)215397082836u2u7h5dwc5bv01insertintotvalues(3474621,′TESTNEWPARTITION′)232302764146zy5v5b08n6ty01insertintotvalues(3342844,′TEST′)219709384507hqma3durn8kk01insertintotvalues(3019103,′TESTNUMBER′)234916421288y6pdnv81wfsh01insertintotvalues(3031248,′TT′)2314325457002hhxyfxpnhja01insertintotvalues(3474587,′AR9TEMPPAYMENTDISCOUNT′)26292971846avss4hks4n1001insertintotvalues(2941004,′AC1AUDITBALANCE′)2939020580175gnjwvzhn9401insertintotvalues(3474584,′SYSIL0003474579C00004′)23278960812ab1f80z8p3801insertintotvalues(2940994,′PM9CRDTLMTNOTIFICATIONPK′)22950453625gfyn7xkrxsqbt01insertintotvalues(3474618,′TESTPAR1IX′)23132513055ay90xvyxbcqsz01insertintotvalues(3448758,′TESTLINK′)2300275362552xxfcytgnskt01insertintotvalues(2940992,′PARTITIONTEST′)224096539664g8u1qy7u0tqf01insertintotvalues(3474585,′APIDUPLICATION1IX′)21024487769g7k53xwyj0wat01insertintotvalues(2940996,′AC1AUDITBALANCE′)24479068674q0x9pcdb511m01insertintotvalues(3474619,′TESTNEWPARTITION′)2756639500qdtj1c2852kf01insertintotvalues(2940995,′AC1AUDITBALANCE′)236136915271pda96bbq93n701insertintotvalues(3484037,′TRANSTEST′)23271204252anttszb1gp7cw01insertintotvalues(3330648,′TESTSEQ′)22154079735g11kv860699gr01insertintotvalues(3107910,′SYNOTEST′)24808148663k5y5k4faj9sk01insertintotvalues(3365025,′TESTDATA′)217527388112fmxmypn7jazv01insertintotvalues(3474608,′TESTIDSEQ′)2167558186787c2344ztg1a01insertintotvalues(3453520,′DATA2′)22399715003bhmwk6k7hjgpv01insertintotvalues(3441190,′SYNCSEQSUG′)215675447450160fa5fqxpd901insertintotvalues(3474578,′APIIDSEQ′)235357246643b9vn979bxs3s01insertintotvalues(2941000,′AC1AUDITBALANCE′)22402608957fbzc3h67m9ttx01insertintotvalues(3527048,′T′)21587210435g08r2sxg9pu6301insertintotvalues(3031347,′SUBSCRIBERHISTORY′)21723657026db6r539mbtuu201insertintotvalues(3474616,′TESTPAR1IX′)22675633632fuvvj12grpvg001insertintotvalues(2940999,′AC1AUDITBALANCE′)218637774807x590y5rjdz6801insertintotvalues(3453493,′PACKBONUS′)21719731522fyfd8pxm821a201insertintotvalues(3474623,′TESTNEWPARTITION′)211192911734mu2kr91bf1t501insertintotvalues(3449163,′DATA′)237636710967q4rcbbh5a41s01insertintotvalues(3001889,′AAA′)220056047542j0q35xvsq6ck01insertintotvalues(2940991,′PARTITIONTEST′)2157620877954f8xh4qa6nd01insertintotvalues(2941001,′AC1AUDITBALANCE′)233627253080d3nx2m46y7dw01insertintotvalues(3474615,′TESTNEWPARTITION′)2638005548gtwd2p0m0fc9c01insertintotvalues(3474611,′TESTPAR1IX′)21649883024dccsqb5j5fdwh01insertintotvalues(3031333,′TRUE9SERVICEAGRPARM1SQ′)218690356599tntgntrqff4b01insertintotvalues(3474586,′APIDUPLICATION1IX′)21159532107n22ak03fkmju01insertintotvalues(3474581,′SYSLOB0003474579C00004′)21097290748082nkh90qfnzw01insertintotvalues(3347713,′TESTFULL′)235715374534338ph3af2pjd01insertintotvalues(3401344,′CL1PROPERTIES′)233301039805ys9uqr37uqpc01insertintotvalues(3107900,′T1′)2604726191d1w7wtck0qsxg01insertintotvalues(3001893,′AAAA′)218375894859g4wrrpqsfszd01insertintotvalues(2941006,′AC1AUDITBALANCE′)227477545979nm6xgqjwfu3501insertintotvalues(2940998,′AC1AUDITBALANCE′)2157715068158suw71g02u6t01insertintotvalues(3474610,′TESTNEWPARTITION′)21350004733c9dz2xj87fvzx01insertintotvalues(3474580,′APIDUPLICATION′)21446605926gnkbn55b3kx3601insertintotvalues(2941003,′AC1AUDITBALANCE′)24227169095124djfmxzayu701insertintotvalues(3474620,′TESTPAR1IX′)210715943a74cufs0a70t701insertintotvalues(3027329,′TESTTEST′)234811092957y69jfb7rv0tg01insertintotvalues(3474609,′TESTNEWPARTITION′)210747588553vqvghp00z16701insertintotvalues(3474612,′TESTPAR1IX′)276478061774xnxgwqtb82901insertintotvalues(3474588,′AR9TEMPPAYMENTDISCOUNT1IX′)29647990972ajavkwws3amt01insertintotvalues(2940993,′PM9CRDTLMTNOTIFICATIONPK′)227046509219nknfbqhmbcp901insertintotvalues(3474579,′APIDUPLICATION′)21580054001da3hqx5g2vdgj01insertintotvalues(3474624,′TESTPAR1IX′)240757964019tqd2w3tfzdxj01insertintotvalues(2941002,′AC1AUDITBALANCE′)2232034972299h2cry54vfhu01insertintotvalues(2940997,′AC1AUDITBALANCE′)238310891845ag5kr7k5mk1001insertintotvalues(3474582,′SYSLOB0003474579C00004$') 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语句的过量硬解析。
简单用跟一个实例来说明。
我们先清空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 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 vsqlareawheresqltextlike′insertintotHASHVALUESQLIDCHILDLATCHVERSIONCOUNTSQLTEXTPARSECALLS−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−−9439841877n25q8hw483jv01insertintotvalues(3453492,′PACKBONUS′)215397082836u2u7h5dwc5bv01insertintotvalues(3474621,′TESTNEWPARTITION′)232302764146zy5v5b08n6ty01insertintotvalues(3342844,′TEST′)219709384507hqma3durn8kk01insertintotvalues(3019103,′TESTNUMBER′)234916421288y6pdnv81wfsh01insertintotvalues(3031248,′TT′)2314325457002hhxyfxpnhja01insertintotvalues(3474587,′AR9TEMPPAYMENTDISCOUNT′)26292971846avss4hks4n1001insertintotvalues(2941004,′AC1AUDITBALANCE′)2939020580175gnjwvzhn9401insertintotvalues(3474584,′SYSIL0003474579C00004′)23278960812ab1f80z8p3801insertintotvalues(2940994,′PM9CRDTLMTNOTIFICATIONPK′)22950453625gfyn7xkrxsqbt01insertintotvalues(3474618,′TESTPAR1IX′)23132513055ay90xvyxbcqsz01insertintotvalues(3448758,′TESTLINK′)2300275362552xxfcytgnskt01insertintotvalues(2940992,′PARTITIONTEST′)224096539664g8u1qy7u0tqf01insertintotvalues(3474585,′APIDUPLICATION1IX′)21024487769g7k53xwyj0wat01insertintotvalues(2940996,′AC1AUDITBALANCE′)24479068674q0x9pcdb511m01insertintotvalues(3474619,′TESTNEWPARTITION′)2756639500qdtj1c2852kf01insertintotvalues(2940995,′AC1AUDITBALANCE′)236136915271pda96bbq93n701insertintotvalues(3484037,′TRANSTEST′)23271204252anttszb1gp7cw01insertintotvalues(3330648,′TESTSEQ′)22154079735g11kv860699gr01insertintotvalues(3107910,′SYNOTEST′)24808148663k5y5k4faj9sk01insertintotvalues(3365025,′TESTDATA′)217527388112fmxmypn7jazv01insertintotvalues(3474608,′TESTIDSEQ′)2167558186787c2344ztg1a01insertintotvalues(3453520,′DATA2′)22399715003bhmwk6k7hjgpv01insertintotvalues(3441190,′SYNCSEQSUG′)215675447450160fa5fqxpd901insertintotvalues(3474578,′APIIDSEQ′)235357246643b9vn979bxs3s01insertintotvalues(2941000,′AC1AUDITBALANCE′)22402608957fbzc3h67m9ttx01insertintotvalues(3527048,′T′)21587210435g08r2sxg9pu6301insertintotvalues(3031347,′SUBSCRIBERHISTORY′)21723657026db6r539mbtuu201insertintotvalues(3474616,′TESTPAR1IX′)22675633632fuvvj12grpvg001insertintotvalues(2940999,′AC1AUDITBALANCE′)218637774807x590y5rjdz6801insertintotvalues(3453493,′PACKBONUS′)21719731522fyfd8pxm821a201insertintotvalues(3474623,′TESTNEWPARTITION′)211192911734mu2kr91bf1t501insertintotvalues(3449163,′DATA′)237636710967q4rcbbh5a41s01insertintotvalues(3001889,′AAA′)220056047542j0q35xvsq6ck01insertintotvalues(2940991,′PARTITIONTEST′)2157620877954f8xh4qa6nd01insertintotvalues(2941001,′AC1AUDITBALANCE′)233627253080d3nx2m46y7dw01insertintotvalues(3474615,′TESTNEWPARTITION′)2638005548gtwd2p0m0fc9c01insertintotvalues(3474611,′TESTPAR1IX′)21649883024dccsqb5j5fdwh01insertintotvalues(3031333,′TRUE9SERVICEAGRPARM1SQ′)218690356599tntgntrqff4b01insertintotvalues(3474586,′APIDUPLICATION1IX′)21159532107n22ak03fkmju01insertintotvalues(3474581,′SYSLOB0003474579C00004′)21097290748082nkh90qfnzw01insertintotvalues(3347713,′TESTFULL′)235715374534338ph3af2pjd01insertintotvalues(3401344,′CL1PROPERTIES′)233301039805ys9uqr37uqpc01insertintotvalues(3107900,′T1′)2604726191d1w7wtck0qsxg01insertintotvalues(3001893,′AAAA′)218375894859g4wrrpqsfszd01insertintotvalues(2941006,′AC1AUDITBALANCE′)227477545979nm6xgqjwfu3501insertintotvalues(2940998,′AC1AUDITBALANCE′)2157715068158suw71g02u6t01insertintotvalues(3474610,′TESTNEWPARTITION′)21350004733c9dz2xj87fvzx01insertintotvalues(3474580,′APIDUPLICATION′)21446605926gnkbn55b3kx3601insertintotvalues(2941003,′AC1AUDITBALANCE′)24227169095124djfmxzayu701insertintotvalues(3474620,′TESTPAR1IX′)210715943a74cufs0a70t701insertintotvalues(3027329,′TESTTEST′)234811092957y69jfb7rv0tg01insertintotvalues(3474609,′TESTNEWPARTITION′)210747588553vqvghp00z16701insertintotvalues(3474612,′TESTPAR1IX′)276478061774xnxgwqtb82901insertintotvalues(3474588,′AR9TEMPPAYMENTDISCOUNT1IX′)29647990972ajavkwws3amt01insertintotvalues(2940993,′PM9CRDTLMTNOTIFICATIONPK′)227046509219nknfbqhmbcp901insertintotvalues(3474579,′APIDUPLICATION′)21580054001da3hqx5g2vdgj01insertintotvalues(3474624,′TESTPAR1IX′)240757964019tqd2w3tfzdxj01insertintotvalues(2941002,′AC1AUDITBALANCE′)2232034972299h2cry54vfhu01insertintotvalues(2940997,′AC1AUDITBALANCE′)238310891845ag5kr7k5mk1001insertintotvalues(3474582,′SYSLOB0003474579C00004$') 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语句的过量硬解析。