一條sql語句的建議調優分析
前幾天開發的同事問我一個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 | | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
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
修改後的語句如下：
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 | | | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1705723/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL語句優化的34條建議(轉-浪花七八朵 )SQL優化
- oracle優化sql語句的一些建議Oracle優化SQL
- 一條sql語句的優化SQL優化
- 一條SQL語句的優化過程SQL優化
- 一條全表掃描sql語句的分析SQL
- sql語句的優化分析SQL優化
- 一條大sql的調優SQL
- sql語句的優化案例分析SQL優化
- 使用hint來調優sql語句SQL
- 一條SQL語句的書寫SQL
- 一條很 巧妙的 SQL 語句SQL
- 一條SQL語句的旅行之路SQL
- 一條sql語句優化不出來, 哭了一鼻子SQL優化
- SQL語句優化－－十條經驗SQL優化
- 使用USE_HASH Hint調優一個SQL語句SQL
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 一條sql語句的改進探索SQL
- 一條update語句的優化探索優化
- SQL語句優化技術分析SQL優化
- 一個SQL語句的優化SQL優化
- 一條簡單SQL語句的構成及語句解析SQL
- 一條簡單的sql語句執行15天的原因分析SQL
- 一條簡單的SQL語句優化-新年新氣象SQL優化
- 透過使用hint unnest調優sql語句SQL
- 通過使用hint unnest調優sql語句SQL
- 使用SQL調整顧問得到SQL優化建議SQL優化
- 一條sql語句的執行過程SQL
- ORACLE SQL語句優化技術分析OracleSQL優化
- 總結出10條SQL語句優化精髓SQL優化
- [20181114]一條sql語句的優化.txtSQL優化
- [20131025]一條sql語句的優化.txtSQL優化
- [20151203]一條sql語句的優化.txtSQL優化
- [20150715]一條sql語句的優化.txtSQL優化
- [20120319]一條sql語句的優化.txtSQL優化
- [20130319]一條sql語句的優化.txtSQL優化
- SQL語句的優化SQL優化
- Sql建表語句SQL
- sql 建表語句SQL