[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下隐式转换还是存在的,有机会还是给测试看看.