当前位置：首页 > news >正文

一条sql语句的建议调优分析(r5笔记第73天)

news 来源：原创 2024/5/17 17:41:22

前几天开发的同事问我一个sql的问题，目前在测试环境中发现这条sql语句执行时间很长，希望我们能够给一些建议，能够尽快做一些改进。sql语句类似下面的形式。SELECT /*+ INDEX(ACCOUNT,ACCOUNT_PK)INDEX(ACCOUNT_EXT ACCOUNT_EXT_PK) */ ACCOUNT.ACCOUNT_ID, ACCOUNT.BE, ACCOUNT.CUSTOMER_NO, ACCOUNT.AR_BALANCE, ACCOUNT_EXT.CYCLE_CODE, ACCOUNT_EXT.CYCLE_MONTH, ACCOUNT_EXT.CYCLE_YEAR, TRX_LOG.MAX_TRX_ID, ACCOUNT.L3_AGREEMENT_ID, ACCOUNT_EXT.UNBILLED_OC_AMT, ACCOUNT_EXT.UB_PEND_CRD, ACCOUNT_EXT.BILLED_UNCONF_OC, ACCOUNT_EXT.BILLED_UNCONF_RC, ACCOUNT_EXT.BILLED_UNCONF_UC, NVL(DISPUTE_BALANCE, 0), ACCOUNT.L9_CRD_LMT_CALC_FORMULA FROM ACCOUNT, ACCOUNT_EXT, (SELECT /*+ NO_MERGE INDEX(TRANSACTION_LOG, TRANSACTION_LOG_1IX) PARALLEL(TRANSACTION_LOG, 8) */ MAX(TRANSACTION_ID) MAX_TRX_ID, ACCOUNT_ID FROM TRANSACTION_LOG WHERE ((TRANSACTION_ID >= :1 and sys_creation_date <= to_date(to_char(sysdate - :2 / 24 / 60 / 60, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) OR (TRANSACTION_ID >= :3 AND TRANSACTION_ID <= :4 AND DL_UPDATE_STAMP = 0)) and (TRANSACTION_LOG.PERIOD_KEY in (:5, :6, :7)) AND TRANSACTION_LOG.TRANS_TYPE IN (SELECT /*+ cardinality(1)*/ DISTINCT column_value as transType FROM table (SELECT CAST(:8 AS Varchar2Array_tp) FROM DUAL)) GROUP BY ACCOUNT_ID) TRX_LOG WHERE ACCOUNT.ACCOUNT_ID = TRX_LOG.ACCOUNT_ID AND ACCOUNT.ACCOUNT_ID = ACCOUNT_EXT.ACCOUNT_ID ORDER BY TRX_LOG.MAX_TRX_ID可以看出sql语句似乎是有调优的痕迹的，但是从执行计划来看，似乎还是有些地方出现了问题。执行计划如下：----------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |----------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 11076 | 941K| | 445K (1)| 01:29:05 | | || 1 | SORT ORDER BY | | 11076 | 941K| 1240K| 445K (1)| 01:29:05 | | || 2 | NESTED LOOPS | | | | | | | | || 3 | NESTED LOOPS | | 11076 | 941K| | 445K (1)| 01:29:02 | | || 4 | NESTED LOOPS | | 11076 | 594K| | 444K (1)| 01:28:49 | | || 5 | VIEW | | 11076 | 205K| | 442K (1)| 01:28:35 | | || 6 | HASH GROUP BY | | 11076 | 389K| | | | | || 7 | CONCATENATION | | | | | | | | || 8 | NESTED LOOPS | | 1510K| 51M| | 263K (1)| 00:52:39 | | || 9 | PARTITION RANGE INLIST | | 5549 | 184K| | 166K (1)| 00:33:21 |KEY(I) |KEY(I) ||* 10 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG | 5549 | 184K| | 166K (1)| 00:33:21 |KEY(I) |KEY(I) ||* 11 | INDEX RANGE SCAN | TRANSACTION_LOG_1IX | 2436K| | | 1811 (1)| 00:00:22 |KEY(I) |KEY(I) ||* 12 | COLLECTION ITERATOR PICKLER FETCH | | 272 | 544 | | 17 (0)| 00:00:01 | | || 13 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | || 14 | NESTED LOOPS | | 1506K| 51M| | 179K (1)| 00:35:56 | | || 15 | PARTITION RANGE INLIST | | 5535 | 183K| | 83402 (1)| 00:16:41 |KEY(I) |KEY(I) ||* 16 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG | 5535 | 183K| | 83402 (1)| 00:16:41 |KEY(I) |KEY(I) ||* 17 | INDEX RANGE SCAN | TRANSACTION_LOG_1IX | 1218K| | | 942 (1)| 00:00:12 |KEY(I) |KEY(I) ||* 18 | COLLECTION ITERATOR PICKLER FETCH | | 272 | 544 | | 17 (0)| 00:00:01 | | || 19 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 | | || 20 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 36 | | 1 (0)| 00:00:01 | | ||* 21 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | | | 1 (0)| 00:00:01 | | ||* 22 | INDEX UNIQUE SCAN | AR9_ACCOUNT_EXT_PK | 1 | | | 1 (0)| 00:00:01 | | || 23 | TABLE ACCESS BY INDEX ROWID | AR9_ACCOUNT_EXT | 1 | 32 | | 1 (0)| 00:00:01 | | |----------------------------------------------------------------------------------------------------------------------------------------------对于这条语句的性能瓶颈还是在于下面的子查询，根据执行计划可以看到走了笛卡尔积。((TRANSACTION_ID >= :1 and sys_creation_date <= to_date(to_char(sysdate - :2 / 24 / 60 / 60, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) OR (TRANSACTION_ID >= :3 AND TRANSACTION_ID <= :4 AND DL_UPDATE_STAMP = 0))一般看到这个问题，感觉笛卡尔积性能是非常差的，这个也是相对的。至少从谓词信息来看，优化器还是在内部做了不少的工作，不能直接就说笛卡尔积是低效的。对于笛卡尔积的情况，在itpub中也有一些帖子有相关的讨论，可以参考。http://www.itpub.net/thread-1511375-4-1.htmlPredicate Information (identified by operation id):--------------------------------------------------- 9 - filter(("TRANSACTION_LOG"."PERIOD_KEY"=:5 OR "TRANSACTION_LOG"."PERIOD_KEY"=:6 OR "TRANSACTION_LOG"."PERIOD_KEY"=:7) AND ("TRANSACTION_ID">=:1 AND "SYS_CREATION_DATE"<=TO_DATE(TO_CHAR(SYSDATE@!-:2/24/60/60,'yyyy-mm-dd hh24:mi:ss'),'yyyy-mm-dd hh24:mi:ss') OR "TRANSACTION_ID">=:3 AND "TRANSACTION_ID"<=:4 AND "DL_UPDATE_STAMP"=0)) 14 - access("TRANSACTION_ID">=:3 AND "TRANSACTION_ID"<=:4) filter("TRANSACTION_ID"<=:4 AND "TRANSACTION_ID">=:3) 17 - access("TRANSACTION_ID">=:1) filter("TRANSACTION_ID">=:1) 18 - filter("TRANSACTION_LOG"."TRANS_TYPE"=VALUE(KOKBF$)) 21 - access("ACCOUNT"."ACCOUNT_ID"="TRX_LOG"."ACCOUNT_ID") 22 - access("ACCOUNT"."ACCOUNT_ID"="ACCOUNT_EXT"."ACCOUNT_ID")对于这条语句的调优来说，尽管空间很小，但是还有一些改进的地方。从调优的Hint来看，有些hint其实是没有使用到的，比如并行的hint,其实这个时候还是能够合理利用起来。改为 parallel_index PARALLEL_INDEX(TRANSACTION_LOG, 8)接着就是性能瓶颈的过滤条件了，其实过滤条件中最好还是能够有一个范围id的情况，比如(transaction_id >= and transaction_id <=xx 这种情况要比只是指定transaction_id>=xxx要好很多，而且可控性要好很多。所以对于过滤条件啊的部分，建议是 (transaction >= and transaction <=xx)的形式。最后是一个补充的建议，即关键的表TRANSACTION_LOG 是一个分区表，所以可以尽可能的使用分区键值。TABLE_NAME PARTITION PARTITION_COUNT COLUMN_LIST PART_COUNTS SUBPAR_COUNT STATUS修改后的语句如下：SELECT /*+ INDEX(ACCOUNT,ACCOUNT_PK)INDEX(ACCOUNT_EXT ACCOUNT_EXT_PK) */ACCOUNT.ACCOUNT_ID,ACCOUNT.BE,ACCOUNT.CUSTOMER_NO,ACCOUNT.AR_BALANCE,ACCOUNT_EXT.CYCLE_CODE,ACCOUNT_EXT.CYCLE_MONTH,ACCOUNT_EXT.CYCLE_YEAR,TRX_LOG.MAX_TRX_ID,ACCOUNT.L3_AGREEMENT_ID,ACCOUNT_EXT.UNBILLED_OC_AMT,ACCOUNT_EXT.UB_PEND_CRD,ACCOUNT_EXT.BILLED_UNCONF_OC,ACCOUNT_EXT.BILLED_UNCONF_RC,ACCOUNT_EXT.BILLED_UNCONF_UC,NVL(DISPUTE_BALANCE, 0),ACCOUNT.L9_CRD_LMT_CALC_FORMULA FROM ACCOUNT, ACCOUNT_EXT, (SELECT /*+ INDEX(TRANSACTION_LOG, TRANSACTION_LOG_1IX) PARALLEL_INDEX(TRANSACTION_LOG, 8) */ MAX(TRANSACTION_ID) MAX_TRX_ID, ACCOUNT_ID FROM TRANSACTION_LOG WHERE ((TRANSACTION_ID >= :1 and TRANSACTION_ID <= :4 and sys_creation_date <= to_date(to_char(sysdate - :2 / 24 / 60 / 60, 'yyyy-mm-dd hh24:mi:ss'), 'yyyy-mm-dd hh24:mi:ss')) OR (TRANSACTION_ID >= :3 AND TRANSACTION_ID <= :4 AND DL_UPDATE_STAMP = 0)) and (TRANSACTION_LOG.PERIOD_KEY in (:5, :6, :7)) and TRANSACTION_LOG.partition_id in () AND TRANSACTION_LOG.TRANS_TYPE IN (SELECT /*+cardinality(1)*/ DISTINCT column_value as transType FROM table (SELECT CAST(:8 AS Varchar2Array_tp) FROM DUAL)) GROUP BY ACCOUNT_ID) TRX_LOGWHERE ACCOUNT.ACCOUNT_ID = TRX_LOG.ACCOUNT_ID AND ACCOUNT.ACCOUNT_ID = ACCOUNT_EXT.ACCOUNT_IDORDER BY TRX_LOG.MAX_TRX_id修改后的执行计划如下：Execution plan as below.---------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |---------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 530 | 46110 | 24465 (1)| 00:04:54 | | | | | || 1 | SORT ORDER BY | | 530 | 46110 | 24465 (1)| 00:04:54 | | | | | || 2 | NESTED LOOPS | | | | | | | | | | || 3 | NESTED LOOPS | | 530 | 46110 | 24464 (1)| 00:04:54 | | | | | || 4 | NESTED LOOPS | | 530 | 29150 | 24457 (1)| 00:04:54 | | | | | || 5 | VIEW | | 530 | 10070 | 176K (87)| 00:35:13 | | | | | || 6 | HASH GROUP BY | | 530 | 20670 | | | | | | | || 7 | CONCATENATION | | | | | | | | | | || 8 | NESTED LOOPS | | 6867 | 261K| 83837 (1)| 00:16:47 | | | | | || 9 | PX COORDINATOR | | | | | | | | | | || 10 | PX SEND QC (RANDOM) | :TQ10000 | 25 | 925 | 83400 (1)| 00:16:41 | | | Q1,00 | P->S | QC (RAND) || 11 | PX PARTITION RANGE INLIST | | 25 | 925 | 83400 (1)| 00:16:41 |KEY(I) |KEY(I) | Q1,00 | PCWC | ||* 12 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG | 25 | 925 | 83400 (1)| 00:16:41 |KEY(I) |KEY(I) | Q1,00 | PCWP | ||* 13 | INDEX RANGE SCAN | TRANSACTION_LOG_1IX | 1218K| | 942 (1)| 00:00:12 |KEY(I) |KEY(I) | Q1,00 | PCWP | ||* 14 | COLLECTION ITERATOR PICKLER FETCH | | 272 | 544 | 17 (0)| 00:00:01 | | | | | || 15 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | | | || 16 | NESTED LOOPS | | 137K| 5229K| 92165 (1)| 00:18:26 | | | | | || 17 | PX COORDINATOR | | | | | | | | | | || 18 | PX SEND QC (RANDOM) | :TQ20000 | 504 | 18648 | 83400 (1)| 00:16:41 | | | Q2,00 | P->S | QC (RAND) || 19 | PX PARTITION RANGE INLIST | | 504 | 18648 | 83400 (1)| 00:16:41 |KEY(I) |KEY(I) | Q2,00 | PCWC | ||* 20 | TABLE ACCESS BY LOCAL INDEX ROWID| TRANSACTION_LOG | 504 | 18648 | 83400 (1)| 00:16:41 |KEY(I) |KEY(I) | Q2,00 | PCWP | ||* 21 | INDEX RANGE SCAN | TRANSACTION_LOG_1IX | 1218K| | 942 (1)| 00:00:12 |KEY(I) |KEY(I) | Q2,00 | PCWP | ||* 22 | COLLECTION ITERATOR PICKLER FETCH | | 272 | 544 | 17 (0)| 00:00:01 | | | | | || 23 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | | | || 24 | TABLE ACCESS BY INDEX ROWID | ACCOUNT | 1 | 36 | 1 (0)| 00:00:01 | | | | | ||* 25 | INDEX UNIQUE SCAN | ACCOUNT_PK | 1 | | 1 (0)| 00:00:01 | | | | | ||* 26 | INDEX UNIQUE SCAN | ACCOUNT_EXT_PK | 1 | | 1 (0)| 00:00:01 | | | | | || 27 | TABLE ACCESS BY INDEX ROWID | ACCOUNT_EXT | 1 | 32 | 1 (0)| 00:00:01 | | | | | |---------------------------------------------------------------------------------------------------------------------------------------------------------