[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
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/267265/viewspace-2682613/ ，如需转载，请注明出处，否则将追究法律责任。
最新文章
- [20261008]验证Shared Memory Segments key以及Semaphore Arrays Key(11g).txt
- [20261008]gdb引入结构体是否可行.txt
- [20261008]bpftrace定义结构体例子.txt
- [20261007]关于oracle sskgph2函数.txt
- [20261007]oracle Semaphore Arrays Key值的冲突.txt
- [20261004]oracle sskgph2函数等价c以及python脚本.txt
- [20261004]bpftrace查看oracle函数调用堆栈问题.txt
- [20261002]验证sskgphpjw函数参与运算的字符串是否存在规律.txt
- [20261002]oracle Semaphore Arrays Key值的计算2.txt
- [20261001]oracle Semaphore Arrays Key值的计算.txt
