关于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语句的过量硬解析。

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2014-12-03

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏乐沙弥的世界

Oracle 历史SQL语句执行计划的对比与分析

    基于CBO优化器的环境中,SQL执行计划的生成依赖于统计信息的真实与完整。如列的离散度,列上的直方图,索引的可用性,索引上的聚簇因子。当这些信息是真实完...

10210
来自专栏.net core新时代

数据字典生成工具之旅(8):SQL查询表的约束默认值等信息

      上一篇代码生成工具里面已经用到了读取表结构的SQL,这篇将更加详细的介绍SQL SERVER常用的几张系统表和视图! 阅读目录 系统表视图介绍 实际...

21770
来自专栏Albert陈凯

hive的partition的作用和使用方法

一、背景 在Hive Select查询中一般会扫描整个表内容,会消耗很多时间做没必要的工作。有时候只需要扫描表中关心的一部分数据,因此建表时引入了partiti...

56140
来自专栏程序猿

SQL 注入语句特征

语句特征 1.判断有无注入点 ; and 1=1 and 1=2 2.猜表一般的表的名称无非是admin adminuser user pass passwor...

514110
来自专栏数据和云

【动手实践】Oracle 12.2新特性:多列列表分区和外部表分区

在Oracle 12.2版本中,增加了大量的分区新特性,这其中包括: 自动的列表分区创建 在线的普通表转换分区表 支持只读分区和读写分区混合 以下介绍的三个特...

33050
来自专栏我的博客

Python-MySQL查询函数使用示例

# -*- coding: UTF-8 -*- import MySQLdb import time #警告信息try except是无法捕捉的 fro...

36050
来自专栏拂晓风起

[C#]log4net写SQLServer数据库日志的配置方法

15840
来自专栏杨建荣的学习笔记

MySQL中的change,modify和自增列的关系(r12笔记第70天)

关于MySQL里的change和modify,总是看到两种不同的语法,在Oracle中语法有modify,如果修改表名有rename。 alter tab...

23460
来自专栏散尽浮华

Mysql更换MyISAM存储引擎为Innodb的操作记录

一般情况下,mysql会默认提供多种存储引擎,可以通过下面的查看: 1)查看mysql是否安装了innodb插件。 通过下面的命令结果可知,已经安装了innod...

31090
来自专栏乐沙弥的世界

批量生成sqlldr文件,高速卸载数据

      SQL*Loader 是用于将外部数据进行批量高速加载的数据库的最高效工具,可用于将多种平面格式文件加载到Oracle数据库。SQL*Loader支...

19420

扫码关注云+社区

领取腾讯云代金券