[20200326]繫結變數抓取與NULL值.txt

lfree發表於2020-03-26

[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/,如需轉載,請註明出處,否則將追究法律責任。

相關文章