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

[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分析。