炼数成金 门户 大数据 Oracle 查看内容

基于ORACLE SQL优化之绑定变量(3)

2017-3-15 21:39| 发布者: 炼数成金_小数| 查看: 14599| 评论: 0|原作者: Silence 陈喜强|来自: DBA求职布道者

摘要: 对于plsql代码中使用文本型绑定变量的目标SQL来说,只要SQL文本中文本型绑定变量的定义长度发生变化,则所分配的内存空间的大小也可能会随之发生变化,那么一旦Oracle为这些绑定变量所分配的内存空间的大小发生了变 ...

SQL 存储 Oracle Hadoop BI

绑定变量分级是指Oracle在plSQL代码中会根据文本型绑定变量的定义长度而将这些文本型绑定变量分为4个等级。

a、定义长度在32字节以内的文本型绑定变量被分在第一个等级
b、长度在33-128字节之间为第二个等级
c、长度在129-2000字节之间为第三个等级
d、长度在2000字节以上的被分为第四个等级

其中第四个等级文本型绑定变量分配的内存空间,取决于对应文本行绑定变量所传入的实际绑定变量值的大小。具体来说就是:如果实际传入绑定变量值小于或者等于2000字节,则Oracle为其分配2000字节内存空间,如果大于2000字节,则为其分配4000字节的内存空间。

绑定变量分级 仅适用于文本型的绑定变量,这意味着Oracle不会对数值型number型的绑定变量做绑定变量分级。

对于plsql代码中使用文本型绑定变量的目标SQL来说,只要SQL文本中文本型绑定变量的定义长度发生变化,则所分配的内存空间的大小也可能会随之发生变化,那么一旦Oracle为这些绑定变量所分配的内存空间的大小发生了变化,那么该SQL之前存储的child cursor中的解析树和执行计划就不能被重用了。原因是child cursor中除了会存储目标SQL的解析树和执行计划外,还会存储该SQL所使用的绑定变量的类型和长度,这意味着即使该SQL的SQL文本没有发生任何改变,只要其SQL文本中文本型绑定变量的定义长度发生了改变,那么该SQL再次执行时就可能还是做硬解析。

如下示例为一个绑定变量分级的操作:
创建一张表t,两列,列n为number,列v为varchar2(3000),然后分不同的字节插入五条数据,其中v字段分别为varchar2(32),varchar2(33),varchar2(129),varchar2(2001),varchar2(32767)
查询结果如下
SQL> select * from t;
         N V
---------- ------------------------------
         1 didu1
         2 didu2
         3 didu3
         4 didu4
         5 didu5
 
SQL>
由于插入的第4,5条数据的时候v的值只有5个字节,所以实际上Oracle只会为其分配2000字节的内存空间,也就说执行范例plsql代码4和5会使用软解析,软软解析,因为会沿用3代码的解析树和执行计划。
查询一下目标SQL的对应的parent cursor:
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'insert into t%';
 
SQL_TEXT                                                     SQL_ID                     VERSION_COUNT EXECUTIONS
------------------------------------------------------------ -------------------------- ------------- ----------
insert into t values(:n,:v)                                  21mycdpm39kzv                          3          5

说明目标SQL确实执行5次,对应的parent cursor确实挂了三个child cursor

SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address='00007FF771A1DE68' order by position;
 
ADDRESS          BIND_NAME                                                      POSITION   DATATYPE MAX_LENGTH
---------------- ------------------------------------------------------------ ---------- ---------- ----------
00007FF771A1DE68 N                                                                     1          2         22
00007FF771A1DE68 V                                                                     2          1         32
 
SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address='00007FF774A51260' order by position;
 
ADDRESS          BIND_NAME                                                      POSITION   DATATYPE MAX_LENGTH
---------------- ------------------------------------------------------------ ---------- ---------- ----------
00007FF774A51260 N                                                                     1          2         22
00007FF774A51260 V                                                                     2          1        128
 
SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address='00007FF771359A60' order by position;
 
ADDRESS          BIND_NAME                                                      POSITION   DATATYPE MAX_LENGTH
---------------- ------------------------------------------------------------ ---------- ---------- ----------
00007FF771359A60 N                                                                     1          2         22
00007FF771359A60 V                                                                     2          1       2000

从结果上看,child cursor 0中文本型绑定变量v确实被分配了32字节内存空间,child cursor 1绑定变量v被分配了128字节内存空间,child cursor 2绑定变量v被分配了2000字节的内存空间,但是数值型绑定变量同样分配了22字节的内存空间。
SQL> declare
  2   n number(10);
  3   v varchar2(2002);
  4  begin
  5   n := 6;
  6   v := rpad('didu6',2002,'6');
  7   execute immediate 'insert into t values (:n,:v)' using n,v;
  8   commit;
  9  end;
 10  /
 
PL/SQL 过程已成功完成。
 
SQL> select n,length(v) from t;
 
         N  LENGTH(V)
---------- ----------
         1          5
         2          5
         3          5
         4          5
         5          5
         6       2002
上述范例代码执行的还是之前的SQL语句,只是实际传入的v的值的长度发生了变化,由于这次插入的v的值大于2000,所以Oracle会为绑定变量v分配4000字节的内存空间,所以这次的插入会使用硬解析,所以目标SQL对应的parent cursor下应有4个child cursor
如下验证:
SQL> select sql_text,sql_id,version_count,executions from v$sqlarea where sql_text like 'insert into t%';
 
SQL_TEXT                                                     SQL_ID                     VERSION_COUNT EXECUTIONS
------------------------------------------------------------ -------------------------- ------------- ----------
insert into t values(:n,:v)                                  21mycdpm39kzv                          4          6
然后来看一下这个多出来的child cursor具体信息:
SQL> select sql_id,child_number,child_address from v$sql where sql_id='21mycdpm39kzv';
 
SQL_ID                     CHILD_NUMBER CHILD_ADDRESS
-------------------------- ------------ ----------------
21mycdpm39kzv                         0 00007FF771A1DE68
21mycdpm39kzv                         1 00007FF774A51260
21mycdpm39kzv                         2 00007FF771359A60
21mycdpm39kzv                         3 00007FF775992650

SQL> select address,bind_name,position,datatype,max_length from v$sql_bind_metadata where address='00007FF775992650' order by position;
 
ADDRESS          BIND_NAME                                                      POSITION   DATATYPE MAX_LENGTH
---------------- ------------------------------------------------------------ ---------- ---------- ----------
00007FF775992650 N                                                                     1          2         22
00007FF775992650 V                                                                     2          1       4000

从上述的结果看到,child cursor 3中文本行绑定变量v确实被分配了4000字节的内存空间。
所以得到结论:
为了避免不必要的硬解析,在plsql代码中处理带文本型绑定变量的目标SQL时,应该将这些文本型绑定变量的定义长度保持在同一个等级,当然,这里较好的定义成统一的长度,比如varchar2(4000)。

the end ,over!!!

欢迎加入本站公开兴趣群
软件开发技术群
兴趣范围包括:Java,C/C++,Python,PHP,Ruby,shell等各种语言开发经验交流,各种框架使用,外包项目机会,学习、培训、跳槽等交流
QQ群:26931708

Hadoop源代码研究群
兴趣范围包括:Hadoop源代码解读,改进,优化,分布式系统场景定制,与Hadoop有关的各种开源项目,总之就是玩转Hadoop
QQ群:288410967 

鲜花

握手

雷人

路过

鸡蛋

最新评论

热门频道

  • 大数据
  • 商业智能
  • 量化投资
  • 科学探索
  • 创业

即将开课

 

GMT+8, 2019-5-24 09:55 , Processed in 0.229662 second(s), 23 queries .