ITPub博客

首页 > 数据库 > Oracle > [20200326]绑定变量抓取与NULL值.txt

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

原创 Oracle 作者:lfree 时间:2020-03-26 10:12:40 0 删除 编辑

[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博客 ” ,链接:http://blog.itpub.net/267265/viewspace-2682613/,如需转载,请注明出处,否则将追究法律责任。

全部评论
熟悉oracle相关技术,擅长sql优化,rman备份与恢复,熟悉linux shell编程。

注册时间:2008-01-03

  • 博文量
    2695
  • 访问量
    6464190