关于pl/sql中的绑定变量

本文涉及的产品
云解析 DNS,旗舰版 1个月
全局流量管理 GTM,标准版 1个月
公共DNS(含HTTPDNS解析),每月1000万次HTTP解析
简介: 在看关于shared pool的文档时,必定会提到绑定变量,也能够通过几个简单的例子对绑定变量带来影响有深刻的认识,但是在工作中,可能有时候我们就忘了绑定变量的影响了,其实有时候一个很小的变动就会导致性能几十几百倍的提升。
在看关于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 vsqlareawheresqltextlikeinsertintotHASHVALUESQLIDCHILDLATCHVERSIONCOUNTSQLTEXTPARSECALLS9439841877n25q8hw483jv01insertintotvalues(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语句的过量硬解析。
目录
打赏
0
0
0
0
16
分享
相关文章
|
9天前
|
SQL
【YashanDB知识库】like 变量的SQL语句应用程序执行效率低与yasql执行效率高
【YashanDB知识库】like 变量的SQL语句应用程序执行效率低与yasql执行效率高
局域网监控电脑屏幕软件:PL/SQL 实现的数据库关联监控
在当今网络环境中,基于PL/SQL的局域网监控系统对于企业和机构的信息安全至关重要。该系统包括屏幕数据采集、数据处理与分析、数据库关联与存储三个核心模块,能够提供全面而准确的监控信息,帮助管理者有效监督局域网内的电脑使用情况。
71 2
DataWorks产品使用合集之ODPPS中如何使用SQL查询从表中获取值并将其赋值给临时变量以供后续使用
DataWorks作为一站式的数据开发与治理平台,提供了从数据采集、清洗、开发、调度、服务化、质量监控到安全管理的全套解决方案,帮助企业构建高效、规范、安全的大数据处理体系。以下是对DataWorks产品使用合集的概述,涵盖数据处理的各个环节。
MaxCompute产品使用问题之odps sql如何定义变量
MaxCompute作为一款全面的大数据处理平台,广泛应用于各类大数据分析、数据挖掘、BI及机器学习场景。掌握其核心功能、熟练操作流程、遵循最佳实践,可以帮助用户高效、安全地管理和利用海量数据。以下是一个关于MaxCompute产品使用的合集,涵盖了其核心功能、应用场景、操作流程以及最佳实践等内容。
314 0
|
9月前
|
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
PL/SQL中执行按钮变为灰色后如何恢复【已解决】
623 0
SQL调优之绑定变量用法简介
SQL调优之绑定变量用法简介
Oracle的PL/SQL游标自定义异常:数据探险家的“专属警示灯”
【4月更文挑战第19天】Oracle PL/SQL中的游标自定义异常是处理数据异常的有效工具,犹如数据探险家的警示灯。通过声明异常名(如`LOW_SALARY_EXCEPTION`)并在满足特定条件(如薪资低于阈值)时使用`RAISE`抛出异常,能灵活应对复杂业务规则。示例代码展示了如何在游标操作中定义和捕获自定义异常,提升代码可读性和维护性,确保在面对数据挑战时能及时响应。掌握自定义异常,让数据管理更从容。
Oracle的PL/SQL游标异常处理:从“惊涛骇浪”到“风平浪静”
【4月更文挑战第19天】Oracle PL/SQL游标异常处理确保了在数据操作中遇到的问题得以优雅解决,如`NO_DATA_FOUND`或`TOO_MANY_ROWS`等异常。通过使用`EXCEPTION`块捕获并处理这些异常,开发者可以防止程序因游标问题而崩溃。例如,当查询无结果时,可以显示定制的错误信息而不是让程序终止。掌握游标异常处理是成为娴熟的Oracle数据管理员的关键,能保证在复杂的数据环境中稳健运行。
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")
5-10Can't connect to MySQL server on 'sh-cynosl-grp-fcs50xoa.sql.tencentcdb.com' (110)")

热门文章

最新文章