[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<3 and state < :state;
SYS@192.168.100.235:1521/orcl> @ 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<3 and
state < :state
Plan hash value: 557083498
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 52419 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 4 | 2784 | 52419 (1)| 00:00:03 |
|* 2 | INDEX SKIP SCAN | IX_LIS_TEST_TEST_NO__831848037 | 249 | | 52170 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2490
3 - (NUMBER): 90
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3))
2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)
filter(("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
42 rows selected.
--//索引的名字命名有点怪怪的IX_LIS_TEST_TEST_NO__831848037，好像sql profile提示建立的索引不应该是这样的格式，猜测该产品
--//早期可能在ms sqlserver下开发的。执行计划走的是INDEX SKIP SCAN，前面一个字段是test_no，自然执行效率低下。
--//另外注意下划线可以发现参数1类型timestamp类型，开始没有注意这个细节。
SYS@192.168.100.235:1521/orcl> @ ind2 LIS.IX_LIS_TEST_TEST_NO__831848037
Display indexes where table or index name matches LIS.IX_LIS_TEST_TEST_NO__831848037...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
LIS LIS_TEST IX_LIS_TEST_TEST_NO__831848037 1 TEST_NO
2 TEST_DATE
3 INST_ID
4 NO_PREFIX
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
LIS LIS_TEST IX_LIS_TEST_TEST_NO__831848037 NORMAL YES VALID NO N 3 52158 8238727 8238727 8217923 2022-08-09 22:19:23 1 VISIBLE
SYS@192.168.100.235:1521/orcl> @ descz LIS.LIS_TEST "column_name in ('TEST_NO','TEST_DATE','INST_ID','SENT_INST_FLAG','SENT_COUNT','STATE')"
eXtended describe of LIS.LIS_TEST
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ------------------- -------------------
LIS LIS_TEST 8701 2022-08-09 22:07:12 3 TEST_NO NOT NULL NUMBER(19,0) 237680 .00000400000 0 HYBRID 254 -9020 2103294101535845721
8701 2022-08-09 22:07:12 4 TEST_DATE NOT NULL DATE(7) 561 .00177900000 0 HYBRID 254 0001-01-01 00:00:00 9773-06-08 00:00:00
8238717 2022-08-09 22:07:12 5 INST_ID NOT NULL NUMBER(10,0) 226 .00000006069 0 FREQUENCY 226 1530 4090
8238717 2022-08-09 22:07:12 28 SENT_INST_FLAG NOT NULL NUMBER(10,0) 2 .00000006069 0 FREQUENCY 2 0 1
8238717 2022-08-09 22:07:12 29 SENT_COUNT NOT NULL NUMBER(10,0) 224 .00000006069 0 FREQUENCY 224 0 393
8238717 2022-08-09 22:07:12 108 STATE NUMBER(10,0) 5 .00000006069 0 FREQUENCY 5 60 100
6 rows selected.
--//呵呵还可以看出TEST_DATE存在垃圾数据。High_value=9773-06-08 00:00:00.
--//查看绑定变量值如下：
SYS@192.168.100.235:1521/orcl> @ 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<3 and
state < :state
Plan hash value: 2953700840
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 160 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 6 | 4176 | 160 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 346 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2890
3 - (NUMBER): 90
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3))
2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//你可以发现oracle可以使用我建立的索引。并且执行计划走INDEX RANGE SCAN使用I_LIS_TEST_TEST_DATE_INST_ID索引。
--//我手工生成脚本,注在sqlplus使用variable无法定义date，timestamp类型，只能使用字符串，注意带入日期的格式要符合要求：
SYS@192.168.100.235:1521/orcl> @b9 4qz6aykj6gq6v 0 1
variable END_DATE VARCHAR2(32)
variable INST_ID NUMBER
variable STATE NUMBER
begin
:END_DATE := '2022/08/11 00:00:00';
:INST_ID := 2490;
:STATE := 90;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=LIS;
alter session set statistics_level=all;
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<3 and state < :state;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
--//注我取消秒后面的000000000.不然报错我开始就是通过这里发现存在数据类型"转换"问题的。
--//执行计划如下：
SQL_ID 4qz6aykj6gq6v, child number 2
-------------------------------------
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<3 and
state < :state
Plan hash value: 2953700840
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 157 (100)| | 0 |00:00:00.01 | 335 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 6 | 4176 | 157 (0)| 00:00:01 | 0 |00:00:00.01 | 335 | 4 |
|* 2 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 1 | 338 | | 4 (0)| 00:00:01 | 522 |00:00:00.01 | 5 | 4 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=852): '2022/08/11 00:00:00'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2490
3 - (NUMBER): 90
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3))
2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)
--//可以发现也可以使用索引，但是我的测试无法反映真实的情况,我带入的:END_DATE是varchar2(32)。toad下执行也很麻烦，toad带入
--//的参数类型没有timestamp类型，我也不知道如何处理。我记忆里在itpub上问过，toad下如何使用timestamp数据类型，没人解答这
--//个问题.
--//我个人认为问题在于带入的timestamp类型没有秒后面的0值，就不会出现隐式转换。【后记：我有点想当然了】
--//也许oracle 19c版本支持这样的操作，当带入日期类型参数是timestamp类型时，如果秒后面的值全部是0，可以当作date类型使用。
--//导致没有存在隐式转换，我给在11g测试看看.
--//加入秒后面的0的情况。
$ cat 4qz6aykj6gq6v.sql9_0
variable END_DATE VARCHAR2(32)
variable INST_ID NUMBER
variable STATE NUMBER
begin
:END_DATE := '2022-08-11 00:00:00.000000000';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:INST_ID := 2490;
:STATE := 90;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=LIS;
alter session set statistics_level=all;
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<3 and state < :state;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
SYS@192.168.100.235:1521/orcl> @ 4qz6aykj6gq6v.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-----------------------------------------
Last statement has a PREV_SQL_ID of ZERO
2 rows selected.
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline bytes
argment : adaptive (12c) hint_report (19c)
Rollback complete.
Session altered.
--//执行报错!!也就是这样我才发现带入类型是timestamp类型。
--//还可以通过生产系统的其它语句验证我的测试有效，建立的I_LIS_TEST_TEST_DATE_INST_ID索引有用。
SYS@192.168.100.235:1521/orcl> @ sql_id 18xh7p6w137q5
--SQL_ID = 18xh7p6w137q5
select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1;
SYS@192.168.100.235:1521/orcl> @ bind_cap 18xh7p6w137q5 test_date
C200
-------------------------------------------------------------------------------------------------------------------------------------------------------------
select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ---------- ---------- ---------- ------------------- --------------- ------------ ------------------------------
18xh7p6w137q5 2 YES :TEST_DATE 1 11 2022-08-11 09:36:03 TIMESTAMP 2022-08-11 00:00:00.000000000
--//一样是timetamp类型。
SYS@192.168.100.235:1521/orcl> @ dpc 18xh7p6w137q5 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 18xh7p6w137q5, child number 2
-------------------------------------
select nvl(max(test_no),0) test_No from lis_test where
test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo
and :MaxNo and is_initial<=1
Plan hash value: 467228195
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 316 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 90 | 1800 | 316 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 689 | | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / LIS_TEST@SEL$1
4 - SEL$1 / LIS_TEST@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
2 - :2 (NUMBER): 1671
3 - (NUMBER): 1000
4 - (NUMBER): 2000
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:MAXNO>=:STARTNO)
3 - filter(("TEST_NO"<=:MAXNO AND "TEST_NO">=:STARTNO AND "IS_INITIAL"<=1))
4 - access("TEST_DATE"=:TEST_DATE AND "INST_ID"=:INST_ID)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//索引有效!!
SYS@192.168.100.235:1521/orcl> @ b9 18xh7p6w137q5 0 1
variable TEST_DATE VARCHAR2(32)
variable INST_ID NUMBER
variable STARTNO NUMBER
variable MAXNO NUMBER
begin
:TEST_DATE := '2022/08/11 00:00:00.000000000';
:INST_ID := 1725;
:STARTNO := 0;
:MAXNO := 300;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=LIS;
alter session set statistics_level=all;
select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
SYS@192.168.100.235:1521/orcl> @18xh7p6w137q5.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Last statement has a PREV_SQL_ID of ZERO
2 rows selected.
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline bytes
argment : adaptive (12c) hint_report (19c)
Rollback complete.
Session altered.
--//顺便改写如下：
select a.*,1 as TenantId from lis_test a where test_date=to_timestamp('2022-08-11 00:00:00.000000000','yyyy-mm-dd hh24:mi:ss.ff') and inst_id=1;
Plan hash value: 284515054
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 696 | 564 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 696 | 564 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 1 | | 563 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A"@"SEL$1")
INDEX_SS(@"SEL$1" "A"@"SEL$1" ("LIS_TEST"."TEST_DATE" "LIS_TEST"."INST_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("INST_ID"=1)
filter("INST_ID"=1 AND INTERNAL_FUNCTION("TEST_DATE")=TIMESTAMP' 2022-08-11 00:00:00.000000000')
--//可以验证发生隐式转换。
总结：
--//也就是在这种特殊的情况下隐式转换并没有发生，以前好像没有遇到类似的问题，看来19c做了一些改进，看来给在11g下测试看看。
--//生产系统遇到一个奇怪的隐式转换问题，问题在于没有发生隐式转换，做一个分析调查。
--//后记：后面的分析存在一下偏差，不更正当时的分析，在这里做一个补充，实际上从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<3 and state < :state;
SYS@192.168.100.235:1521/orcl> @ 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<3 and
state < :state
Plan hash value: 557083498
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 52419 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 4 | 2784 | 52419 (1)| 00:00:03 |
|* 2 | INDEX SKIP SCAN | IX_LIS_TEST_TEST_NO__831848037 | 249 | | 52170 (1)| 00:00:03 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2490
3 - (NUMBER): 90
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3))
2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)
filter(("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID))
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
42 rows selected.
--//索引的名字命名有点怪怪的IX_LIS_TEST_TEST_NO__831848037，好像sql profile提示建立的索引不应该是这样的格式，猜测该产品
--//早期可能在ms sqlserver下开发的。执行计划走的是INDEX SKIP SCAN，前面一个字段是test_no，自然执行效率低下。
--//另外注意下划线可以发现参数1类型timestamp类型，开始没有注意这个细节。
SYS@192.168.100.235:1521/orcl> @ ind2 LIS.IX_LIS_TEST_TEST_NO__831848037
Display indexes where table or index name matches LIS.IX_LIS_TEST_TEST_NO__831848037...
TABLE_OWNER TABLE_NAME INDEX_NAME POS# COLUMN_NAME DSC
-------------------- ------------------------------ ------------------------------ ---- ------------------------------ ----
LIS LIS_TEST IX_LIS_TEST_TEST_NO__831848037 1 TEST_NO
2 TEST_DATE
3 INST_ID
4 NO_PREFIX
INDEX_OWNER TABLE_NAME INDEX_NAME IDXTYPE UNIQ STATUS PART TEMP H LFBLKS NDK NUM_ROWS CLUF LAST_ANALYZED DEGREE VISIBILIT
-------------------- ------------------------------ ------------------------------ ---------- ---- -------- ---- ---- -- ---------- ------------- ---------- ---------- ------------------- ------ ---------
LIS LIS_TEST IX_LIS_TEST_TEST_NO__831848037 NORMAL YES VALID NO N 3 52158 8238727 8238727 8217923 2022-08-09 22:19:23 1 VISIBLE
SYS@192.168.100.235:1521/orcl> @ descz LIS.LIS_TEST "column_name in ('TEST_NO','TEST_DATE','INST_ID','SENT_INST_FLAG','SENT_COUNT','STATE')"
eXtended describe of LIS.LIS_TEST
DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT OWNER.TABLE_NAME <filters>
SAMPLE : @ TAB_LH TABLE_NAME "column_id between 3 and 5"
IF NOT INPUT <filters> ,USE "1=1" .
Owner Table_Name SAMPLE_SIZE LAST_ANALYZED Col# Column Name Null? Type NUM_DISTINCT Density NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ---------- -------------------- ------------ -------------- ---------- --------------- ----------- ------------------- -------------------
LIS LIS_TEST 8701 2022-08-09 22:07:12 3 TEST_NO NOT NULL NUMBER(19,0) 237680 .00000400000 0 HYBRID 254 -9020 2103294101535845721
8701 2022-08-09 22:07:12 4 TEST_DATE NOT NULL DATE(7) 561 .00177900000 0 HYBRID 254 0001-01-01 00:00:00 9773-06-08 00:00:00
8238717 2022-08-09 22:07:12 5 INST_ID NOT NULL NUMBER(10,0) 226 .00000006069 0 FREQUENCY 226 1530 4090
8238717 2022-08-09 22:07:12 28 SENT_INST_FLAG NOT NULL NUMBER(10,0) 2 .00000006069 0 FREQUENCY 2 0 1
8238717 2022-08-09 22:07:12 29 SENT_COUNT NOT NULL NUMBER(10,0) 224 .00000006069 0 FREQUENCY 224 0 393
8238717 2022-08-09 22:07:12 108 STATE NUMBER(10,0) 5 .00000006069 0 FREQUENCY 5 60 100
6 rows selected.
--//呵呵还可以看出TEST_DATE存在垃圾数据。High_value=9773-06-08 00:00:00.
--//查看绑定变量值如下：
SYS@192.168.100.235:1521/orcl> @ 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<3 and
state < :state
Plan hash value: 2953700840
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 160 (100)| |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 6 | 4176 | 160 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 346 | | 4 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2890
3 - (NUMBER): 90
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3))
2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//你可以发现oracle可以使用我建立的索引。并且执行计划走INDEX RANGE SCAN使用I_LIS_TEST_TEST_DATE_INST_ID索引。
--//我手工生成脚本,注在sqlplus使用variable无法定义date，timestamp类型，只能使用字符串，注意带入日期的格式要符合要求：
SYS@192.168.100.235:1521/orcl> @b9 4qz6aykj6gq6v 0 1
variable END_DATE VARCHAR2(32)
variable INST_ID NUMBER
variable STATE NUMBER
begin
:END_DATE := '2022/08/11 00:00:00';
:INST_ID := 2490;
:STATE := 90;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=LIS;
alter session set statistics_level=all;
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<3 and state < :state;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
--//注我取消秒后面的000000000.不然报错我开始就是通过这里发现存在数据类型"转换"问题的。
--//执行计划如下：
SQL_ID 4qz6aykj6gq6v, child number 2
-------------------------------------
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<3 and
state < :state
Plan hash value: 2953700840
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 157 (100)| | 0 |00:00:00.01 | 335 | 4 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 6 | 4176 | 157 (0)| 00:00:01 | 0 |00:00:00.01 | 335 | 4 |
|* 2 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 1 | 338 | | 4 (0)| 00:00:01 | 522 |00:00:00.01 | 5 | 4 |
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (VARCHAR2(30), CSID=852): '2022/08/11 00:00:00'
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
2 - :2 (NUMBER): 2490
3 - (NUMBER): 90
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(("STATE"<:STATE AND "SENT_INST_FLAG"=0 AND "SENT_COUNT"<3))
2 - access("TEST_DATE"=:END_DATE AND "INST_ID"=:INST_ID)
--//可以发现也可以使用索引，但是我的测试无法反映真实的情况,我带入的:END_DATE是varchar2(32)。toad下执行也很麻烦，toad带入
--//的参数类型没有timestamp类型，我也不知道如何处理。我记忆里在itpub上问过，toad下如何使用timestamp数据类型，没人解答这
--//个问题.
--//我个人认为问题在于带入的timestamp类型没有秒后面的0值，就不会出现隐式转换。【后记：我有点想当然了】
--//也许oracle 19c版本支持这样的操作，当带入日期类型参数是timestamp类型时，如果秒后面的值全部是0，可以当作date类型使用。
--//导致没有存在隐式转换，我给在11g测试看看.
--//加入秒后面的0的情况。
$ cat 4qz6aykj6gq6v.sql9_0
variable END_DATE VARCHAR2(32)
variable INST_ID NUMBER
variable STATE NUMBER
begin
:END_DATE := '2022-08-11 00:00:00.000000000';
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
:INST_ID := 2490;
:STATE := 90;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=LIS;
alter session set statistics_level=all;
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<3 and state < :state;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
SYS@192.168.100.235:1521/orcl> @ 4qz6aykj6gq6v.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
-----------------------------------------
Last statement has a PREV_SQL_ID of ZERO
2 rows selected.
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline bytes
argment : adaptive (12c) hint_report (19c)
Rollback complete.
Session altered.
--//执行报错!!也就是这样我才发现带入类型是timestamp类型。
--//还可以通过生产系统的其它语句验证我的测试有效，建立的I_LIS_TEST_TEST_DATE_INST_ID索引有用。
SYS@192.168.100.235:1521/orcl> @ sql_id 18xh7p6w137q5
--SQL_ID = 18xh7p6w137q5
select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1;
SYS@192.168.100.235:1521/orcl> @ bind_cap 18xh7p6w137q5 test_date
C200
-------------------------------------------------------------------------------------------------------------------------------------------------------------
select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1
SQL_ID CHILD_NUMBER WAS NAME POSITION MAX_LENGTH LAST_CAPTURED DATATYPE_STRING VALUE_STRING C30
------------- ------------ --- ---------- ---------- ---------- ------------------- --------------- ------------ ------------------------------
18xh7p6w137q5 2 YES :TEST_DATE 1 11 2022-08-11 09:36:03 TIMESTAMP 2022-08-11 00:00:00.000000000
--//一样是timetamp类型。
SYS@192.168.100.235:1521/orcl> @ dpc 18xh7p6w137q5 ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 18xh7p6w137q5, child number 2
-------------------------------------
select nvl(max(test_no),0) test_No from lis_test where
test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo
and :MaxNo and is_initial<=1
Plan hash value: 467228195
-----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
-----------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 316 (100)| |
| 1 | SORT AGGREGATE | | 1 | 20 | | |
|* 2 | FILTER | | | | | |
|* 3 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 90 | 1800 | 316 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 689 | | 5 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
3 - SEL$1 / LIS_TEST@SEL$1
4 - SEL$1 / LIS_TEST@SEL$1
Peeked Binds (identified by position):
--------------------------------------
1 - :1 (TIMESTAMP): [Not Printable]
2 - :2 (NUMBER): 1671
3 - (NUMBER): 1000
4 - (NUMBER): 2000
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(:MAXNO>=:STARTNO)
3 - filter(("TEST_NO"<=:MAXNO AND "TEST_NO">=:STARTNO AND "IS_INITIAL"<=1))
4 - access("TEST_DATE"=:TEST_DATE AND "INST_ID"=:INST_ID)
Note
-----
- Warning: basic plan statistics not available. These are only collected when:
* hint 'gather_plan_statistics' is used for the statement or
* parameter 'statistics_level' is set to 'ALL', at session or system level
--//索引有效!!
SYS@192.168.100.235:1521/orcl> @ b9 18xh7p6w137q5 0 1
variable TEST_DATE VARCHAR2(32)
variable INST_ID NUMBER
variable STARTNO NUMBER
variable MAXNO NUMBER
begin
:TEST_DATE := '2022/08/11 00:00:00.000000000';
:INST_ID := 1725;
:STARTNO := 0;
:MAXNO := 300;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=LIS;
alter session set statistics_level=all;
select nvl(max(test_no),0) test_No from lis_test where test_date=:test_date and inst_id=:inst_id and TEST_NO between :StartNo and :MaxNo and is_initial<=1;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' ''
@dpc '' outline
rollback;
alter session set current_schema=SYS ;
SYS@192.168.100.235:1521/orcl> @18xh7p6w137q5.sql9_0
PL/SQL procedure successfully completed.
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Last statement has a PREV_SQL_ID of ZERO
2 rows selected.
argment : typical all advanced partition predicate remote note parallel projection alias peeked_binds outline bytes
argment : adaptive (12c) hint_report (19c)
Rollback complete.
Session altered.
--//顺便改写如下：
select a.*,1 as TenantId from lis_test a where test_date=to_timestamp('2022-08-11 00:00:00.000000000','yyyy-mm-dd hh24:mi:ss.ff') and inst_id=1;
Plan hash value: 284515054
---------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time |
---------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 696 | 564 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| LIS_TEST | 1 | 696 | 564 (0)| 00:00:01 |
|* 2 | INDEX SKIP SCAN | I_LIS_TEST_TEST_DATE_INST_ID | 1 | | 563 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1 / A@SEL$1
2 - SEL$1 / A@SEL$1
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$1" "A"@"SEL$1")
INDEX_SS(@"SEL$1" "A"@"SEL$1" ("LIS_TEST"."TEST_DATE" "LIS_TEST"."INST_ID"))
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('19.1.0')
OPTIMIZER_FEATURES_ENABLE('19.1.0')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("INST_ID"=1)
filter("INST_ID"=1 AND INTERNAL_FUNCTION("TEST_DATE")=TIMESTAMP' 2022-08-11 00:00:00.000000000')
--//可以验证发生隐式转换。
总结：
--//也就是在这种特殊的情况下隐式转换并没有发生，以前好像没有遇到类似的问题，看来19c做了一些改进，看来给在11g下测试看看。
