前幾天開發的同僚問我一個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
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
一般看到這個問題，感覺笛卡爾積性能是非常差的，這個也是相對的。至少從謂詞資訊來看，優化器還是在内部做了不少的工作，不能直接就說笛卡爾積是低效的。對于笛卡爾積的情況，在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"
"TRANSACTION_ID">=:3 AND "TRANSACTION_ID"
14 - access("TRANSACTION_ID">=:3 AND "TRANSACTION_ID"
filter("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 =xxx要好很多，而且可控性要好很多。
是以對于過濾條件啊的部分，建議是 (transaction >= and transaction
最後是一個補充的建議，即關鍵的表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
修改後的語句如下：
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
(SELECT /*+ INDEX(TRANSACTION_LOG,
TRANSACTION_LOG_1IX) PARALLEL_INDEX(TRANSACTION_LOG, 8) */
WHERE ((TRANSACTION_ID >= :1 and TRANSACTION_ID
and TRANSACTION_LOG.partition_id in ()
(SELECT /*+cardinality(1)*/
WHERE ACCOUNT.ACCOUNT_ID = TRX_LOG.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 | | | | | |
![oracle中的start with connect by用法[圖]](data:image/gif;base64,R0lGODlhAQABAIAAAP///wAAACwAAAAAAQABAAACAkQBADs=)