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'