[20241221]SQL优化遇到的问题.txt

--//每年的年底我都会做一些sql优化工作，最近几年我非常不愿意做这类工作，主要不能体现自己的价值，特别是exadata环境。
--//不过在优化如下sql语句时遇到一些问题，主要是很久没有做做这类工作，有点生疏，做一个记录与总结。

1.环境：
SYS@127.0.0.1:9105/xtdb/xtdb1> @ver2
==============================
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.14.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.问题语句：
--//通过脚本生产问题语句执行脚本：

$ cat bzmg7z5br9tpg.sql9_0
variable STARTTIME VARCHAR2(32)
variable ENDTIME VARCHAR2(32)
begin
:STARTTIME := '2024/12/20 00:00:00';
:ENDTIME := '2024/12/21 00:00:00';
null;
end;
/
set termout off
set sqlblanklines on
alter session set current_schema=MEDSURGERY;
alter session set statistics_level=all;

SELECT *
  FROM (SELECT A.PATIENT_ID AS PATIENT_ID,
     VISIT_ID,
....
     THIRD_SUPPLY_NURSE,
     CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END AS START_DATE_TIME,
     OPERATION_NAME,
....
     A.OUT_PACU_DATE_TIME
     FROM MED_OPERATION_MASTER A
     LEFT JOIN MED_PAT_MASTER_INDEX B ON A.PATIENT_ID = B.PATIENT_ID) TEMP
 WHERE 1 = 1
    AND ((SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime)
     OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime)
     OR (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime))
    AND ( 1 = 2 OR operating_room = '138')
  ORDER BY OPER_STATUS, OPERATING_ROOM_NO,SEQUENCE,START_DATE_TIME ;
set termout on
set sqlblanklines off
--@zws '' ''
--@dpc '' '' ''
@dpc '' outline ''
rollback;
alter session set current_schema=SYS ;

--//简单说明，做了一些格式化处理，我觉得写代码这有点奇葩，后面的条件( 1 = 2 OR operating_room = '138')竟然使用or。
--//一般正常不会这样写，而是缺省1=1 然后使用and来补充或者替换相关过滤条件，里面如果没有岂不是要再加上一个1=1,变成
--//( 1 = 2 OR 1=1),因为operating_room = '138'经常变化，awr报表漏掉这类语句，这些语句累积起来，消耗还是很大的。
--//执行计划全表扫描MED_OPERATION_MASTER。

3.问题解决：
--//仔细看问题sql语句，可以发现只要建立三个日期字段的相关索引问题就应该解决了。
SYS@127.0.0.1:9106/xtdb/xtdb2> @ desczz MEDSURGERY.MED_OPERATION_MASTER IN_DATE_TIME,SCHEDULED_DATE_TIME,START_DATE_TIME
eXtended describe of MEDSURGERY.MED_OPERATION_MASTER

DISPLAY TABLE_NAME OF COLUMN_NAME INFORMATION.
INPUT   OWNER.TABLE_NAME  <filters>
SAMPLE  : @ desczz TABLE_NAME column_name1,column_name2
IF NOT INPUT <filters> ,USE "" .

Owner      Table_Name           SAMPLE_SIZE LAST_ANALYZED       Col# Column Name          Null? Type    NUM_DISTINCT        Density  NUM_NULLS HISTOGRAM NUM_BUCKETS Low_value           High_value
---------- -------------------- ----------- ------------------- ---- -------------------- ----- ------- ------------ -------------- ---------- --------- ----------- ------------------- -------------------
MEDSURGERY MED_OPERATION_MASTER       91261 2024-12-19 22:01:24   29 START_DATE_TIME            DATE(7)        78072   .00001280869      37559                     1 2022-05-26 08:50:00 2024-12-19 21:35:00
                                      91452 2024-12-19 22:01:24   53 IN_DATE_TIME               DATE(7)        74640   .00001339764      37368                     1 2022-05-26 07:20:00 2024-12-19 21:00:00
                                     128783 2024-12-19 22:01:24  122 SCHEDULED_DATE_TIME        DATE(7)        47948   .00002085593         37                     1 2022-05-18 10:00:00 2024-12-20 10:00:00
3 rows selected.

--//仔细检查发现SCHEDULED_DATE_TIME没有建立索引，想当然认为建立该字段索引问题应该解决，而且查询都是1天的时间间隔。
--//扫描3个相关索引就可以.再次测试后发现并没有使用相关索引，还是选择全表扫描。

--//然后加入如下提示：
BITMAP_TREE(@"SEL$C3EBEA37" "A"@"SEL$2" OR(1 1 ("MED_OPERATION_MASTER"."SCHEDULED_DATE_TIME") 2
("MED_OPERATION_MASTER"."START_DATE_TIME") 3 ("MED_OPERATION_MASTER"."IN_DATE_TIME")))

--//发现并不起作用，在这里开始浪费大量时间....
--//无意中注解 OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime) 这行，发现可以使用相关索引，这行注解简
--//单。取消注解就不能使用相关索引，至此陷入思维的很乱....

--//无意中检查发现Predicate Information (identified by operation id):段，发现出现如下信息：
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - storage(("A"."OPERATING_ROOM"='138' AND ((CASE  WHEN "A"."IN_DATE_TIME" IS NULL THEN "A"."SCHEDULED_DATE_TIME" ELSE
              "A"."IN_DATE_TIME" END >=:STARTTIME AND CASE  WHEN "A"."IN_DATE_TIME" IS NULL THEN "A"."SCHEDULED_DATE_TIME" ELSE "A"."IN_DATE_TIME" END
              <=:ENDTIME) OR ("A"."SCHEDULED_DATE_TIME">=:STARTTIME AND "A"."SCHEDULED_DATE_TIME"<:ENDTIME) OR ("A"."IN_DATE_TIME">=:STARTTIME AND
              "A"."IN_DATE_TIME"<=:ENDTIME))))
       filter(("A"."OPERATING_ROOM"='138' AND ((CASE  WHEN "A"."IN_DATE_TIME" IS NULL THEN "A"."SCHEDULED_DATE_TIME" ELSE
              "A"."IN_DATE_TIME" END >=:STARTTIME AND CASE  WHEN "A"."IN_DATE_TIME" IS NULL THEN "A"."SCHEDULED_DATE_TIME" ELSE "A"."IN_DATE_TIME" END
              <=:ENDTIME) OR ("A"."SCHEDULED_DATE_TIME">=:STARTTIME AND "A"."SCHEDULED_DATE_TIME"<:ENDTIME) OR ("A"."IN_DATE_TIME">=:STARTTIME AND
              "A"."IN_DATE_TIME"<=:ENDTIME))))
   5 - access("A"."PATIENT_ID"="B"."PATIENT_ID")

--//filter部分根本没有START_DATE_TIME字段信息，为什么？oracle不可能转换成这样的过滤条件。

--//我改写如下执行:
select * from  MED_OPERATION_MASTER A where
1 = 1
AND ((SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime)
OR (START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime)
OR (IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime))
AND ( 1 = 2 OR operating_room = '138');

--//ok，没有问题，到此已经浪费大量时间。
--//再仔细看sql语句才发现，编写者给我挖了一个坑,前面START_DATE_TIME被定义如下：
CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END AS START_DATE_TIME,
--//这样就很好理解为什么filter条件出现前面的情况。
--//也就是我要优化必须建立CASE WHEN IN_DATE_TIME IS NULL THEN SCHEDULED_DATE_TIME ELSE IN_DATE_TIME END的函数索引。

--//实际上仔细看发现这个条件(START_DATE_TIME >= :startTime AND START_DATE_TIME <= :endTime)是多余的。
--//IN_DATE_TIME是null 等于SCHEDULED_DATE_TIME，其他情况是IN_DATE_TIME。
--//这样已经涵盖在如下条件里面，完全可以取消这个条件。
(SCHEDULED_DATE_TIME >= :startTime AND SCHEDULED_DATE_TIME < :endTime)
(IN_DATE_TIME >= :startTime AND IN_DATE_TIME <= :endTime)

4.总结：
--//实际上这个问题很简单，主要问题在于我仅仅盯着谓词条件，没有主要开发重新定义START_DATE_TIME。
--//实际上我个人认为开发写代码不规范，不应该使用该表存在的字段START_DATE_TIME，哪怕定义为START_DATE_TIME1，问题都能很快
--//定位和发现.
--//而且自己在测试时，一开始没有主要执行计划的Predicate Information 信息,如果注意这些就不会走这些弯路，浪费大量时间。
--//至此以为问题已经接近解决，要么叫开发修改代码，要么建立相关索引。但是当我在21c下测试时，又遇到另外的情况。
--//太长另外写一篇blog分析。