[20220811]奇怪的隱式轉換問題(12c補充測試).txt
[20220811]奇怪的隱式轉換問題(12c補充測試).txt
--//生產系統遇到一個奇怪的隱式轉換問題，問題在於沒有發生隱式轉換，前面已經做了一些分析增加12c下的測試情況.
--//我當時的猜測如下：
--//也許oracle 19c版本支援這樣的操作，當帶入日期型別引數是timestamp型別時，如果秒後面的值全部是0，可以當作date型別使用。
--//導致不會發生隱式轉換，我估計oracle估計遇到這樣的情況有點多，做了這樣的改進，規避了這個問題，在執行時探測繫結變數，避
--//免了隱式轉換的發生。
--//後記:測試的結果說明我有點想當然了，實際上從12.2版本開始，oracle就支援這樣的情況，當使用繫結變數時，帶入的繫結變數參
--//數是timestamp型別時，不再存在隱式轉換。即使秒後面的值非0!!
--//我看了我以前寫的[20191219]oracle timestamp資料型別的儲存.txt=>
--//如果秒後面的值是0，儲存佔用7個位元組。
--//在家裡僅僅有12c的環境，做一些補充測試：
1.環境：
SCOTT@test01p> @ ver1
PORT_STRING VERSION BANNER CON_ID
------------------------------ -------------- -------------------------------------------------------------------------------- ----------
IBMPC/WIN_NT64-9.1.0 12.2.0.1.0 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production 0
2.建立指令碼：
--//我本來想測試一下11g是否也是一樣，我記憶裡不會出現19c的情況，我翻看了以前工作的最佳化筆記。
--//以前10g下絕對不可能，我做的最佳化筆記裡有類似的情況。
--//在11g,12c下測試，有一點點小麻煩，必須自己編寫PL/sql，因為sqlplus下variable無法支援date，timestamp型別的定義。
--//而toad裡面也遇到類似的問題，帶入引數沒有選擇timestamp型別的，不知道如何解決。
create table empx as select * from emp;
create index i_empx_hiredate on empx(hiredate);
$ cat m9.txt
DECLARE
v_empno number;
v_hiredate1 timestamp(9);
v_hiredate2 timestamp(9);
v_hiredate3 timestamp(9);
v_hiredate4 timestamp(9);
v_hiredate date;
begin
v_hiredate1 := to_timestamp('1980-12-17 00:00:00.000000000','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate2 := to_timestamp('1980-12-17 10:10:10.000000000','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate3 := to_timestamp('1980-12-17 00:00:00.000000001','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate4 := to_timestamp('1980-12-17 00:00:00.000001001','yyyy-mm-dd hh24:mi:ss.ff9') ;
select /*+ test1 */ count(*) into v_empno from empx where hiredate = v_hiredate1;
dbms_output.put_line( 'test 1:'||to_char(v_empno) );
select /*+ test2 */ count(*) into v_empno from empx where hiredate = v_hiredate2;
dbms_output.put_line( 'test 2:'||to_char(v_empno) );
select /*+ test3 */ count(*) into v_empno from empx where hiredate = v_hiredate3;
dbms_output.put_line( 'test 3:'||to_char(v_empno) );
select /*+ test4 */ count(*) into v_empno from empx where hiredate = v_hiredate4;
dbms_output.put_line( 'test 4:'||to_char(v_empno) );
end;
/
3.測試：
--//執行多次，避免對應子游標清除。
SET SERVEROUTPUT ON;
@ m9.txt
@ m9.txt
@ m9.txt
@ m9.txt
SCOTT@test01p> @ m9.txt
test 1:1
test 2:0
test 3:0
test 4:0
PL/SQL procedure successfully completed.
--//計算結果正確.
SET SERVEROUTPUT OFF
SCOTT@test01p> select executions,sql_id,sql_text c80 from v$sql where lower(sql_text) like 'select%test%' and sql_text not like '%sql_text%' order by 3;
EXECUTIONS SQL_ID C80
---------- ------------- --------------------------------------------------------------------------------
13 3pdjz4fgwwfj2 SELECT /*+ test1 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
13 0juh2dbyx948s SELECT /*+ test2 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
13 f04fd6q8z7n9w SELECT /*+ test3 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
13 f3gx0d2rfn9sb SELECT /*+ test4 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
$ echo 3pdjz4fgwwfj2 0juh2dbyx948s f04fd6q8z7n9w f3gx0d2rfn9sb | tr ' ' '\n' | xargs -IQ sqlplus scott/btbtms@test01p @ dpc Q '' '' | egrep I_EMPX_HIREDATE
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 8 | 1 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 8 | 1 (0)| 00:00:01 |
--//恩，真心徹底顛覆我的認知,12c下就可以避免這種形式的隱式轉換.並且4種timestamp形式都可以,只要使用繫結變數就ok。
SCOTT@test01p> @ dpc f3gx0d2rfn9sb '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID f3gx0d2rfn9sb, child number 0
-------------------------------------
SELECT /*+ test4 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
Plan hash value: 3602029756
--------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 8 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMPX@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("HIREDATE"=:B1)
SCOTT@test01p> @ bind_cap f3gx0d2rfn9sb ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ----- ---------- ---------- ------------------- --------------- ------------- ------------------------------
f3gx0d2rfn9sb 0 YES :B1 1 11 2022-08-11 21:19:55 TIMESTAMP 1980-12-17 00:00:00.000001001
--//引數:B1帶入什麼值呢??而且計算結果也是正確的.
--//如果我直接帶入數值:
SCOTT@test01p> select count(*) from empx where hiredate = timestamp '1980-12-17 00:00:00.000001001';
COUNT(*)
----------
0
SCOTT@test01p> @dpc '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 7dyh67x20nnwk, child number 0
-------------------------------------
select count(*) from empx where hiredate = timestamp '1980-12-17
00:00:00.000001001'
Plan hash value: 36332186
----------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | SORT AGGREGATE | | 1 | 8 | | |
|* 2 | TABLE ACCESS FULL| EMPX | 1 | 8 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$1 / EMPX@SEL$1
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(INTERNAL_FUNCTION("HIREDATE")=TIMESTAMP' 1980-12-17
00:00:00.000001001')
--//可以發現帶入timestamp數值出現隱式轉換，執行計劃全部掃描，也就是不支援文字變數。
SCOTT@test01p> alter session set cursor_sharing=force ;
Session altered.
Select count(*) from empx where hiredate = timestamp '1980-12-17 00:00:00.000001001';
--//結果不貼出來了,依舊選擇全表掃描.使用to_timestamp('1980-12-17 00:00:00.000001001','yyyy-mm-dd hh24:mi:ss.ff9')也是一樣.
--//如果真像測試那樣,oracle確實做了重大改進!!
4.做10053跟蹤看看:
SCOTT@test01p> @10053x f3gx0d2rfn9sb 0
PL/SQL procedure successfully completed.
SCOTT@test01p> @ tpt/ttt
tracefile_identifier = D:\APP\ORACLE\diag\rdbms\test\test\trace\test_ora_4956_af3gx0d2rfn9sb.trc
--//檢查跟蹤檔案發現如下內容:
*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=180 mxl=11(11) mxlc=00 mal=00 scl=00 pre=00
oacflg=11 fl2=8000000 frm=00 csi=00 siz=16 off=0
kxsbbbfp=33849e88 bln=11 avl=11 flg=05
value=1980-12-17 00:00:00.
--//value後面有1個小數點.
...
Peeked Binds
============
Bind variable information
position=1
datatype(code)=180
datatype(string)=TIMESTAMP
max length=11
...
4.開啟statistics_level = all看看:
> @ dpc f3gx0d2rfn9sb '' ''
..
SQL_ID f3gx0d2rfn9sb, child number 1
-------------------------------------
SELECT /*+ test4 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
Plan hash value: 3602029756
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 |
| 1 | SORT AGGREGATE | | 1 | 1 | 8 | | | 1 |00:00:00.01 |
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 1 | 8 | 1 (0)| 00:00:01 | 0 |00:00:00.01 |
---------------------------------------------------------------------------------------------------------------------
--//buffers列都沒有，也就是邏輯讀應該為0，這樣的情況連索引都沒有探察。
SCOTT@test01p> @ dpc 3pdjz4fgwwfj2 '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 3pdjz4fgwwfj2, child number 0
-------------------------------------
SELECT /*+ test1 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
Plan hash value: 3602029756
-------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 1 (100)| | 1 |00:00:00.01 | 1 |
| 1 | SORT AGGREGATE | | 1 | 1 | 8 | | | 1 |00:00:00.01 | 1 |
|* 2 | INDEX RANGE SCAN| I_EMPX_HIREDATE | 1 | 1 | 8 | 1 (0)| 00:00:01 | 1 |00:00:00.01 | 1 |
-------------------------------------------------------------------------------------------------------------------------------
5.改寫指令碼看看:
$ cat m9.txt
DECLARE
v_empno number;
v_hiredate1 timestamp(9);
v_hiredate2 timestamp(9);
v_hiredate3 timestamp(9);
v_hiredate4 timestamp(9);
v_hiredate date;
begin
v_hiredate1 := to_timestamp('1980-12-17 00:00:00.000000000','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate2 := to_timestamp('1980-12-17 10:10:10.000000000','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate3 := to_timestamp('1980-12-17 00:00:00.000000001','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate4 := to_timestamp('1980-12-17 00:00:00.000001001','yyyy-mm-dd hh24:mi:ss.ff9') ;
v_hiredate := to_date('1980-12-17 00:00:00','yyyy-mm-dd hh24:mi:ss') ;
select /*+ test1 */ count(*) into v_empno from empx where hiredate = v_hiredate1;
dbms_output.put_line( 'test 1:'||to_char(v_empno) );
select /*+ test2 */ count(*) into v_empno from empx where hiredate = v_hiredate2;
dbms_output.put_line( 'test 2:'||to_char(v_empno) );
select /*+ test3 */ count(*) into v_empno from empx where hiredate = v_hiredate3;
dbms_output.put_line( 'test 3:'||to_char(v_empno) );
select /*+ test4 */ count(*) into v_empno from empx where hiredate = v_hiredate4;
dbms_output.put_line( 'test 4:'||to_char(v_empno) );
select /*+ test5 */ count(*) into v_empno from empx where hiredate = v_hiredate;
dbms_output.put_line( 'test 5:'||to_char(v_empno) );
end;
/
SCOTT@test01p> SET SERVEROUTPUT ON
SCOTT@test01p> @ m9.txt
test 1:1
test 2:0
test 3:0
test 4:0
test 5:1
PL/SQL procedure successfully completed.
SCOTT@test01p> select executions,sql_id,sql_text c80 from v$sql where lower(sql_text) like 'select%test5%' and sql_text not like '%sql_text%' order by 3;
EXECUTIONS SQL_ID C80
---------- ------------- --------------------------------------------------------------------------------
8 drk7cbdh2c24u SELECT /*+ test5 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
SCOTT@test01p> @ 10053x drk7cbdh2c24u 0
PL/SQL procedure successfully completed.
--//檢查跟蹤檔案發現如下內容:
*******************************************
Peeked values of the binds in SQL statement
*******************************************
----- Bind Info (kkscoacd) -----
Bind#0
oacdty=12 mxl=07(07) mxlc=00 mal=00 scl=00 pre=00
oacflg=11 fl2=0000 frm=00 csi=00 siz=8 off=0
kxsbbbfp=322ac000 bln=07 avl=07 flg=05
value="12/17/1980 0:0:0"
...
Peeked Binds
============
Bind variable information
position=1
datatype(code)=12
datatype(string)=DATE
max length=7
value=12/17/1980 00:00:00
--//使用10053跟蹤視乎提示oracle帶入:B1僅僅是timestamp秒之前的值.後面的一些細節我說不上.
--//而且超出我的想象,即使timestamp型別存在秒後面的值非0值,也不存在隱式轉換.只要使用繫結變數!!
--//這個算是oracle在最佳化上一個"重大"改進,估計一些應用存在這樣的情況，欄位date型別而帶入的繫結變數引數時timestamp型別，一
--//些細節我自己也沒有理解清楚.我感覺我應該不是第一個遇到這類問題的人。
--//而且如果使用常量,還是存在隱式轉換的.
--//我估計11g下隱式轉換還是存在的,有機會還是給測試看看.
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/267265/viewspace-2910282/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- [20220811]奇怪的隱式轉換問題.txt
- [20220815]奇怪的隱式轉換問題(11g測試補充).txt
- mysql隱式轉換問題MySql
- scala隱式轉換優先順序問題
- [20140116]檢視?隱式轉換?sql優化問題.txtSQL優化
- [20120601]ITL的問題補充.txt
- itoa函式的奇怪問題函式
- 【原創】由隱式轉換引起的資料庫效能問題資料庫
- js顯式轉換和隱式轉換JS
- javascript 隱式轉換JavaScript
- sql隱式轉換SQL
- Oracle 隱式轉換Oracle
- java隱式轉換Java
- [20220603]測試quiz night(補充).txtUI
- [20211013]測試遠端監聽補充.txt
- [20191106]隱式轉換.txt
- Scala - 隱式轉換和隱式引數
- Scala隱式轉換與隱式引數
- SQL Server資料庫中的資料型別隱式轉換問題SQLServer資料庫資料型別
- 【隱式轉換】注意隱式轉換將導致索引無法使用索引
- Scala Essentials: 隱式轉換
- [] == ![]，走進==隱式轉換的世界
- Java資料型別的顯式轉換和隱式轉換Java資料型別
- app 測試環境切換問題APP
- [20190211]簡單測試埠是否開啟(補充).txt
- scala中隱式轉換之隱式轉換呼叫類中本不存在的方法
- [20120201][補充]函式索引與取max值的問題1.txt函式索引
- [20130723]ORACLE 12C Invisible Columns的補充.txtOracle
- JavaScript隱式型別轉換JavaScript型別
- MySQL 隱式型別轉換MySql型別
- 【C++】禁止隱式轉換C++
- [20181128]toad連線資料庫的問題(補充).txt資料庫
- Spark中的三種隱式轉換Spark
- 你所忽略的js隱式轉換JS
- MySQL和Oracle中的隱式轉換MySqlOracle
- C++隱式型別的轉換C++型別
- 資料型別的隱式轉換資料型別
- 有趣的JavaScript隱式型別轉換JavaScript型別