[20220815]奇怪的隱式轉換問題(11g測試補充).txt

[20220815]奇怪的隱式轉換問題(11g測試補充).txt

--//生產系統遇到一個奇怪的隱式轉換問題，問題在於沒有發生隱式轉換，前面已經做了一些分析增加11g下的測試情況.

--//測試的結果說明我有點想當然了，實際上從12.2版本開始，oracle就支援這樣的情況，當使用繫結變數時，帶入的繫結變數參
--//數是timestamp型別時，不再存在隱式轉換。即使秒後面的值非0!!

--//我看了我以前寫的[20191219]oracle timestamp資料型別的儲存.txt,如果秒後面的值是0，儲存佔用7個位元組。

--//在11g下做一些補充測試：

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

2.建立指令碼：

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.測試：
--//執行多次，避免對應子游標清除。
SCOTT@book> @ m9.txt
test 1:1
test 2:0
test 3:0
test 4:0
PL/SQL procedure successfully completed.
@ m9.txt
@ m9.txt
@ m9.txt
@ m9.txt

SCOTT@book> 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
---------- ------------- --------------------------------------------------------------------------------
         7 3pdjz4fgwwfj2 SELECT /*+ test1 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
         7 0juh2dbyx948s SELECT /*+ test2 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
         7 f04fd6q8z7n9w SELECT /*+ test3 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
         7 f3gx0d2rfn9sb SELECT /*+ test4 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1

$ echo 3pdjz4fgwwfj2 0juh2dbyx948s f04fd6q8z7n9w f3gx0d2rfn9sb  | tr ' ' '\n' | xargs -IQ sqlplus scott/book @ dpc Q '' '' | grep I_EMPX_HIREDATE
--//沒有返回，說明沒有使用索引I_EMPX_HIREDATE，這4種情況。

$ echo 3pdjz4fgwwfj2 0juh2dbyx948s f04fd6q8z7n9w f3gx0d2rfn9sb  | tr ' ' '\n' | xargs -IQ sqlplus scott/book @ dpc Q '' '' | grep EMPX
SELECT /*+ test1 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
|*  2 |   TABLE ACCESS FULL| EMPX |      1 |     9 |     3   (0)| 00:00:01 |
   2 - SEL$1 / EMPX@SEL$1
SELECT /*+ test2 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
|*  2 |   TABLE ACCESS FULL| EMPX |      1 |     9 |     3   (0)| 00:00:01 |
   2 - SEL$1 / EMPX@SEL$1
SELECT /*+ test3 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
|*  2 |   TABLE ACCESS FULL| EMPX |      1 |     9 |     3   (0)| 00:00:01 |
   2 - SEL$1 / EMPX@SEL$1
SELECT /*+ test4 */ COUNT(*) FROM EMPX WHERE HIREDATE = :B1
|*  2 |   TABLE ACCESS FULL| EMPX |      1 |     9 |     3   (0)| 00:00:01 |
   2 - SEL$1 / EMPX@SEL$1

--//執行選擇全表掃描，可以確定11g下確實發生了隱式轉換。

--//做到這裡我突然想起上線前(估計該專案上線快2年了),對方提出要求一定要在19c下執行，我當時覺得很奇怪,這個專案我不負責。現
--//在想想突然開竅了，他們應用帶入引數日期型別可能大量都是使用timestamp型別，在以前的版本一定會出現隱式轉換問題，導致出
--//現大量效能問題。而使用19c(實際上我的測試12.2以上版本都可以)巧妙的掩蓋這個設計缺陷，可以使用date欄位型別的索引。

--//順便再看看生產系統的情況：
SYS@192.168.100.235:1521/orcl> @ pr
==============================
PORT_STRING                   : x86_64/Linux 2.4.xx
VERSION                       : 19.0.0.0.0
BANNER                        : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
BANNER_FULL                   : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
BANNER_LEGACY                 : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
CON_ID                        : 0
PL/SQL procedure successfully completed.

SYS@192.168.100.235:1521/orcl> SELECT count(*),datatype_string FROM v$sql_bind_capture WHERE datatype_string in ('TIMESTAMP','DATE') group by datatype_string;
  COUNT(*) DATATYPE_STRING
---------- ------------------------------
      6628 DATE
       792 TIMESTAMP

--//哈哈，從記數輸出上可以驗證我的判斷，許多sql語句存在混用的date，timestamp的情況，看來國內的應用IT專案都是豆腐渣工程，
--//也許還給加上一個字首，那就是豆腐渣中的豆腐渣工程。
--//不去探究問題的本質，無語.............