[20220811]奇怪的隐式转换问题.txt
--//生产系统遇到一个奇怪的隐式转换问题，问题在于没有发生隐式转换，做一个分析调查。
--//后记：后面的分析存在一下偏差，不更正当时的分析，在这里做一个补充，实际上从12.2版本开始，oracle就支持这样的情况，当使
--//用绑定变量时，带入的绑定变量参数是timestamp类型时，不再存在隐式转换。即使秒后面的值非0!!
--//我另外写一篇 [20220811]奇怪的隐式转换问题(12c补充测试).txt 来说明问题。
--//感觉oracle 一个小小的改进，很奇怪我感觉至少我这之前没见过别人写过或者遇到这样的情况。
1.环境：
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.
2.分析：
SYS@192.168.100.235:1521/orcl> @dashtop sql_id 1=1 &day
Total
Seconds AAS %This SQL_ID FIRST_SEEN LAST_SEEN
--------- ------- ------- ------------- ------------------- -------------------
18420 .2 36% 2022-08-10 08:54:16 2022-08-11 07:59:14
4420 .1 9% 4qz6aykj6gq6v 2022-08-10 08:54:37 2022-08-11 07:46:32
2640 .0 5% gxak7guzxkwbh 2022-08-10 08:54:27 2022-08-11 07:23:29
2000 .0 4% 3rhg88u6qnt7h 2022-08-10 10:01:05 2022-08-11 07:23:29
1690 .0 3% 79ymhrhpk0x3p 2022-08-10 08:55:47 2022-08-11 07:54:03
1600 .0 3% 9akuv534yadhw 2022-08-10 10:13:57 2022-08-11 07:55:33
1100 .0 2% 0bzc2mv4s5f30 2022-08-10 08:55:37 2022-08-11 07:46:42
1030 .0 2% 18xh7p6w137q5 2022-08-10 08:55:17 2022-08-11 07:55:43
...
30 rows selected.
--//分析sql_id=4qz6aykj6gq6v.
SYS@192.168.100.235:1521/orcl> @ sql_id 4qz6aykj6gq6v
--SQL_ID = 4qz6aykj6gq6v
select a.*,1 as TenantId from lis_test a where test_date=:end_date and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT @ dpc 4qz6aykj6gq6v '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4qz6aykj6gq6v, child number 5
-------------------------------------
select a.*,1 as TenantId from lis_test a where test_date=:end_date
and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT @ bind_cap 4qz6aykj6gq6v ''
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- --------- -------- ---------- ------------------- --------------- ------------ ------------------------------
4qz6aykj6gq6v 5 YES :END_DATE 1 11 2022-08-11 08:54:14 TIMESTAMP 2022-08-11 00:00:00.000000000
YES :INST_ID 2 22 2022-08-11 08:54:14 NUMBER 2390
YES :STATE 3 22 2022-08-11 08:54:14 NUMBER 90
3 rows selected.
--//注意一个细节变量:END_DATE的数据类型带入的是TIMESTAMP。而前面的显示执行计划显示的是access，filter确是
--//"TEST_DATE"=:END_DATE，而字段TEST_DATE的定义数据类型是date(7),也就是执行计划没有发生没有执行隐式转换。
--//开始怀疑是否存在打入参数即有date类型又有timestamp类型，开始没有注意Peeked Binds (identified by position):部分显示，
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2490
3 - (NUMBER): 90
--//不然就不会在开始优化开始时走弯路了。
--//查询如下：
SYS@192.168.100.235:1521/orcl> @ bind_cap_awr 4qz6aykj6gq6v END_DATE
@bind_cap_awr sql_id [column]
1 row selected.
SNAP_ID INSTANCE_NUMBER SQL_ID WAS LAST_CAPTURED NAME POSITION MAX_LENGTH DATATYPE_STRING VALUE_STRING C30
---------- --------------- ------------- --- ------------------- --------- -------- ---------- --------------- ------------ ------------------------------
14720 1 4qz6aykj6gq6v YES 2022-08-02 22:48:44 :END_DATE 1 11 TIMESTAMP 2022-08-02 00:00:00.000000000
14721 1 4qz6aykj6gq6v YES 2022-08-02 23:48:48 :END_DATE 1 11 TIMESTAMP 2022-08-02 00:00:00.000000000
14722 1 4qz6aykj6gq6v YES 2022-08-03 00:48:52 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000
14723 1 4qz6aykj6gq6v YES 2022-08-03 01:48:54 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000
14724 1 4qz6aykj6gq6v YES 2022-08-03 02:48:58 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000
14725 1 4qz6aykj6gq6v YES 2022-08-03 03:49:01 :END_DATE 1 11 TIMESTAMP 2022-08-03 00:00:00.000000000
--//snip....
14921 1 4qz6aykj6gq6v YES 2022-08-11 07:54:12 :END_DATE 1 11 TIMESTAMP 2022-08-11 00:00:00.000000000
14922 1 4qz6aykj6gq6v YES 2022-08-11 08:54:14 :END_DATE 1 11 TIMESTAMP 2022-08-11 00:00:00.000000000
203 rows selected.
--//很明显不存在参数即有date类型又有timestamp类型的情况。
SYS@192.168.100.235:1521/orcl> @ cntg LIS.LIS_TEST SENT_INST_FLAG
select count(*) , SENT_INST_FLAG from LIS.LIS_TEST group by SENT_INST_FLAG order by 1 desc;
COUNT(*) SENT_INST_FLAG
---------- --------------
5129342 0
3135070 1
2 rows selected.
SYS@192.168.100.235:1521/orcl> @ cntg LIS.LIS_TEST SENT_count
select count(*) , SENT_count from LIS.LIS_TEST group by SENT_count order by 1 desc;
COUNT(*) SENT_COUNT
---------- ----------
6186388 0
2059983 1
13864 2
2230 3
...
1 182
224 rows selected.
--//建立索引在SENT_INST_FLAG以及SENT_COUNT也是不合理的，也就是根据以上分析建立复合索引：TEST_DATE,INST_ID.而且不存在隐式
--//转换的问题。
CREATE INDEX LIS.i_LIS_TEST_TEST_DATE_INST_ID ON LIS.LIS_TEST
(TEST_DATE, INST_ID)
LOGGING
STORAGE (
BUFFER_POOL DEFAULT
FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT
)
NOPARALLEL;
--//再看执行计划，发现
SYS@192.168.100.235:1521/orcl> @ dpc 4qz6aykj6gq6v '' '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 4qz6aykj6gq6v, child number 1
-------------------------------------
select a.*,1 as TenantId from lis_test a where test_date=:end_date
and inst_id=:inst_id AND SENT_INST_FLAG = 0 AND SENT_COUNT |
