通過使用hint unnest調優sql語句
生產環境中有一條sql語句通過sql_monitor看到執行的時間實在是太驚人了，竟然達到了13個小時，而且還沒有執行完。
sql語句如下：
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
WHERE CYC_CUST.PERIOD_KEY = :periodKey
AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
AND EXISTS
(SELECT 1
FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
WHERE PAYER.PERIOD_KEY = :periodKey
AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
AND PAYER.DB_STATUS = 'BL'
AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
AND PAYER.FORMAT_EXT_DATE IS NULL
AND DOC.PERIOD_KEY = :periodKey
AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.BA_NO = DOC.BA_NO
AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))
檢視執行計劃沒有發現很嚴重的資源消耗。但是實際的執行情況怎麼和執行計劃相差甚遠。預計8分鐘，實際上十多個小時還沒有執行完。
Plan hash value: 3506320481
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 42048 (1)| 00:08:25 | | |
| 1 | SORT AGGREGATE | | 1 | 24 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 24 | 42046 (1)| 00:08:25 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 15 | 42045 (1)| 00:08:25 | 171 | 171 |
|* 6 | TABLE ACCESS FULL | BL1_CYCLE_CUSTOMERS | 1 | 15 | 42045 (1)| 00:08:25 | 171 | 171 |
|* 7 | INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 52 | 2 (0)| 00:00:01 | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 34 | 1 (0)| 00:00:01 | 171 | 171 |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_CYC_PAYER_POP | 1 | 34 | 1 (0)| 00:00:01 | 171 | 171 |
|* 13 | INDEX RANGE SCAN | BL1_CYC_PAYER_POP_1IX | 3 | | 1 (0)| 00:00:01 | 171 | 171 |
| 14 | PARTITION RANGE SINGLE | | 7 | | 1 (0)| 00:00:01 | 171 | 171 |
|* 15 | INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 7 | | 1 (0)| 00:00:01 | 171 | 171 |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_DOCUMENT | 1 | 18 | 1 (0)| 00:00:01 | 171 | 171 |
--------------------------------------------------------------------------------------------------------------------------------
這個時候可以通過sql monitor得到一個相對比較準確的資源使用情況。
一看IO請求達21M次，約等於160.9G左右的資料量。
從sql語句的執行計劃可以看出，語句可以分為兩大部分，一部分是exist字句上面的部分，兩個大表做了關聯，得到了相關的customer_no然後在exists字句中繼續關聯。
大量的IO請求都消耗在BL1_CUSTOMER,其實這個表實際上資料量近千萬，還沒有80G多G，但是傳送的IO請求累計的資料量卻已經超過了80G，佔到了整個IO請求數的一半以上。消耗的CPU資源也在73%以上
可以通過禁用子查詢解巢狀來做為一種調優思路，優先從子查詢中先輸出資料來。
而BL1_CYCLE_PAYER_POP表作為一個重要的關聯表。子查詢中的條件AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO和外部查詢相關聯。
可以優先查詢這個表，考慮到執行的頻率和效能，新增了並行hint。
這樣sql語句就變為
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
WHERE CYC_CUST.PERIOD_KEY = :periodKey
AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
AND EXISTS
(SELECT /*+unnest full(payer) parallel(payer 4)*/1
FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
WHERE PAYER.PERIOD_KEY = :periodKey
AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
AND PAYER.DB_STATUS = 'BL'
AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
AND PAYER.FORMAT_EXT_DATE IS NULL
AND DOC.PERIOD_KEY = :periodKey
AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.BA_NO = DOC.BA_NO
AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))
優化後的執行計劃如下：
Plan hash value: 227985194
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 13688 (1)| 00:02:45 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 37 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 37 | | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 37 | | | | | Q1,01 | PCWP | |
| 5 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | |
| 6 | NESTED LOOPS | | 1 | 37 | 13688 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 28 | 13688 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 8 | VIEW | VW_SQ_1 | 1 | 13 | 13686 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 9 | HASH UNIQUE | | 1 | 52 | | | | | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 1 | 52 | | | | | Q1,01 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 1 | 52 | | | | | Q1,00 | P->P | HASH |
| 12 | HASH UNIQUE | | 1 | 52 | | | | | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | | | | | | | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 52 | 13686 (1)| 00:02:45 | | | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 1 | 34 | 13686 (1)| 00:02:45 | 171 | 171 | Q1,00 | PCWC | |
|* 16 | TABLE ACCESS FULL | BL1_CYC_PAYER_POP | 1 | 34 | 13686 (1)| 00:02:45 | 171 | 171 | Q1,00 | PCWP | |
| 17 | PARTITION RANGE SINGLE | | 7 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
|* 18 | INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 7 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_DOCUMENT | 1 | 18 | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
| 20 | PARTITION RANGE SINGLE | | 1 | 15 | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 21 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_CYCLE_CUSTOMERS | 1 | 15 | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 22 | INDEX RANGE SCAN | BL1_CYCLE_CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 23 | INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 24 | TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
16 - filter("PAYER"."FORMAT_EXT_DATE" IS NULL AND "PAYER"."CYCLE_SEQ_NO"=4105 AND "PAYER"."PERIOD_KEY"=61 AND ("PAYER"."UNDO_REQ_TYPE"='N' OR
"PAYER"."UNDO_REQ_TYPE" IS NULL) AND "PAYER"."DB_STATUS"='BL' AND "PAYER"."CYCLE_SEQ_RUN"=0)
18 - access("PAYER"."BA_NO"="DOC"."BA_NO")
19 - filter("DOC"."CYCLE_SEQ_NO"=4105 AND "DOC"."PERIOD_KEY"=61 AND "DOC"."CYCLE_SEQ_RUN"=0 AND ("DOC"."DOC_PRODUCE_IND"='E' OR
"DOC"."DOC_PRODUCE_IND"='Y'))
21 - filter("CYC_CUST"."UNDO_REQ_TYPE"='N' OR "CYC_CUST"."UNDO_REQ_TYPE" IS NULL)
22 - access("ITEM_0"="CYC_CUST"."CUSTOMER_NO" AND "CYC_CUST"."CYCLE_SEQ_NO"=4105 AND "CYC_CUST"."PERIOD_KEY"=61)
23 - access("CYC_CUST"."CUSTOMER_NO"="CUST"."CUSTOMER_ID")
最後得到的反饋是，原本執行近20個小時的查詢，在新增這個Hint之後，執行時間縮短到了1個小時以內。效能的提升還是相當的可觀的。
| Session | APPC (20015:7013) |
| SQL ID | 74pzzzjddkyd4 |
| SQL Execution ID | 16777242 |
| Execution Started | 2/2/2015 10:52 |
| First Refresh Time | 2/2/2015 10:52 |
| Last Refresh Time | 2/3/2015 0:05 |
| Duration | 47669s |
| Module/Action | bfi@ccbdbpr1 (TNS V1-V3)/- |
| Service | XXXXX |
| Program | bfi@xxx (TNS V1-V3) |
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
WHERE CYC_CUST.PERIOD_KEY = :periodKey
AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
AND EXISTS
(SELECT 1
FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
WHERE PAYER.PERIOD_KEY = :periodKey
AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
AND PAYER.DB_STATUS = 'BL'
AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
AND PAYER.FORMAT_EXT_DATE IS NULL
AND DOC.PERIOD_KEY = :periodKey
AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.BA_NO = DOC.BA_NO
AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))
檢視執行計劃沒有發現很嚴重的資源消耗。但是實際的執行情況怎麼和執行計劃相差甚遠。預計8分鐘，實際上十多個小時還沒有執行完。
Plan hash value: 3506320481
--------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 24 | 42048 (1)| 00:08:25 | | |
| 1 | SORT AGGREGATE | | 1 | 24 | | | | |
|* 2 | FILTER | | | | | | | |
| 3 | NESTED LOOPS | | | | | | | |
| 4 | NESTED LOOPS | | 1 | 24 | 42046 (1)| 00:08:25 | | |
| 5 | PARTITION RANGE SINGLE | | 1 | 15 | 42045 (1)| 00:08:25 | 171 | 171 |
|* 6 | TABLE ACCESS FULL | BL1_CYCLE_CUSTOMERS | 1 | 15 | 42045 (1)| 00:08:25 | 171 | 171 |
|* 7 | INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | |
| 8 | TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | |
| 9 | NESTED LOOPS | | | | | | | |
| 10 | NESTED LOOPS | | 1 | 52 | 2 (0)| 00:00:01 | | |
| 11 | PARTITION RANGE SINGLE | | 1 | 34 | 1 (0)| 00:00:01 | 171 | 171 |
|* 12 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_CYC_PAYER_POP | 1 | 34 | 1 (0)| 00:00:01 | 171 | 171 |
|* 13 | INDEX RANGE SCAN | BL1_CYC_PAYER_POP_1IX | 3 | | 1 (0)| 00:00:01 | 171 | 171 |
| 14 | PARTITION RANGE SINGLE | | 7 | | 1 (0)| 00:00:01 | 171 | 171 |
|* 15 | INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 7 | | 1 (0)| 00:00:01 | 171 | 171 |
|* 16 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_DOCUMENT | 1 | 18 | 1 (0)| 00:00:01 | 171 | 171 |
--------------------------------------------------------------------------------------------------------------------------------
| Buffer Gets | IO Requests | Database Time | Wait Activity | ||||
|
.
|
96M |
.
|
21M |
.
|
48518s |
.
|
100% |
一看IO請求達21M次，約等於160.9G左右的資料量。
從sql語句的執行計劃可以看出，語句可以分為兩大部分，一部分是exist字句上面的部分，兩個大表做了關聯，得到了相關的customer_no然後在exists字句中繼續關聯。
大量的IO請求都消耗在BL1_CUSTOMER,其實這個表實際上資料量近千萬，還沒有80G多G，但是傳送的IO請求累計的資料量卻已經超過了80G，佔到了整個IO請求數的一半以上。消耗的CPU資源也在73%以上
可以通過禁用子查詢解巢狀來做為一種調優思路，優先從子查詢中先輸出資料來。
而BL1_CYCLE_PAYER_POP表作為一個重要的關聯表。子查詢中的條件AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO和外部查詢相關聯。
可以優先查詢這個表，考慮到執行的頻率和效能，新增了並行hint。
這樣sql語句就變為
SELECT NVL(SUM(CUST.WEIGHT), 0) TOTAL_WEIGHT
FROM BL1_CUSTOMER CUST, BL1_CYCLE_CUSTOMERS CYC_CUST
WHERE CYC_CUST.PERIOD_KEY = :periodKey
AND CYC_CUST.CYCLE_SEQ_NO = :cycleSeqNo
AND CYC_CUST.CUSTOMER_NO = CUST.CUSTOMER_ID
AND (CYC_CUST.UNDO_REQ_TYPE = 'N' OR CYC_CUST.UNDO_REQ_TYPE IS NULL)
AND EXISTS
(SELECT /*+unnest full(payer) parallel(payer 4)*/1
FROM BL1_CYC_PAYER_POP PAYER, BL1_DOCUMENT DOC
WHERE PAYER.PERIOD_KEY = :periodKey
AND PAYER.CYCLE_SEQ_NO = :cycleSeqNo
AND PAYER.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.CUSTOMER_NO = CYC_CUST.CUSTOMER_NO
AND PAYER.DB_STATUS = 'BL'
AND (PAYER.UNDO_REQ_TYPE = 'N' OR PAYER.UNDO_REQ_TYPE IS NULL)
AND PAYER.FORMAT_EXT_DATE IS NULL
AND DOC.PERIOD_KEY = :periodKey
AND DOC.CYCLE_SEQ_NO = :cycleSeqNo
AND DOC.CYCLE_SEQ_RUN = :cycleSeqRun
AND PAYER.BA_NO = DOC.BA_NO
AND doc.DOC_PRODUCE_IND IN ('Y', 'E'))
優化後的執行計劃如下：
Plan hash value: 227985194
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 13688 (1)| 00:02:45 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 37 | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 37 | | | | | Q1,01 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 37 | | | | | Q1,01 | PCWP | |
| 5 | NESTED LOOPS | | | | | | | | Q1,01 | PCWP | |
| 6 | NESTED LOOPS | | 1 | 37 | 13688 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 7 | NESTED LOOPS | | 1 | 28 | 13688 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 8 | VIEW | VW_SQ_1 | 1 | 13 | 13686 (1)| 00:02:45 | | | Q1,01 | PCWP | |
| 9 | HASH UNIQUE | | 1 | 52 | | | | | Q1,01 | PCWP | |
| 10 | PX RECEIVE | | 1 | 52 | | | | | Q1,01 | PCWP | |
| 11 | PX SEND HASH | :TQ10000 | 1 | 52 | | | | | Q1,00 | P->P | HASH |
| 12 | HASH UNIQUE | | 1 | 52 | | | | | Q1,00 | PCWP | |
| 13 | NESTED LOOPS | | | | | | | | Q1,00 | PCWP | |
| 14 | NESTED LOOPS | | 1 | 52 | 13686 (1)| 00:02:45 | | | Q1,00 | PCWP | |
| 15 | PX BLOCK ITERATOR | | 1 | 34 | 13686 (1)| 00:02:45 | 171 | 171 | Q1,00 | PCWC | |
|* 16 | TABLE ACCESS FULL | BL1_CYC_PAYER_POP | 1 | 34 | 13686 (1)| 00:02:45 | 171 | 171 | Q1,00 | PCWP | |
| 17 | PARTITION RANGE SINGLE | | 7 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
|* 18 | INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 7 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
|* 19 | TABLE ACCESS BY LOCAL INDEX ROWID| BL1_DOCUMENT | 1 | 18 | 1 (0)| 00:00:01 | 171 | 171 | Q1,00 | PCWP | |
| 20 | PARTITION RANGE SINGLE | | 1 | 15 | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 21 | TABLE ACCESS BY LOCAL INDEX ROWID | BL1_CYCLE_CUSTOMERS | 1 | 15 | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 22 | INDEX RANGE SCAN | BL1_CYCLE_CUSTOMERS_PK | 1 | | 1 (0)| 00:00:01 | 171 | 171 | Q1,01 | PCWP | |
|* 23 | INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
| 24 | TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 9 | 1 (0)| 00:00:01 | | | Q1,01 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
16 - filter("PAYER"."FORMAT_EXT_DATE" IS NULL AND "PAYER"."CYCLE_SEQ_NO"=4105 AND "PAYER"."PERIOD_KEY"=61 AND ("PAYER"."UNDO_REQ_TYPE"='N' OR
"PAYER"."UNDO_REQ_TYPE" IS NULL) AND "PAYER"."DB_STATUS"='BL' AND "PAYER"."CYCLE_SEQ_RUN"=0)
18 - access("PAYER"."BA_NO"="DOC"."BA_NO")
19 - filter("DOC"."CYCLE_SEQ_NO"=4105 AND "DOC"."PERIOD_KEY"=61 AND "DOC"."CYCLE_SEQ_RUN"=0 AND ("DOC"."DOC_PRODUCE_IND"='E' OR
"DOC"."DOC_PRODUCE_IND"='Y'))
21 - filter("CYC_CUST"."UNDO_REQ_TYPE"='N' OR "CYC_CUST"."UNDO_REQ_TYPE" IS NULL)
22 - access("ITEM_0"="CYC_CUST"."CUSTOMER_NO" AND "CYC_CUST"."CYCLE_SEQ_NO"=4105 AND "CYC_CUST"."PERIOD_KEY"=61)
23 - access("CYC_CUST"."CUSTOMER_NO"="CUST"."CUSTOMER_ID")
最後得到的反饋是，原本執行近20個小時的查詢，在新增這個Hint之後，執行時間縮短到了1個小時以內。效能的提升還是相當的可觀的。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/23718752/viewspace-1429117/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 透過使用hint unnest調優sql語句SQL
- 使用hint來調優sql語句SQL
- 使用USE_HASH Hint調優一個SQL語句SQL
- oracle 通過sql profile為sql語句加hintOracleSQL
- ORACLE 通過SPM為SQL語句加HINTOracleSQL
- 通過SQL PROFILE自動優化SQL語句SQL優化
- 通過分析SQL語句的執行計劃優化SQL語句SQL優化
- 通過hint push_subq優化sql優化SQL
- 【sql調優之執行計劃】使用hint(五)Hint for parallelSQLParallel
- SQL語句優化方法用hint的30種方法SQL優化
- 通過sql語句分析足彩SQL
- 使用SQL調整顧問進行語句優化SQL優化
- 通過分析SQL語句的執行計劃優化SQL 二SQL優化
- SQL Server調優系列玩轉篇三（利用索引提示（Hint）引導語句最大優化執行）SQLServer索引優化
- SQL Server調優系列玩轉篇（如何利用查詢提示（Hint）引導語句執行）SQLServer
- 通過分析SQL語句的執行計劃優化SQL(總結)SQL優化
- ORACLE常用SQL最佳化hint語句OracleSQL
- sql調優一例---索引排序hintSQL索引排序
- 通過ADDM進行SQL調優SQL
- 通過_optimizer_rownum_pred_based_fkr優化一條sql語句優化SQL
- 【sql調優之執行計劃】使用hint(四)Hints for JoinSQL
- 通過java來格式化sql語句JavaSQL
- 一條sql語句的建議調優分析SQL
- ORACLE sql 語句的執行過程(SQL效能調整)OracleSQL
- 一條SQL語句的優化過程SQL優化
- 【sql調優之執行計劃】使用hint(三)Hints for Query TransformationsSQLORM
- 【sql調優之執行計劃】使用hint(二)Hints for Access PathsSQL
- SQL語句優化SQL優化
- 通過ORA錯誤反思sql語句規範SQL
- 通過sql語句分析足彩(第三篇）SQL
- 【sql調優之執行計劃】使用hint(六) append and noappendSQLAPP
- 通過SQL語句提取儲存過程中的內容SQL儲存過程
- MYSQL SQL語句優化MySql優化
- sql語句效能優化SQL優化
- SQL語句的優化SQL優化
- 求助：SQL語句優化SQL優化
- MySQL_通過binlog檢視原始SQL語句MySql
- 通過java程式抽取日誌中的sql語句JavaSQL