[20200326]繫結變數抓取與NULL值.txt
[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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20150812]關於抓取繫結變數.txt變數
- Oracle9i, 10g 如何抓取繫結變數的值Oracle變數
- ORACLE 獲取繫結變數值Oracle變數
- 如何獲取繫結變數值變數
- 查詢繫結變數的值變數
- 關於繫結變數的SQL繫結什麼值變數SQL
- [20180912]PLSLQ與繫結變數.txt變數
- [20180930]in list與繫結變數.txt變數
- 獲取sql繫結變數的值SQL變數
- [20180930]in list與繫結變數個數.txt變數
- [20191213]共享池繫結變數的值在哪裡.txt變數
- [20210120]in list與繫結變數個數.txt變數
- Oracle 變數繫結與變數窺視合集Oracle變數
- 如何用FGA得到繫結變數的值變數
- 如何得到繫結變數的輸入值變數
- 繫結變數變數
- [20190506]檢視巢狀與繫結變數.txt巢狀變數
- [20170929]& 代替冒號繫結變數.txt變數
- [20160706]like % 繫結變數.txt變數
- [20220414]toad與繫結變數peek.txt變數
- 【sql調優】繫結變數與CBOSQL變數
- 【原創】Oracle 變數繫結與變數窺視合集Oracle變數
- 繫結變數之繫結變數窺探(Bind Peeking)變數
- v$sql_bind_capture與timestamp型別的繫結變數的數值SQLAPT型別變數
- [20171231]PLSQL使用繫結變數.txtSQL變數
- Oracle 繫結變數Oracle變數
- [20191216]共享池繫結變數的值在哪裡2.txt變數
- [20211227]抽取跟蹤檔案中的繫結變數值.txt變數
- [20231210]執行計劃與繫結變數.txt變數
- 從不繫結變數與繫結變數兩種情況討論柱狀圖的作用變數
- 檢視繫結變數變數
- 繫結變數窺測變數
- PLSQL使用繫結變數SQL變數
- Oracle之繫結變數Oracle變數
- 關於繫結變數變數
- 【優化】使用繫結變數 OR 不使用繫結變數，這不是問題！優化變數
- [20160224]繫結變數的分配長度.txt變數
- [20121102]PLSQL中的繫結變數.txtSQL變數