關於索引掃描的極速調優實戰（第一篇)
一般在生產環境中，如果某個查詢中涉及一個大表，走索引掃描是顯然是最值得推薦的方式，但是索引掃描有unique index scan, range scan,skip scan, full scan, fast full scan,這些索引掃描看起來好像很繁雜，但是如果掌握得當，卻能夠在索引掃描的基礎上極速提升效能。關於索引掃描的方式，可以參考。http://blog.itpub.net/23718752/viewspace-1335358/ 關於索引的使用模式
首先來看看這個問題。
開發反應這周有一個process處理資料特別慢，有很多的業務處理都受到了影響，想讓我看看在資料庫級別能夠發現什麼。
從這個反饋來說，可能資料庫中是有鎖了，或者是存在著一些異常的程式消耗了較多的資源，要不就是sql語句的問題。因為這個庫已經執行很長時間了。涉及到的開發變更還是比較少的。所以就先檢視了資料庫的負載。
BEGIN_TIME------------------------- END_TIME--------------------------- ELAPSED_TIME- BTIME----- WORKLOAD_PER--------
----------------------------------- ----------------------------------- ------------- ---------- --------------------
12360 ** 11-DEC-14 01.00.06.432 AM 12361 ** 11-DEC-14 02.00.08.531 AM 60.035 103.07 171%
12361 ** 11-DEC-14 02.00.08.531 AM 12362 ** 11-DEC-14 03.00.11.099 AM 60.043 105.13 175%
12362 ** 11-DEC-14 03.00.11.099 AM 12363 ** 11-DEC-14 04.00.13.507 AM 60.040 148.71 247%
12363 ** 11-DEC-14 04.00.13.507 AM 12364 ** 11-DEC-14 05.00.17.328 AM 60.064 169.35 281%
12364 ** 11-DEC-14 05.00.17.328 AM 12365 ** 11-DEC-14 06.00.20.742 AM 60.057 89.84 149%
12365 ** 11-DEC-14 06.00.20.742 AM 12366 ** 11-DEC-14 07.00.23.766 AM 60.050 89.49 149%
12366 ** 11-DEC-14 07.00.23.766 AM 12367 ** 11-DEC-14 08.00.25.956 AM 60.037 113.92 189%
12367 ** 11-DEC-14 08.00.25.956 AM 12368 ** 11-DEC-14 09.00.28.480 AM 60.042 92.33 153%
12368 ** 11-DEC-14 09.00.28.480 AM 12369 ** 11-DEC-14 10.00.31.163 AM 60.045 180.66 300%
12369 ** 11-DEC-14 10.00.31.163 AM 12370 ** 11-DEC-14 11.00.34.040 AM 60.048 204.65 340%
12370 ** 11-DEC-14 11.00.34.040 AM 12371 ** 11-DEC-14 12.00.37.255 PM 60.054 361.93 602%
12371 ** 11-DEC-14 12.00.37.255 PM 12372 ** 11-DEC-14 01.00.40.741 PM 60.058 400.98 667%
12372 ** 11-DEC-14 01.00.40.741 PM 12373 ** 11-DEC-14 02.00.43.680 PM 60.049 225.84 376%
12373 ** 11-DEC-14 02.00.43.680 PM 12374 ** 11-DEC-14 03.00.46.353 PM 60.045 220.51 367%
12374 ** 11-DEC-14 03.00.46.353 PM 12375 ** 11-DEC-14 04.00.48.809 PM 60.041 276.56 460%
12375 ** 11-DEC-14 04.00.48.809 PM 12376 ** 11-DEC-14 05.00.51.411 PM 60.043 204.22 340%
從整體來看，負載還是可以接受的。
然後檢視鎖的情況，也沒有發現什麼延遲的鎖等待。這個時候鎖等待導致的延遲可能也排除了。
這個時候抓一個awr報告看看細節。
從load profile來看，cpu使用率不高。相對來說,logical reads較高。
等待事件的情況如下。
直接進入sql語句的部分。
這個時候可以很明顯的看到sql語句佔用了83%的比例。可以看到每條語句的執行時間在0.52秒左右。看起來還是可以的，但是從報表中來看，這條語句的執行頻率很高。
對應的sql語句如下：
SELECT LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) --這個語句的關鍵就在於標黃的部分,這條語句是想得到cycle_year,cycle_month最高的值，把year,month拼接成20141209這樣的形式
FROM CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a)
對應的索引如下：
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- --------------------- ---------- ------ ---------- --------- --------------------- ---------- ------ ---------- --------- --------------------- --------
CRDT_LMT_NOTIFICATION_PK NORMAL UNIQUE YES CYCLE_CODE,CYCLE_MONTH,CYCLE_YEAR,CUSTOMER_ID,AGREEMENT_ID,OFFER_INSTANCE,ITEM_ID TABLE N/A 5457339 03-DEC-14 N
檢視對應的執行計劃。
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 4281 (1)| 00:00:52 | | |
| 1 | PARTITION RANGE ITERATOR | | 2 | 74 | 2141 (1)| 00:00:26 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 2 | 74 | 2141 (1)| 00:00:26 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 2141 (1)| 00:00:26 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 7 | 238 | 2141 (1)| 00:00:26 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 7 | 238 | 2141 (1)| 00:00:26 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT /*+ PUSH_SUBQ OPT_ESTIMATE (TABLE
"CRDT_LMT_NOTIFICATION" SCALE_ROWS=1016.803110 ) OPT_ESTIMATE (INDEX_FILTER "CRDT_LMT_NOTIFICATION"
"CRDT_LMT_NOTIFICATION_PK" SCALE_ROWS=440.696164 ) INDEX ("CRDT_LMT_NOTIFICATION" "CRDT_LMT_NOTIFICATION_PK")
*/ MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "CRDT_LMT_NOTIFICATION"
"CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=:A AND "OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND
"ITEM_ID"=:A))
6 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A)
我們使用sql_profile來看看調優的建議。這裡的不同之處是原本的range scan變成了skip scan. 資源消耗一下子小了幾十倍。
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 6 (67)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 2 | 74 | 3 (67)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION_PK | 2 | 74 | 3 (67)| 00:00:01 | KEY | KEY |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK_PK | 1 | | 3 (67)| 00:00:01 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 7 | 238 | 3 (67)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK_PK | 7 | 238 | 3 (67)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT /*+ OPT_ESTIMATE (TABLE "CRDT_LMT_NOTIFICATION_PK"
SCALE_ROWS=1016.803110 ) OPT_ESTIMATE (INDEX_FILTER "CRDT_LMT_NOTIFICATION_PK" "CRDT_LMT_NOTIFICATION_PK_PK"
SCALE_ROWS=440.696164 ) */ MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM
"PRDUSG3O"."CRDT_LMT_NOTIFICATION_PK" "CRDT_LMT_NOTIFICATION_PK" WHERE "CYCLE_CODE"=:A AND "OFFER_INSTANCE"=:A AND
"AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A))
6 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A)
在和開發確認之後，這條語句是關鍵的語句，是在一個新開發的需求中新加的。因為情況緊急，壓力一下子堆在了我身上，大家希望我來對這條語句進行調優，能從0.5秒進行更高效的調優。
今天章節開篇先來介紹一下問題的情況，明天來詳細的分析一下處理的思路，各種方案的對比和最終的建議。
首先來看看這個問題。
開發反應這周有一個process處理資料特別慢，有很多的業務處理都受到了影響，想讓我看看在資料庫級別能夠發現什麼。
從這個反饋來說，可能資料庫中是有鎖了，或者是存在著一些異常的程式消耗了較多的資源，要不就是sql語句的問題。因為這個庫已經執行很長時間了。涉及到的開發變更還是比較少的。所以就先檢視了資料庫的負載。
BEGIN_TIME------------------------- END_TIME--------------------------- ELAPSED_TIME- BTIME----- WORKLOAD_PER--------
----------------------------------- ----------------------------------- ------------- ---------- --------------------
12360 ** 11-DEC-14 01.00.06.432 AM 12361 ** 11-DEC-14 02.00.08.531 AM 60.035 103.07 171%
12361 ** 11-DEC-14 02.00.08.531 AM 12362 ** 11-DEC-14 03.00.11.099 AM 60.043 105.13 175%
12362 ** 11-DEC-14 03.00.11.099 AM 12363 ** 11-DEC-14 04.00.13.507 AM 60.040 148.71 247%
12363 ** 11-DEC-14 04.00.13.507 AM 12364 ** 11-DEC-14 05.00.17.328 AM 60.064 169.35 281%
12364 ** 11-DEC-14 05.00.17.328 AM 12365 ** 11-DEC-14 06.00.20.742 AM 60.057 89.84 149%
12365 ** 11-DEC-14 06.00.20.742 AM 12366 ** 11-DEC-14 07.00.23.766 AM 60.050 89.49 149%
12366 ** 11-DEC-14 07.00.23.766 AM 12367 ** 11-DEC-14 08.00.25.956 AM 60.037 113.92 189%
12367 ** 11-DEC-14 08.00.25.956 AM 12368 ** 11-DEC-14 09.00.28.480 AM 60.042 92.33 153%
12368 ** 11-DEC-14 09.00.28.480 AM 12369 ** 11-DEC-14 10.00.31.163 AM 60.045 180.66 300%
12369 ** 11-DEC-14 10.00.31.163 AM 12370 ** 11-DEC-14 11.00.34.040 AM 60.048 204.65 340%
12370 ** 11-DEC-14 11.00.34.040 AM 12371 ** 11-DEC-14 12.00.37.255 PM 60.054 361.93 602%
12371 ** 11-DEC-14 12.00.37.255 PM 12372 ** 11-DEC-14 01.00.40.741 PM 60.058 400.98 667%
12372 ** 11-DEC-14 01.00.40.741 PM 12373 ** 11-DEC-14 02.00.43.680 PM 60.049 225.84 376%
12373 ** 11-DEC-14 02.00.43.680 PM 12374 ** 11-DEC-14 03.00.46.353 PM 60.045 220.51 367%
12374 ** 11-DEC-14 03.00.46.353 PM 12375 ** 11-DEC-14 04.00.48.809 PM 60.041 276.56 460%
12375 ** 11-DEC-14 04.00.48.809 PM 12376 ** 11-DEC-14 05.00.51.411 PM 60.043 204.22 340%
從整體來看，負載還是可以接受的。
然後檢視鎖的情況，也沒有發現什麼延遲的鎖等待。這個時候鎖等待導致的延遲可能也排除了。
這個時候抓一個awr報告看看細節。
| Snap Id | Snap Time | Sessions | Cursors/Session | |
|---|---|---|---|---|
| Begin Snap: | 12314 | 09-Dec-14 03:00:07 | 253 | 4.4 |
| End Snap: | 12315 | 09-Dec-14 04:00:09 | 248 | 4.5 |
| Elapsed: | 60.04 (mins) | |||
| DB Time: | 86.64 (mins) |
| Per Second | Per Transaction | Per Exec | Per Call | |
|---|---|---|---|---|
| DB Time(s): | 1.4 | 1.4 | 0.00 | 0.00 |
| DB CPU(s): | 1.4 | 1.4 | 0.00 | 0.00 |
| Redo size: | 81,566.1 | 77,546.7 | ||
| Logical reads: | 121,122.2 | 115,153.6 | ||
| Block changes: | 393.3 | 373.9 | ||
| Physical reads: | 9.7 | 9.2 | ||
| Physical writes: | 16.6 | 15.8 | ||
| User calls: | 534.7 | 508.4 | ||
| Parses: | 3.8 | 3.6 | ||
| Hard parses: | 0.1 | 0.1 | ||
| W/A MB processed: | 0.1 | 0.1 | ||
| Logons: | 0.1 | 0.1 | ||
| Executes: | 291.1 | 276.8 | ||
| Rollbacks: | 0.0 | 0.0 | ||
| Transactions: | 1.1 |
| Event | Waits | Time(s) | Avg wait (ms) | % DB time | Wait Class |
|---|---|---|---|---|---|
| DB CPU | 5,124 | 98.56 | |||
| db file sequential read | 34,433 | 65 | 2 | 1.24 | User I/O |
| log file sync | 3,515 | 16 | 5 | 0.30 | Commit |
| control file sequential read | 34,785 | 10 | 0 | 0.20 | System I/O |
| SQL*Net message to client | 1,751,834 | 1 | 0 | 0.03 | Network |
| Elapsed Time (s) | Executions | Elapsed Time per Exec (s) | %Total | %CPU | %IO | SQL Id | SQL Module | SQL Text |
|---|---|---|---|---|---|---|---|---|
| 4,352.98 | 8,375 | 0.52 | 83.73 | 99.95 | 0.01 | m1EnvelopeMT@ccbdbpr5 (TNS V1-V3) | /* */ SELECT LAST_THRESHOLD, C... | |
| 250.14 | 4,942 | 0.05 | 4.81 | 99.94 | 0.00 | m1EnvelopeMT@ccbdbpr5 (TNS V1-V3) | /* */ select count (1) from RP... | |
| 158.68 | 4,810 | 0.03 | 3.05 | 99.89 | 0.00 | m1EnvelopeMT@ccbdbpr5 (TNS V1-V3) | /* */ SELECT IDENTIFIER FROM (... |
對應的sql語句如下：
SELECT LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) --這個語句的關鍵就在於標黃的部分,這條語句是想得到cycle_year,cycle_month最高的值，把year,month拼接成20141209這樣的形式
FROM CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a)
對應的索引如下：
INDEX_NAME TABLESPACE INDEX_TYPE UNIQUENES PAR COLUMN_LIST TABLE_TYPE STATUS NUM_ROWS LAST_ANAL G
------------------------------ ---------- ---------- --------- --- ------------------------------ ---------- ------ ---------- --------- --------------------- ---------- ------ ---------- --------- --------------------- ---------- ------ ---------- --------- --------------------- --------
CRDT_LMT_NOTIFICATION_PK NORMAL UNIQUE YES CYCLE_CODE,CYCLE_MONTH,CYCLE_YEAR,CUSTOMER_ID,AGREEMENT_ID,OFFER_INSTANCE,ITEM_ID TABLE N/A 5457339 03-DEC-14 N
檢視對應的執行計劃。
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 4281 (1)| 00:00:52 | | |
| 1 | PARTITION RANGE ITERATOR | | 2 | 74 | 2141 (1)| 00:00:26 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 2 | 74 | 2141 (1)| 00:00:26 | KEY | KEY |
|* 3 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 2141 (1)| 00:00:26 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 7 | 238 | 2141 (1)| 00:00:26 | KEY | KEY |
|* 6 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 7 | 238 | 2141 (1)| 00:00:26 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT /*+ PUSH_SUBQ OPT_ESTIMATE (TABLE
"CRDT_LMT_NOTIFICATION" SCALE_ROWS=1016.803110 ) OPT_ESTIMATE (INDEX_FILTER "CRDT_LMT_NOTIFICATION"
"CRDT_LMT_NOTIFICATION_PK" SCALE_ROWS=440.696164 ) INDEX ("CRDT_LMT_NOTIFICATION" "CRDT_LMT_NOTIFICATION_PK")
*/ MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "CRDT_LMT_NOTIFICATION"
"CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=:A AND "OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND
"ITEM_ID"=:A))
6 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A)
我們使用sql_profile來看看調優的建議。這裡的不同之處是原本的range scan變成了skip scan. 資源消耗一下子小了幾十倍。
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 74 | 6 (67)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 2 | 74 | 3 (67)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION_PK | 2 | 74 | 3 (67)| 00:00:01 | KEY | KEY |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK_PK | 1 | | 3 (67)| 00:00:01 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 7 | 238 | 3 (67)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK_PK | 7 | 238 | 3 (67)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT /*+ OPT_ESTIMATE (TABLE "CRDT_LMT_NOTIFICATION_PK"
SCALE_ROWS=1016.803110 ) OPT_ESTIMATE (INDEX_FILTER "CRDT_LMT_NOTIFICATION_PK" "CRDT_LMT_NOTIFICATION_PK_PK"
SCALE_ROWS=440.696164 ) */ MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM
"PRDUSG3O"."CRDT_LMT_NOTIFICATION_PK" "CRDT_LMT_NOTIFICATION_PK" WHERE "CYCLE_CODE"=:A AND "OFFER_INSTANCE"=:A AND
"AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A))
6 - access("CYCLE_CODE"=:A AND "CUSTOMER_ID"=:A AND "AGREEMENT_ID"=:A AND "OFFER_INSTANCE"=:A AND "ITEM_ID"=:A)
filter("OFFER_INSTANCE"=:A AND "AGREEMENT_ID"=:A AND "CUSTOMER_ID"=:A AND "ITEM_ID"=:A)
在和開發確認之後，這條語句是關鍵的語句，是在一個新開發的需求中新加的。因為情況緊急，壓力一下子堆在了我身上，大家希望我來對這條語句進行調優，能從0.5秒進行更高效的調優。
今天章節開篇先來介紹一下問題的情況，明天來詳細的分析一下處理的思路，各種方案的對比和最終的建議。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25462274/viewspace-1961951/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於索引掃描的極速調優實戰（第二篇)索引
- 關於按鍵掃描程式的終極討論
- mysql索引覆蓋掃描優化MySql索引優化
- 索引全掃描和索引快速全掃描的區別索引
- Oracle中存取資料掃描Table及索引的方式(全表掃描，索引掃描等)Oracle索引
- Oracle優化－索引原理[注意索引跳躍式掃描!Oracle優化索引
- 【Oracle】 索引的掃描方式Oracle索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 【MySQL】全索引掃描的bugMySql索引
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- 解讀Oracle 索引掃描Oracle索引
- 走索引掃描的慢查詢索引
- 有索引卻走全表掃描的實驗分析索引
- SQL Server調優實戰 亂建聚集索引的後果SQLServer索引
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- (轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- 轉)索引掃描還是全表掃描（Index Scan Or Full Table Scan）索引Index
- 使用索引掃描來進行排序索引排序
- 關於Oracle 9i 跳躍式索引掃描(Index Skip Scan)的小測試 (轉)Oracle索引Index
- 優化全表掃描優化
- 持續近7個小時的索引掃描的查詢優化分析索引優化
- 關於分割槽表中的全partition掃描問題
- PostgreSQL技術內幕（七）索引掃描SQL索引
- stopkey對索引掃描的影響測試TopK索引
- MySQL——索引優化實戰MySql索引優化
- 實戰 nginx 調優Nginx
- 效能調優實戰
- Hive調優實戰Hive
- 全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）索引直方圖
- MySQL調優篇 | SQL調優實戰（5）MySql
- 京東掃描平臺EOS—JS掃描落地與實踐JS
- 索引唯一性掃描(INDEX UNIQUE SCAN)索引Index
- mysql下建立索引讓其index全掃描MySql索引Index
- 生產環境大型sql語句調優實戰第一篇（一）SQL
- 生產環境大型sql語句調優實戰第一篇（二）SQL
- win10系統mcafee實時掃描怎麼關閉_win10系統mcafee實時掃描如何關閉Win10
- 關於Hadoop調優Hadoop
- oracle優化：避免全表掃描Oracle優化