前几天开发的同事问我一个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.html 谓词信息如下:
Predicate 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
-------------------- --------- --------------- ------------------------------ ----------- ------------ ------
TRANSACTION_LOG RANGE 366 PERIOD_KEY,PARTITION_ID 2 0 VALID
当前表的查询语句只使用到了period_key,如果能够使用到partition_id,会更加高效,所以建议增加一个条件为partition_id
修改后的语句如下:
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_LOG
WHERE ACCOUNT.ACCOUNT_ID = TRX_LOG.ACCOUNT_ID
AND ACCOUNT.ACCOUNT_ID = ACCOUNT_EXT.ACCOUNT_ID
ORDER 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 | | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------