關於索引掃描的極速調優實戰（第二篇)
在上一篇http://blog.itpub.net/23718752/viewspace-1364914/ 中我們大體介紹了下問題的情況，已經初步根據awr能夠抓取到存在問題的sql語句。
這條sql語句執行很頻繁，目前平均執行時間在0.5秒。開發部門希望我們能不能做點最佳化，他們也在同時想辦法從業務上來最佳化這個問題。從0.5秒的情況下，能夠再提高很多，是得費很大力氣的。
況且這個問題比較緊急，從拿到sql語句開始，就感覺到一種壓力。
最開始的注意力都集中在cycle_month和cycle_year的處理上。
對於下面的部分，是這條sql語句的關鍵，cycle_year,cycle_month是在索引列中，但是根據業務邏輯，需要把cycle_year,cycle_month拼成一個數字，然後計算cycle_year+cycle_month最大的值。
目前的實現是把cycle_year準換成為字元型，然後使用這個字串在子查詢中匹配。這樣的話，cycle_year,cycle_month作為索引列就不能直接使用索引了，還得依靠第一個索引列cycle_code.
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
FROM CRDT_LMT_NOTIFICATION
自己採用瞭如下的方式來改進，但是檢視收效甚微，基本沒有變化。
AND (CYCLE_YEAR,CYCLE_MONTH) =
(SELECT substr(MAX(cycle_year*100+cycle_month),0,4),substr(MAX(cycle_year*100+cycle_month),5,6)
所以看來需要索引掃描上多下點功夫。
根據sqlprofile中的提示，使用index skip scan效率最高。
但是使用index_ss卻始終都是走index range scan.
SELECT /*+index_ss(CRDT_LMT_NOTIFICATION CRDT_LMT_NOTIFICATION_PK)*/LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM CRDT_LMT_NOTIFICATION
WHERE CYCLE_CODE = 25
AND ITEM_ID = 15131
AND AGREEMENT_ID = 15997361
AND OFFER_INSTANCE = 223499890
AND CUSTOMER_ID = 10349451
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE CYCLE_CODE = 25
AND ITEM_ID = 15131
AND AGREEMENT_ID = 15997361
AND OFFER_INSTANCE = 223499890
AND CUSTOMER_ID = 10349451)
SQL> @plan
Plan hash value: 2310822947
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 4281 (1)| 00:00:52 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 2141 (1)| 00:00:26 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 2141 (1)| 00:00:26 | 13 | 25 |
|* 3 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 2140 (1)| 00:00:26 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 2140 (1)| 00:00:26 | 13 | 25 |
|* 6 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 2140 (1)| 00:00:26 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM " CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
最後發現主要的原因是因為隱含引數_optimizer_skip_scan_enabled 值為"false"導致的。
SQL> show parameter skip_scan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_skip_scan_enabled boolean FALSE
對於這個引數，我使用alter sessison在session級做了改動。
alter session set "_optimizer_skip_scan_enabled"=true;
然後檢視執行計劃。效率極大的提高了。
SQL> @plan
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (34)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 2 (50)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 2 (50)| 00:00:01 | KEY | KEY |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 1 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
"OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A) AND TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A)))
6 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
"OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
為什麼skip scan效率這麼高，但是使用隱含引數禁用了它呢。
產品部門的解釋是對於skip scan在大多數的場景中，效率不是很理想，基本跟index full scan的效果一樣，所以從最佳化器內部使用隱含引數禁用，就使用了range scan.
這條sql語句執行很頻繁，目前平均執行時間在0.5秒。開發部門希望我們能不能做點最佳化，他們也在同時想辦法從業務上來最佳化這個問題。從0.5秒的情況下，能夠再提高很多，是得費很大力氣的。
況且這個問題比較緊急，從拿到sql語句開始，就感覺到一種壓力。
最開始的注意力都集中在cycle_month和cycle_year的處理上。
對於下面的部分，是這條sql語句的關鍵，cycle_year,cycle_month是在索引列中，但是根據業務邏輯，需要把cycle_year,cycle_month拼成一個數字，然後計算cycle_year+cycle_month最大的值。
目前的實現是把cycle_year準換成為字元型，然後使用這個字串在子查詢中匹配。這樣的話，cycle_year,cycle_month作為索引列就不能直接使用索引了，還得依靠第一個索引列cycle_code.
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
FROM CRDT_LMT_NOTIFICATION
自己採用瞭如下的方式來改進，但是檢視收效甚微，基本沒有變化。
AND (CYCLE_YEAR,CYCLE_MONTH) =
(SELECT substr(MAX(cycle_year*100+cycle_month),0,4),substr(MAX(cycle_year*100+cycle_month),5,6)
所以看來需要索引掃描上多下點功夫。
根據sqlprofile中的提示，使用index skip scan效率最高。
但是使用index_ss卻始終都是走index range scan.
SELECT /*+index_ss(CRDT_LMT_NOTIFICATION CRDT_LMT_NOTIFICATION_PK)*/LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM CRDT_LMT_NOTIFICATION
WHERE CYCLE_CODE = 25
AND ITEM_ID = 15131
AND AGREEMENT_ID = 15997361
AND OFFER_INSTANCE = 223499890
AND CUSTOMER_ID = 10349451
AND (TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09')) =
(SELECT MAX(TO_CHAR(CYCLE_YEAR, '9999') || TO_CHAR(CYCLE_MONTH, '09'))
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE CYCLE_CODE = 25
AND ITEM_ID = 15131
AND AGREEMENT_ID = 15997361
AND OFFER_INSTANCE = 223499890
AND CUSTOMER_ID = 10349451)
SQL> @plan
Plan hash value: 2310822947
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 4281 (1)| 00:00:52 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 2141 (1)| 00:00:26 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 2141 (1)| 00:00:26 | 13 | 25 |
|* 3 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 2140 (1)| 00:00:26 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 2140 (1)| 00:00:26 | 13 | 25 |
|* 6 | INDEX RANGE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 2140 (1)| 00:00:26 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM " CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
SQL> show parameter skip_scan
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
_optimizer_skip_scan_enabled boolean FALSE
對於這個引數，我使用alter sessison在session級做了改動。
alter session set "_optimizer_skip_scan_enabled"=true;
然後檢視執行計劃。效率極大的提高了。
SQL> @plan
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 3 (34)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 2 (50)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 2 (50)| 00:00:01 | KEY | KEY |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 1 (0)| 00:00:01 | KEY | KEY |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
"OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A) AND TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A)))
6 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND
"OFFER_INSTANCE"=TO_NUMBER(:A) AND "ITEM_ID"=TO_NUMBER(:A))
filter("OFFER_INSTANCE"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "CUSTOMER_ID"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
為什麼skip scan效率這麼高，但是使用隱含引數禁用了它呢。
產品部門的解釋是對於skip scan在大多數的場景中，效率不是很理想，基本跟index full scan的效果一樣，所以從最佳化器內部使用隱含引數禁用，就使用了range scan.
所以這個問題的處理就比較糾結，想得到立竿見影的效果吧，使用index_ss不起作用，做全域性變更吧，這樣可能會影響其它的sql執行。使用alter session處理，在程式中實現又不現實。
最後使用另外一個hint解決上面的顧慮。opt_param，這個hint是在10gR2之後引進的，要解決的問題就是可以避免系統級的db引數變更。
嘗試的hint格式如下。
SELECT /*+opt_param('_optimizer_skip_scan_enabled',true)*/ ....
但是執行計劃中缺還是走了range scan。資源消耗跟沒加hint一個樣。
最後發現對於這個hint需要寫為：
最後使用另外一個hint解決上面的顧慮。opt_param，這個hint是在10gR2之後引進的，要解決的問題就是可以避免系統級的db引數變更。
嘗試的hint格式如下。
SELECT /*+opt_param('_optimizer_skip_scan_enabled',true)*/ ....
但是執行計劃中缺還是走了range scan。資源消耗跟沒加hint一個樣。
最後發現對於這個hint需要寫為：
SELECT /*+opt_param('_optimizer_skip_scan_enabled','true')*/ ....
這樣就能夠達到預期的目標了。從0.5秒到0.01秒，絕對是效能的極大提升。
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 6 (67)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 3 (67)| 00:00:01 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
可能這個問題到此就告一段落了，我在得到了一個初步的結論之後和開發部門進行協調，他們也試圖從業務上進行簡化。
最後他們把糾結的cycle_month和cycle_year的拼接去除了。改為在程式中處理。
與其說是改進不是直接說是簡化。
SELECT
LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a
AND CYCLE_YEAR=:a
AND CYCLE_MONTH=:a
這條sql語句直觀來看肯定是走唯一性掃描，但是效果有多好呢。可以看看幾個指標，都已經達到了最低。
Plan hash value: 404442430
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CYCLE_MONTH"=TO_NUMBER(:A) AND "CYCLE_YEAR"=TO_NUMBER(:A) AND
"CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
這樣就能夠達到預期的目標了。從0.5秒到0.01秒，絕對是效能的極大提升。
Plan hash value: 387232563
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 6 (67)| 00:00:01 | | |
| 1 | PARTITION RANGE ITERATOR | | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 3 (67)| 00:00:01 | 13 | 25 |
|* 3 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 3 (67)| 00:00:01 | 13 | 25 |
| 4 | SORT AGGREGATE | | 1 | 34 | | | | |
| 5 | PARTITION RANGE ITERATOR | | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
|* 6 | INDEX SKIP SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | 34 | 3 (67)| 00:00:01 | 13 | 25 |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131 AND
TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')= (SELECT
MAX(TO_CHAR("CYCLE_YEAR",'9999')||TO_CHAR("CYCLE_MONTH",'09')) FROM "PRDUSG3O"." CRDT_LMT_NOTIFICATION"
" CRDT_LMT_NOTIFICATION" WHERE "CYCLE_CODE"=25 AND "OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND
"CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131))
6 - access("CYCLE_CODE"=25 AND "CUSTOMER_ID"=10349451 AND "AGREEMENT_ID"=15997361 AND "OFFER_INSTANCE"=223499890 AND
"ITEM_ID"=15131)
filter("OFFER_INSTANCE"=223499890 AND "AGREEMENT_ID"=15997361 AND "CUSTOMER_ID"=10349451 AND "ITEM_ID"=15131)
可能這個問題到此就告一段落了，我在得到了一個初步的結論之後和開發部門進行協調，他們也試圖從業務上進行簡化。
最後他們把糾結的cycle_month和cycle_year的拼接去除了。改為在程式中處理。
與其說是改進不是直接說是簡化。
SELECT
LAST_THRESHOLD, CYCLE_MONTH, CYCLE_YEAR
FROM PM9_CRDT_LMT_NOTIFICATION
WHERE ITEM_ID = :a
AND AGREEMENT_ID = :a
AND CYCLE_CODE = :a
AND OFFER_INSTANCE = :a
AND CUSTOMER_ID = :a
AND CYCLE_YEAR=:a
AND CYCLE_MONTH=:a
這條sql語句直觀來看肯定是走唯一性掃描，但是效果有多好呢。可以看看幾個指標，都已經達到了最低。
Plan hash value: 404442430
-----------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 37 | 1 (0)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE | | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
| 2 | TABLE ACCESS BY LOCAL INDEX ROWID| CRDT_LMT_NOTIFICATION | 1 | 37 | 1 (0)| 00:00:01 | KEY | KEY |
|* 3 | INDEX UNIQUE SCAN | CRDT_LMT_NOTIFICATION_PK | 1 | | 1 (0)| 00:00:01 | KEY | KEY |
-----------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("CYCLE_CODE"=TO_NUMBER(:A) AND "CYCLE_MONTH"=TO_NUMBER(:A) AND "CYCLE_YEAR"=TO_NUMBER(:A) AND
"CUSTOMER_ID"=TO_NUMBER(:A) AND "AGREEMENT_ID"=TO_NUMBER(:A) AND "OFFER_INSTANCE"=TO_NUMBER(:A) AND
"ITEM_ID"=TO_NUMBER(:A))
從開發得到的反饋是這個邏輯的修改也不復雜，最後他們決定使用簡化後的sql。
在協調部署之後。速度有了極大的提升。
處理的事務數有了近10倍的提升。從十萬事務到近百萬事務 ，處理的速度還是提升了很多。
以下是事務處理的一些反饋資料。可以看到效果還是很明顯的。
透過這個例項，我們可以看到業務最佳化還是最好的最佳化，從資料庫的角度來做最佳化，也需要考慮到影響範圍，儘量是影響和變更最低，效率最高。
在協調部署之後。速度有了極大的提升。
處理的事務數有了近10倍的提升。從十萬事務到近百萬事務 ，處理的速度還是提升了很多。
以下是事務處理的一些反饋資料。可以看到效果還是很明顯的。
|
TIME |
COUNT |
|
20141212 00 |
119844 |
|
20141212 01 |
57357 |
|
20141212 02 |
23153 |
|
20141212 03 |
20610 |
|
20141212 04 |
111148 |
|
20141212 05 |
102540 |
|
20141212 06 |
59834 |
|
20141212 07 |
213985 |
|
20141212 08 |
69733 |
|
20141212 09 |
137163 |
|
20141212 10 |
163106 |
|
20141212 11 |
87091 |
|
20141212 12 |
89880 |
|
20141212 13 |
841172 |
|
20141212 14 |
960209 |
|
20141212 15 |
948309 |
|
20141212 16 |
899030 |
|
20141212 17 |
870231 |
|
20141212 18 |
953362 |
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25723371/viewspace-1382227/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 關於索引掃描的極速調優實戰（第一篇)索引
- 關於按鍵掃描程式的終極討論
- mysql索引覆蓋掃描優化MySql索引優化
- 索引全掃描和索引快速全掃描的區別索引
- Oracle中存取資料掃描Table及索引的方式(全表掃描，索引掃描等)Oracle索引
- Oracle優化－索引原理[注意索引跳躍式掃描!Oracle優化索引
- 【Oracle】 索引的掃描方式Oracle索引
- MySQL中的全表掃描和索引樹掃描MySql索引
- 【MySQL】全索引掃描的bugMySql索引
- oracle 全表掃描,索引範圍掃描與塊的理解Oracle索引
- 解讀Oracle 索引掃描Oracle索引
- 走索引掃描的慢查詢索引
- 有索引卻走全表掃描的實驗分析索引
- 使用索引快速全掃描(Index FFS)避免全表掃描的若干場景索引Index
- SQL Server調優實戰 亂建聚集索引的後果SQLServer索引
- 生產環境sql語句調優實戰第二篇SQL
- (轉)索引掃描還是全表掃描（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索引
- DAST 黑盒漏洞掃描器 第二篇：規則篇AST
- stopkey對索引掃描的影響測試TopK索引
- MySQL——索引優化實戰MySql索引優化
- 實戰 nginx 調優Nginx
- 效能調優實戰
- Hive調優實戰Hive
- 全表掃描的cost 與 索引掃描Cost的比較 – 無直方圖（10.1.0.3以後）索引直方圖
- 京東掃描平臺EOS—JS掃描落地與實踐JS
- MySQL調優篇 | SQL調優實戰（5）MySql
- 索引唯一性掃描(INDEX UNIQUE SCAN)索引Index
- mysql下建立索引讓其index全掃描MySql索引Index
- win10系統mcafee實時掃描怎麼關閉_win10系統mcafee實時掃描如何關閉Win10
- oracle優化：避免全表掃描Oracle優化
- 關於Hadoop調優Hadoop