[20200326]绑定变量抓取与NULL值.txt

来源：这里教程网时间：2026-03-03 15:21:11 作者：

[20200326]绑定变量抓取与NULL值.txt --//如果绑定变量传入的变量是NULL,使用视图v$sql_bind_capture看到是什么值呢? --//昨天做优化时遇到一个问题,自己验证看看. 1.环境: SCOTT@book> @ ver1 PORT_STRING VERSION BANNER ------------------------------ -------------- -------------------------------------------------------------------------------- x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production create table t as select rownum id , lpad('a',10,'a') vc from dual connect by level<=20; --//分析略. 2.测试: variable v1 varchar2(10); exec :v1 := NULL; SCOTT@book> select * from t where vc = :v1; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID bkb7yq66usd1g, child number 0 ------------------------------------- select * from t where vc = :v1 Plan hash value: 1601196873 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 14 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): (null) ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("VC"=:V1) --//sql_id=bkb7yq66usd1g SCOTT@book> set null null_notexist SCOTT@book> @ bind_cap.sql bkb7yq66usd1g '' SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30 ------------- ------------ --- ---- -------- ---------- ------------------- --------------- ------------ ------------------------------ bkb7yq66usd1g 0 YES :V1 1 32 2020-03-26 08:51:46 VARCHAR2(32) NULL null_notexist --//很明显在视图v$sql_bind_capture看到的字符串'NULL'来表示NULL值。 variable v2 varchar2(10); variable v3 varchar2(10); exec :v2 := 'NULL'; exec :v3 := 'null'; SCOTT@book> select * from t where vc = :v1 or vc = :v2 or vc = :v3; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 2f5cv97um55gu, child number 0 ------------------------------------- select * from t where vc = :v1 or vc = :v2 or vc = :v3 Plan hash value: 1601196873 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 2 | 28 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (VARCHAR2(30), CSID=852): (null) 2 - (VARCHAR2(30), CSID=852): 'NULL' 3 - (VARCHAR2(30), CSID=852): 'null' Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("VC"=:V1 OR "VC"=:V2 OR "VC"=:V3)) SCOTT@book> @ bind_cap.sql 2f5cv97um55gu '' C200 ------------------------------------------------------ select * from t where vc = :v1 or vc = :v2 or vc = :v3 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30 ------------- ------------ --- ---- -------- ---------- ------------------- --------------- ------------ ------------- 2f5cv97um55gu 0 YES :V1 1 32 2020-03-26 08:55:53 VARCHAR2(32) NULL null_notexist YES :V2 2 32 2020-03-26 08:55:53 VARCHAR2(32) NULL null_notexist YES :V3 3 32 2020-03-26 08:55:53 VARCHAR2(32) null null_notexist --//注意看VALUE_STRING的显示，很容易出现歧义性。实际上在这个视图里面对于字符串很难区分到底NULL还是'NULL'字符串的。 --//不过可以猜测大多数情况应该表示NULL值，^_^也许不对。 3.测试: variable v_id1 number; variable v_id2 number; exec :v_id1 := NULL; exec :v_id2 := 0 SCOTT@book> Select * from t where id = :v_id1 or id = :v_id2; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 0z8s0vaj77vbr, child number 0 ------------------------------------- Select * from t where id = :v_id1 or id = :v_id2 Plan hash value: 1601196873 --------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 3 (100)| | |* 1 | TABLE ACCESS FULL| T | 1 | 14 | 3 (0)| 00:00:01 | --------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (NUMBER): (null) 2 - (NUMBER): 0 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(("ID"=:V_ID1 OR "ID"=:V_ID2)) SCOTT@book> @ bind_cap.sql 0z8s0vaj77vbr '' C200 ------------------------------------------------ Select * from t where id = :v_id1 or id = :v_id2 SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30 ------------- ------------ --- ------ -------- ---------- ------------------- --------------- ------------ ---------- 0z8s0vaj77vbr 0 YES :V_ID1 1 22 2020-03-26 09:05:30 NUMBER NULL YES :V_ID2 2 22 2020-03-26 09:05:30 NUMBER 0 --//number类型问题不大，VALUE_STRING＝NULL，一定表示null值。 4.总结: --//如果抓取绑定变量值在v$sql_bind_capture视图的VALUE_STRING在DATATYPE_STRING为字符类型是显示NULL，一定注意多数情况下是 --//表示NULL值而'NULL'字符串。这点在优化时注意，最近一条sql语句优化时遇到问题，没注意看，带入的是'NULL'字符串，实际上参 --//数是NULL。从某种意义讲开发在写这些语句是有问题，执行前没有仔细验证，导致语句短路,例子： SCOTT@book> insert into t values (21,NULL); 1 row created. SCOTT@book> commit ; Commit complete. SCOTT@book> select * from t where vc=NULL; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID anzhn856k2rnr, child number 0 ------------------------------------- select * from t where vc=NULL Plan hash value: 1322348184 ---------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | FILTER | | | | | | | 2 | TABLE ACCESS FULL| T | 20 | 280 | 3 (0)| 00:00:01 | ---------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 2 - SEL$1 / T@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(NULL IS NOT NULL) --//注意查询过滤条件,filter(NULL IS NOT NULL)。 SCOTT@book> create index i_t_id on t(id); Index created. SCOTT@book> variable v_idx number; SCOTT@book> select * from t where id=:v_idx; no rows selected SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID 3d4rgz2g849n5, child number 0 ------------------------------------- select * from t where id=:v_idx Plan hash value: 4153437776 --------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | | 1 | TABLE ACCESS BY INDEX ROWID| T | 1 | 14 | 1 (0)| 00:00:01 | |* 2 | INDEX RANGE SCAN | I_T_ID | 1 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / T@SEL$1 2 - SEL$1 / T@SEL$1 Peeked Binds (identified by position): -------------------------------------- 1 - (NUMBER): (null) ~~~~~~~~~~~~~~~~~~~~~~~ Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID"=:V_IDX) --//v_idx开始没有赋值，缺省为NULL。注意看执行计划使用对应建立的索引。 --//你可以想像由于绑定变量peeking，可能你第一次带入就是NULL，就可能执行语句发生畸变，选择不合理的索引。 --//昨天在优化时就遇到这个问题。执行语句类似如下： select * from t where id=:1 and cr_date <= to_date(:2,'yyyy-mm-dd hh24:mi:ss'); --//如果第1次执行:2 带入的是NULL，就会使用cr_date索引，注意查询范围<=。导致后续的执行都是选择cr_date索引,这样当:2非NULL时 --//(一般这个日期就是当天日期)，相当于扫描整个索引以及对应表数据块,这个业务奇慢无比，我们这里需要8秒才完成。 --//我在优化时没有执行看带入的是'NULL'字符串，实际上我的执行脚本前面有set termout off，屏蔽了输出，执行是报错的。 --//但是生成的执行计划走的是id索引，导致误判，做一个记录，以后查询v$sql_bind_capture出现NULL要有意识是表示NULL，而不是字 --//符串。 SCOTT@book> select to_date('NULL','yyyy-mm-dd hh24:mi:ss') from dual ; select to_date('null','yyyy-mm-dd hh24:mi:ss') from dual * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 SCOTT@book> select * from emp where empno=7369 and hiredate <= to_date('null','yyyy-mm-dd hh24:mi:ss'); select * from emp where empno=7369 and hiredate <= to_date('null','yyyy-mm-dd hh24:mi:ss') * ERROR at line 1: ORA-01841: (full) year must be between -4713 and +9999, and not be 0 SCOTT@book> @ dpc '' '' PLAN_TABLE_OUTPUT ------------------------------------- SQL_ID banz95n3anpm0, child number 0 ------------------------------------- select * from emp where empno=7369 and hiredate <= to_date('null','yyyy-mm-dd hh24:mi:ss') Plan hash value: 2949544139 --------------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 1 (100)| | |* 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 1 (0)| 00:00:01 | |* 2 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| | --------------------------------------------------------------------------------------- Query Block Name / Object Alias (identified by operation id): ------------------------------------------------------------- 1 - SEL$1 / EMP@SEL$1 2 - SEL$1 / EMP@SEL$1 Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter("HIREDATE"<=TO_DATE('null','yyyy-mm-dd hh24:mi:ss')) 2 - access("EMPNO"=7369) --//即使执行错误，执行计划也是会生成的，实际上就是这样也会导致执行计划选择固定。以后要优化时特别要注意这种情况。 5.附录脚本如下: $ cat bind_cap.sql set verify off column value_string format a50 column datatype_string format a15 break on sql_id on child_number skip 1 select replace(sql_fulltext,chr(13),'') c200 from v$sql where sql_id='&1' and rownum<=1; SELECT sql_id, child_number, was_captured, name, position, max_length, last_captured, datatype_string, DECODE ( datatype_string, 'DATE', TO_CHAR (TO_DATE (value_string, 'mm/dd/yy hh24:mi:ss'), 'yyyy/mm/dd hh24:mi:ss'), value_string) value_string, decode(datatype_string,'TIMESTAMP',ANYDATA.accesstimestamp (value_anydata)) c30 FROM v$sql_bind_capture WHERE sql_id = '&1' and was_captured='YES' and DUP_POSITION is null and name=nvl('&&2',name) order by child_number,was_captured,position; break on sql_id on child_number skip 0

编辑推荐：

返回列表

[20200326]绑定变量抓取与NULL值.txt

编辑推荐：

相关推荐

excel表格怎样在数字前输入0

excel表格里怎么替换数据

excel表格里面如何插入圆形

excel表格里怎么添加表格数据透视表

excel表格中怎么添加线边框

excel表格里怎样设置条件自动排序

excel表格例怎样导入数据库

Excel表格如何设置数据排序

excel表格里怎么选择性粘贴快捷键

excel表格怎么插入分隔符号

excel表格怎么设置多行多列数据内容排序

Excel表格如何设置行和列相互转换

excel表格连接公式怎么使用

excel表格怎样链接到cad中

雷神推出 MIX PRO II 迷你主机：基于 Ultra 200H，玻璃上盖 + ARGB 灯效

制造商 Musnap 推出彩色墨水屏电纸书 Ocean C：支持手写笔、第三方安卓应用

最新软件资讯

热文推荐

天极热推