關於索引掃描的極速調優實戰（第二篇)

在上一篇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.