[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下隐式转换还是存在的,有机会还是给测试看看.
--//生产系统遇到一个奇怪的隐式转换问题，问题在于没有发生隐式转换，前面已经做了一些分析增加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下隐式转换还是存在的,有机会还是给测试看看.
