Oracle绑定变量类型和长度引起的执行计划变化

请关注个人小站：http://sqlhis.com/

在Oracle数据库中执行SQL语句，当客户端发出一条语句交付到ORACLE，会进行以下几个步骤：

1、语法检查(syntax check)
检查此sql的拼写是否语法。
2、语义检查(semantic check)
诸如检查sql语句中的访问对象是否存在及该用户是否具备相应的权限。
3、对sql语句进行解析(prase)
利用内部算法对sql进行解析，生成解析树(parse tree)及执行计划(execution plan)。
4、执行sql，返回结果(execute and return)

其中，软、硬解析就发生在第三个过程里。

如果使用了绑定变量，且绑定变量类型一直未变话，则通常只在第一次执行的时候进行一次硬解析（优化器创建解析树、生成执行计划），后续都是软解析（将此SQL和cache中的进行比较，如果相同，取已生成的执行计划），创建解析树、生成执行计划对于sql的执行来说是开销昂贵的动作，所以，应当极力避免硬解析，尽量使用软解析。

本范例想说明:即便SQL语句完全相同，但是如果绑定变量的类型或者长度发生了变化的话，也会发生硬解析.

 建立测试表并清理缓存

--建立测试表
create table TESTBIND
(
  aaa CHAR(10),
  bbb CHAR(100),
  ccc CHAR(2000)
)


--清理缓存
ALTER SYSTEM FLUSH SHARED_POOL;
alter system flush BUFFER_CACHE;

 

执行一次插入操作

--第一次绑定
DECLARE v_AAA CHAR(10):='A';
        v_BBB CHAR(10):='B';
        v_CCC CHAR(10):='C';
BEGIN    
    INSERT INTO TESTBIND VALUES(v_AAA,v_BBB,v_CCC);
    COMMIT;
END;

 

查找被缓存的执行计划

SELECT SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS,EXECUTIONS, CHILD_NUMBER
FROM V$sql
WHERE sql_text LIKE  '%INSERT INTO TESTBIND%'
AND sql_TEXT NOT LIKE '%DECLARE%'

 返回结果如图：

SQL_ID：这段SQL的唯一ID

EXECUTIONS：这段语句执行次数

 多次执行上面的插入语句并检查V$SQL缓存，发现EXECUTIONS不断增加，这说明后续都是执行的软解析，即复用了第一次生成的执行计划

 

 再次执行插入操作，这次改变绑定变量的类型，v_AAA的类型从CHAR(10) 改为了VARCHAR(10)

--第二次绑定
DECLARE v_AAA VARCHAR(10):='A';
        v_BBB CHAR(10):='B';
        v_CCC CHAR(10):='C';
BEGIN    
    INSERT INTO TESTBIND VALUES(v_AAA,v_BBB,v_CCC);
    COMMIT;
END;

然后再次查看V$SQL，发现现在有两条记录，也就是说，同一个语句，由于传入的绑定变量类型不同，在数据库中有两个执行计划，执行计划可以通过CHILD_NUMBER来区分

SELECT SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS,EXECUTIONS, CHILD_NUMBER
FROM V$sql
WHERE sql_text LIKE  '%INSERT INTO TESTBIND%'
AND sql_TEXT NOT LIKE '%DECLARE%'

 

为了看的更清楚一点，我们观察v$sql_shared_cursor,这个视图会说明两个执行计划不一致的原因，v$sql_shared_cursor有很多不同的列，标识了各种执行计划不能复用的原因，由于我们今天只测试改变绑定变量类型和长度，所以只需要关注:

BIND_MISMATCH:当为Y的时候表示绑定变量类型不一致

BIND_LENGTH_UPGRADEABLE:当为Y的时候表示绑定变量类型的长度发生了变化

REASON：一个XML输出，网上搜索了一遍，也没看到这个XML的解释，总之比较奇怪，不太看的明白

SELECT SQL_ID,CHILD_NUMBER,BIND_MISMATCH,BIND_LENGTH_UPGRADEABLE,REASON FROM v$sql_shared_cursor
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

查询结果如下：BIND_MISMATCH=Y，表示绑定变量的类型发生了变化

REASON字段的XML，除了标红的Bind mismatch(8)可以理解为绑定变量不一致外，其他都不太理解，且找了一轮没有注释

<ChildNode>
<ChildNumber>0</ChildNumber>
<ID>40</ID>
<reason>Bind mismatch(8)</reason>
<size>4x4</size>
<bind_position>0</bind_position>
<original_oacflg>19</original_oacflg>
<original_oacdty>96</original_oacdty>
<new_oacdty>1</new_oacdty>
</ChildNode>

 

从视图可以查看每个查询计划绑定变量的类型

SELECT * FROM v$sql_bind_capture
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE  '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

根据CHILD_NUMBER区分，可以看出参数B3 的类型发生了变化，注意这里类型的长度都是32，后面会说到原因

查询具体的执行计划可以通过以下语句，第一个参数是SQL_ID,第二个参数是CHILD_NUMBER

select  * from table(dbms_xplan.display_cursor('4yddzp87tmzza',0)); 
select  * from table(dbms_xplan.display_cursor('4yddzp87tmzza',1)); 

 

再次执行插入语句，这次将V_AAA类型的长度从10改为33

--第三次绑定
DECLARE v_AAA VARCHAR(33):='A';
        v_BBB CHAR(10):='B';
        v_CCC CHAR(10):='C';
BEGIN    
    INSERT INTO TESTBIND VALUES(v_AAA,v_BBB,v_CCC);
    COMMIT;
END;

 

 再次通过语句检查执行计划和绑定变量类型

SELECT SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS,EXECUTIONS, CHILD_NUMBER
FROM V$sql
WHERE sql_text LIKE  '%INSERT INTO TESTBIND%'
AND sql_TEXT NOT LIKE '%DECLARE%'

SELECT SQL_ID,CHILD_NUMBER,BIND_MISMATCH,BIND_LENGTH_UPGRADEABLE,REASON FROM v$sql_shared_cursor
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE  '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

SELECT * FROM v$sql_bind_capture
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE  '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

V$SQL视图，可见又多了一个执行计划

v$sql_shared_cursor视图，可见BIND_LENGTH_UPGRADEABLE=Y,即表示重新编译原因是由于绑定变量的长度发生变化引起

v$sql_bind_capture视图，可见数据长度从32变为128

 

 再次执行一个插入语句

--第四次绑定
DECLARE v_AAA VARCHAR(129):='A';
        v_BBB CHAR(10):='B';
        v_CCC CHAR(10):='C';
BEGIN    
    INSERT INTO TESTBIND VALUES(v_AAA,v_BBB,v_CCC);
    COMMIT;
END;

可见一一共4个执行计划，每个插入都是不同的执行计划

解释了每次重新编译的原因

每次绑定变量的差异，其中关注一下类型的长度分别是32,128,2000，也就是根据传入类型的长度进行了区间划分

1-32分配到32

33到128分配到128

129到2000分配到2000，

这样的话，不会每改变一次传入变量长度执行计划就编译一次

 

 可以试下将绑定变量V_AAA长度设置为1000

--第五次绑定
DECLARE v_AAA VARCHAR(1000):='A';
        v_BBB CHAR(10):='B';
        v_CCC CHAR(10):='C';
BEGIN    
    INSERT INTO TESTBIND VALUES(v_AAA,v_BBB,v_CCC);
    COMMIT;
END;

无论执行多少次，都不会有新的执行计划产生，实际上使用了VARCHAR2(2000)这个参数相关的执行计划

 

 

最后汇总以下查询语句：

--检查缓存
SELECT SQL_TEXT,SQL_ID,LOADED_VERSIONS,OPEN_VERSIONS,EXECUTIONS, CHILD_NUMBER
FROM V$sql
WHERE sql_text LIKE  '%INSERT INTO TESTBIND%'
AND sql_TEXT NOT LIKE '%DECLARE%'

--v$SQL的按SQL_ID的汇总表
SELECT * FROM v$sqlarea
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE  '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

--相同语句使用不同执行计划的具体原因
SELECT SQL_ID,CHILD_NUMBER,BIND_MISMATCH,BIND_LENGTH_UPGRADEABLE,REASON FROM v$sql_shared_cursor
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE  '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

--不同执行计划对应的具体绑定变量
SELECT * FROM v$sql_bind_capture
WHERE sql_id IN (SELECT sql_ID FROM V$sql WHERE sql_text LIKE  '%INSERT INTO TESTBIND%' AND sql_TEXT NOT LIKE '%DECLARE%')

--查询具体的执行计划，第一个参数是SQL_ID,第二个参数和是CHILD_NUMBER
select  * from table(dbms_xplan.display_cursor('4yddzp87tmzza',0)); 
select  * from table(dbms_xplan.display_cursor('4yddzp87tmzza'