[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项目都是豆腐渣工程，
--//也许还给加上一个前缀，那就是豆腐渣中的豆腐渣工程。
--//不去探究问题的本质，无语.............