ToB企服应用市场:ToB评测及商务社交产业平台

标题: [20220811]奇怪的隐式转换问题.txt [打印本页]
作者: tsx81428    时间: 2022-9-16 17:17
标题: [20220811]奇怪的隐式转换问题.txt
[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



欢迎光临 ToB企服应用市场:ToB评测及商务社交产业平台 (https://dis.qidao123.com/) Powered by Discuz! X3.4