梆定变量的长度不一致也可能会造成硬解析

当第一次使用梆定变量为字符串类型,并且小于32bytes时，oracle捕获这个梆定变量，并改写该梆定变量的字符串长度为32Bytes。如果第二次再使用该sql和梆定变量，如果梆定变量长度超过32字节小于128字节，则oracle会自动将该梆定变量长度改为128字节，并且重新生成一个子游标和执行计划，造成相同梆定变量的sql的执行计划不能共享，需要重新生成解析树和执行计划（即是硬解析），过多的硬解析是万恶之源，会占用大量的CPU、IO和内存资源，造成系统性能瓶颈。所以使用相同SQL的相同梆定变量时，要特别注意每次该梆定变量的长度是否在符合软解析的区间长度内。

 

测试结果如下：

1）、创建一个10个字节可变长度的字符串变量 v：

23:24:15SYS@orcl*SQL> var v varchar2(10);

23:24:26SYS@orcl*SQL> exec :v :='abcd';

23:24:26SYS@orcl*SQL> print v;

V

----------

abcd

 

 

2）、将变量 v 梆定到 sql 的查询谓词条件值中，并执行：

23:24:27SYS@orcl*SQL> select * from tuser.testbind where test=:v;

 

TEST

----------

abcd

 

 

3）、查询上面执行的sql在 Library Cache （Sharedpool共享池中的库缓存）中生成游标和执行计划的 sql_id，并注意子游标号CHILD_NUMBER是0（第一个子游标，即第一个执行计划版本）：

23:24:32SYS@orcl*SQL> select sql_id,sql_text,child_number from v$sql where sql_textlike 'select * from tuser.testbind where test=:v%';

 

SQL_ID                     SQL_TEXT                                                          CHILD_NUMBER

-------------------------------------------------------------------------------------- ------------------

13gr9g2810v9c              select * from tuser.testbindwhere test=:v                                   0

 

 

4）、由于是第一次执行第2步的sql，并且梆定变量长度还没有变化，所以如下显示的梆定变量长度已更改字段（BIND_LENGTH_UPGRADEABLE）为No：

23:24:38SYS@orcl*SQL> select sql_id,child_number,BIND_LENGTH_UPGRADEABLE fromv$sql_shared_cursor

23:24:50   2 where sql_id='13gr9g2810v9c';

 

SQL_ID                           CHILD_NUMBERBIND_LENGTH_UPGRADEABLE

-------------------------------------------- ------------------------------

13gr9g2810v9c                               0 N

 

 

5）、查询捕获的梆定变量信息，DATATYPE_STRING字段列自动将小于32字节的梆定变量更改为32字节：

23:25:03SYS@orcl*SQL> select position,LAST_CAPTURED,datatype_string,value_string

23:25:04   2 from v$sql_bind_capture

23:25:04   3 where sql_id='13gr9g2810v9c';

 

NAME          POSITION LAST_CAPTURED          DATATYPE_STRING     VALUE_STRING

-----------   -------- ----------------------------------------- -------------------------

:V            1        2017-04-14 23:24:31    VARCHAR2(32)        abcd

 

 

6）、再将梆定变量 v 的长度更改为超过32字节，如下33字节：

23:25:05SYS@orcl*SQL>

23:25:09SYS@orcl*SQL> var v varchar2(33);

23:25:18SYS@orcl*SQL> exec :v :='abcd';

23:25:18SYS@orcl*SQL> print v;

V

----------

abcd

 

 

7）、再次执行以上第二步的sql：

23:25:19SYS@orcl*SQL> select * from tuser.testbind where test=:v;

 

TEST

----------

abcd

 

 

 

8)、和以上第3步对照，再次查询 Library Cache 中的 v$sql字典视图，发现生成多了一个子游标（CHILD_NUMBER=1），也就是发生硬解析，重新生成了新的执行计划：

23:25:23SYS@orcl*SQL> select sql_id,sql_text,child_number from v$sql where sql_textlike 'select * from tuser.testbind where test=:v%';

 

SQL_ID                     SQL_TEXT                                                          CHILD_NUMBER

-------------------------------------------------------------------------------------- ------------------

13gr9g2810v9c              select * from tuser.testbindwhere test=:v                                   0

13gr9g2810v9c              select * from tuser.testbindwhere test=:v                                   1

 

 

 

9)、和以上第4步对照，再次查询可以发现，oracle已经捕获到梆定变量 v 的长度相比之前发生了变化：

23:25:39SYS@orcl*SQL> select sql_id,child_number,BIND_LENGTH_UPGRADEABLE fromv$sql_shared_cursor

23:25:40   2 where sql_id='13gr9g2810v9c';

 

SQL_ID                           CHILD_NUMBERBIND_LENGTH_UPGRADEABLE

-------------------------------------------- ------------------------------

13gr9g2810v9c                               0 N

13gr9g2810v9c                               1 Y

 

 

 

10）、oracle 自动将梆定变量 v 的长度改为128字节，第二次定义时是33字节（见第6步）

23:25:40SYS@orcl*SQL> select position,LAST_CAPTURED,datatype_string,value_string

23:25:46   2 from v$sql_bind_capture

23:25:46   3 where sql_id='13gr9g2810v9c';

 

          POSITION LAST_CAPTURED       DATATYPE_STRING                VALUE_STRING

------------------------------------- ------------------------------------------------------------

                 1 2017-04-14 23:25:23VARCHAR2(128)                  abcd

                 1 2017-04-14 23:24:31VARCHAR2(32)                   abcd

 

 

 

由此可见，虽然使用梆定变量能让相同的SQL，不同的条件值可以走上相同的执行计划，从而避免过多的硬解析，不会消耗过多的资源，但也要注意梆定变量长度的变化，否则使用了梆定变量也可能会产生硬解析，造成资源浪费。

（完）