透過使用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%以上
| Id | Operation | Name | Estimated | Cost | Execs | Rows | IO Requests | CPU Activity | |||
| Rows | |||||||||||
| . | 0 | SELECT STATEMENT | . | . | . | 1 | . | . | |||
| -> | 1 | . SORT AGGREGATE | . | 1 | . | 1 | 0 | . | |||
| -> | 2 | .. FILTER | . | . | . | 1 | 403K | . | |||
| -> | 3 | ... NESTED LOOPS | . | . | . | 1 | 562K | . | |||
| -> | 4 | .... NESTED LOOPS | . | 1682 | 45038 | 1 | 562K | . | |||
| -> | 5 | ..... PARTITION RANGE ITERATOR | . | 1682 | 44869 | 1 | 562K | . | |||
| . | 6 | ...... TABLE ACCESS FULL | BL1_CYCLE_CUSTOMERS | 1682 | 44869 | 1 | 562K | 7736 (<0.1%) | |||
| . | 7 | ..... INDEX UNIQUE SCAN | BL1_CUSTOMER_PK | 1 | 1 | 949K | 562K |
.
|
673K (3.3%) | ### | |
| -> | 8 | .... TABLE ACCESS BY INDEX ROWID | BL1_CUSTOMER | 1 | 1 | 990K | 562K |
.
|
11M (52%) |
.
|
73% |
| -> | 9 | ... NESTED LOOPS | . | . | . | 562K | 403K | . | |||
| -> | 10 | .... NESTED LOOPS | . | 1 | 74 | 562K | 6M | . | |||
| -> | 11 | ..... PARTITION RANGE ITERATOR | . | 1 | 37 | 562K | 497K | . | |||
| . | 12 | ...... TABLE ACCESS BY LOCAL INDEX ROWID | BL1_CYC_PAYER_POP | 1 | 37 | 562K | 497K |
.
|
774K (3.8%) |
.
|
0.99% |
| . | 13 | ....... INDEX RANGE SCAN | BL1_CYC_PAYER_POP_1IX | 3 | 36 | 562K | 4M |
.
|
864K (4.2%) |
.
|
1.90% |
| -> | 14 | ..... PARTITION RANGE ITERATOR | . | 14 | 36 | 497K | 6M | . | |||
| . | 15 | ...... INDEX RANGE SCAN | BL1_DOCUMENT_1IX | 14 | 36 | 497K | 6M |
.
|
4M (20%) |
.
|
21% |
| . | 16 | .... TABLE ACCESS BY LOCAL INDEX ROWID | BL1_DOCUMENT | 1 | 37 | 6M | 403K |
.
|
3M (15%) |
.
|
1.20% |
可以透過禁用子查詢解巢狀來做為一種調優思路，優先從子查詢中先輸出資料來。
而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/8494287/viewspace-1440457/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- SQL語句優化SQL優化
- sql語句效能優化SQL優化
- MYSQL SQL語句優化MySql優化
- soar-PHP - SQL 語句優化器和重寫器的 PHP 擴充套件包、 方便框架中 SQL 語句調優PHPSQL優化套件框架
- MySQL之SQL語句優化MySql優化
- [20201210]sql語句優化.txtSQL優化
- [20220331]如何調整sql語句.txtSQL
- EF中使用SQL語句或儲存過程SQL儲存過程
- OceanBase學習之路36|如何透過 SQL 語句刪除不再使用的資源池？SQL
- 優化 SQL 語句的步驟優化SQL
- mysql執行sql語句過程MySql
- 使用sql語句查詢平均值,使用sql語句查詢資料總條數, not in 篩選語句的使用SQL
- [20200320]SQL語句優化的困惑.txtSQL優化
- GreatSQL 在SQL中使用 HINT 語法修改會話變數SQL會話變數
- Java中如何解析SQL語句、格式化SQL語句、生成SQL語句？JavaSQL
- SQL語句SQL
- 【nacos】透過curl語句更新配置檔案
- Fastapi sqlalchemy DBApi 直接使用sql語句ASTAPISQL
- jsqlparser使用記錄---生成sql語句JSSQL
- OceanBase學習之路46|如何透過 SQL 語句或 OCP 建立資源單元？SQL
- [20181114]一條sql語句的優化.txtSQL優化
- MySQL 52個SQL效能優化策略SQL語句彙總MySql優化
- 【SQL】Oracle SQL join on語句and和where使用區別SQLOracle
- 一條sql語句的執行過程SQL
- 【SQL】9 SQL INSERT INTO 語句SQL
- 【SQL】10 SQL UPDATE 語句SQL
- 【SQL】11 SQL DELETE 語句SQLdelete
- sql常用語句SQL
- SQL SELECT 語句SQL
- SQL語句優化的原則與方法QOSQL優化
- [20200324]SQL語句優化的困惑2.txtSQL優化
- Sql語句本身的優化-定位慢查詢SQL優化
- 記一次SQL調優過程SQL
- 4.3.2 關於使用SQL語句建立CDBSQL
- 資料庫查詢優化：使用explain分析sql語句執行效率資料庫優化AISQL
- [20191101]通過zsh計算sql語句的sql_id.txtSQL
- [20191011]通過bash計算sql語句的sql_id.txtSQL
- 《MySQL慢查詢優化》之SQL語句及索引優化MySql優化索引
- SQL優化案例-單表分頁語句的優化（八）SQL優化