问题描述
巡检发现一条Top SQL，单次执行时间为885秒，平均逻辑读消耗为681w，消耗较高，需要进行优化分析
SQL消耗
PLAN_HASH_VALUE ELAPSED_TIME CPU_TIME EXECUTIONS DISK_READS BUFFER_GETS ROWS_PROCESSED
-------------------- ------------------- ------------------- ---------------- ------------------- ------------------- -------------------
4210101381 3,541,841.00 680,447.00 4 11,820,941.00 27,258,033.00 0.00
885,460.25 170,111.75 2,955,235.25 6,814,508.25 0.00
SQL文本
select ...... --省略多个字段
from UM_OFFER_02 b
where to_char(VALID_DATE,'YYYY-MM-DD') = to_char(sysdate+3,'YYYY-MM-DD')
and EXPIRE_DATE > sysdate
and not exists( select 1 from UM_NOTIFY_LOG_MONTHLY a
where TASK_CODE=:1
and MONTH=:2
and b.OFFER_INS_ID = a.OBJ_INS_ID
)
and rownum<=2000
执行计划
Execution Plan
----------------------------------------------------------
Plan hash value: 4210101381
---------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 26 | 5382 | | 21758 (1)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
|* 2 | HASH JOIN RIGHT ANTI | | 26 | 5382 | 17M| 21758 (1)| 00:00:01 | | |
| 3 | TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED| UM_NOTIFY_LOG_MONTHLY | 481K| 12M| | 17188 (1)| 00:00:01 | ROWID | ROWID |
|* 4 | INDEX RANGE SCAN | IDX_UM_NOTIFY_LOG_MONTHLY1 | 481K| | | 3245 (1)| 00:00:01 | | |
| 5 | PARTITION RANGE ALL | | 2628 | 230K| | 3762 (1)| 00:00:01 | 1 | 10 |
|* 6 | TABLE ACCESS STORAGE FULL FIRST ROWS | UM_OFFER_02 | 2628 | 230K| | 3762 (1)| 00:00:01 | 1 | 10 |
---------------------------------------------------------------------------------------------------------------------------------------------------
问题分析
执行计划主要为：
A表全局索引扫描回表的结果集
Hash右反连接
B表全分区全表扫描的结果集
性能瓶颈可能在全表扫，回表等步骤；
接下来统计下数据量，验证下是执行计划是否正确；
数据量统计
SQL> select count(*) from UCR_FILE1.UM_NOTIFY_LOG_MONTHLY a;
COUNT(*)
----------
21155215
SQL> select count(*) from UCR_FILE1.UM_NOTIFY_LOG_MONTHLY a where TASK_CODE=:r1 and MONTH=:r2;
COUNT(*)
----------
111691
SQL> select /*+FULL(B)*/ count(*) from UCR_FILE1.UM_OFFER_02 b;
COUNT(*)
----------
532743551
SQL> select /*+FULL(B)*/ count(*) from UCR_FILE1.UM_OFFER_02 b where to_char(VALID_DATE,'YYYY-MM-DD') = to_char(sysdate+3,'YYYY-MM-DD') and EXPIRE_DATE > sysdate;
COUNT(*)
----------
0
A表约2115w条数据，谓词条件过滤后，返回11w条记录，走索引合理；
B表约5亿条数据，谓词条件过滤后，返回0条记录，可知走全表扫描是不合理；
为了优化全表扫描，要看看B表上相关列是否适合建索引？
表相关信息
A表：
Table Number Empty Chain Average Global Sample
Name of Rows Blocks Blocks Count Row Len Stats Size LAST_ANALYZED
------------------------------ -------------- ---------------- ------------ -------- ------- ------ -------------- --------------------
UM_NOTIFY_LOG_MONTHLY 20,234,119 578,665 0 0 203 YES 20,234,119 2024-08-30 22:07
Column Distinct Null Number Number Sample
Name DATA_TYPE Values able Density Buckets Nulls AVG_COL_LEN Size LAST_ANALYZED
------------------------------ --------------- -------------- ---- ----------- ------- -------------- ----------- -------------- --------------------
MONTH NUMBER 7 Y .00000002 7 0 3 20,234,119 2024-08-30 22:07
OBJ_INS_ID NUMBER 17,942,528 Y .00000006 1 0 10 20,234,119 2024-08-30 22:07
TASK_CODE VARCHAR2 6 Y .00000002 6 0 14 20,234,119 2024-08-30 22:07
Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
IDX_UM_NOTIFY_LOG_MONTHLY1 MONTH 1 NUMBER(6,0)
TASK_CODE 2 VARCHAR2(100)
IDX_UM_NOTIFY_LOG_MONTHLY_OD OBJ_INS_ID 1 NUMBER(16,0)
DEAL_TAG 2 VARCHAR2(1)
B表：
Table Number Empty Chain Average Global Sample
Name of Rows Blocks Blocks Count Row Len Stats Size LAST_ANALYZED
------------------------------ -------------- ---------------- ------------ -------- ------- ------ -------------- --------------------
UM_OFFER_02 518,424,515 10,583,633 0 0 135 YES 518,424,515 2024-07-13 06:26
Column Distinct Null Number Number Sample
Name DATA_TYPE Values able Density Buckets Nulls AVG_COL_LEN Size LAST_ANALYZED
------------------------------ --------------- -------------- ---- ----------- ------- -------------- ----------- -------------- --------------------
EXPIRE_DATE DATE 27,705,344 Y .00000000 254 0 8 6,853 2024-07-13 06:26
OFFER_INS_ID NUMBER 518,424,515 N .00000000 254 0 10 6,853 2024-07-13 06:26
VALID_DATE DATE 81,674,240 Y .00000000 254 0 8 6,853 2024-07-13 06:26 0 8 6,853 2024-07-13 06:26
Index Column Col Column
Name Name Pos Details
------------------------------ ------------------------------ ---- ------------------------
IDX_UM_OFFER_02_OIID OFFER_INS_ID 1 NUMBER(16,0) NOT NULL
B表相关列只有OFFER_INS_ID存在索引，且该列为两个表的连接列，想走这个索引，需要A表嵌套循环B表，从A表返回的数据量来看，显然不合适，可以排除嵌套循环走OFFER_INS_ID列的索引；
需要在其他列上建索引，从两个日期列的基数及业务含义上考虑，适合创建(VALID_DATE,EXPIRE_DATE)的复合索引，但VALID_DATE列上使用了函数，只能单列或函数索引了吗？这样效果是远不如复合索引的；
逻辑改写
再次分析了下SQL，发现可以做下逻辑改写，从而去掉VALID_DATE列的函数
原条件：to_char(VALID_DATE,'YYYY-MM-DD') = to_char(sysdate+3,'YYYY-MM-DD')
含义为：有效日期的年月日等于当前日期三天后的年月日，
DATE字段类型是包含了年月日时分秒的，这里忽略了时分秒的比较，
意味着有效日期只要在当前日期三天后的0时0分0秒到23时59分59秒范围内都满足，
改写为：VALID_DATE >= trunc(sysdate+3) and VALID_DATE < trunc(sysdate+4)
优化建议
在B表上创建(VALID_DATE,EXPIRE_DATE)的复合索引，并将VALID_DATE条件to_char(VALID_DATE,'YYYY-MM-DD') = to_char(sysdate+3,'YYYY-MM-DD')改写为VALID_DATE >= trunc(sysdate+3) and VALID_DATE < trunc(sysdate+4)
结论
经过优化后SQL执行时间与消耗降低，性能提升明显；
函数索引适用场景单一，而该条SQL中单列索引优化效果不如复合索引；
因此再遇到此类情况，可以对SQL逻辑进行分析，看看能否做下逻辑改写；
「喜欢这篇文章，您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容，转载时必须标注文章的来源（墨天轮），文章链接，文章作者等基本信息，否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容，欢迎发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
