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