生產環境sql語句調優實戰第五篇

今天在生產環境中發現一條sql語句儘管走了主鍵索引，但是查詢還是很慢。
sql語句類似下面的形式：
SELECT /*+ index (bl1_cyc_payer_pop BL1_CYC_PAYER_POP_PK) */
 T_TAX.BA_NO,
 T_TAX.TOTAL_TAX_AMT,
 T_TAX.TAX_RELATION,
 T_TAX_ITEM.TAX_ITEM_SEQ_NO,
 T_TAX_ITEM.TAX_SEQ_NO,
 T_TAX_ITEM.TAX_AUTHORITY,
 T_TAX_ITEM.TAX_TYPE,
 T_TAX_ITEM.TAX_RATE,
 T_TAX_ITEM.TAX_AMOUNT,
 T_TAX_ITEM.TAXABLE_AMOUNT,
 .......
  FROM T_TAX, T_TAX_ITEM, T_DOCUMENT, T_CYC_PAYER_POP --這幾張都是大表，少則500萬左右，多則1000多萬。
 WHERE T_TAX.TAX_ITEM_PERIOD_KEY = T_TAX_ITEM.PERIOD_KEY
   AND T_TAX.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
   AND T_TAX.BA_NO = T_CYC_PAYER_POP.BA_NO
   AND T_TAX.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
   AND T_TAX.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
   AND T_TAX.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
   AND T_TAX_ITEM.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
   AND T_TAX_ITEM.TAX_SEQ_NO = T_TAX.TAX_SEQ_NO
   AND T_DOCUMENT.PERIOD_KEY = T_CYC_PAYER_POP.PERIOD_KEY
   AND T_DOCUMENT.CUSTOMER_KEY = T_CYC_PAYER_POP.CUSTOMER_KEY
   AND T_DOCUMENT.BA_NO = T_CYC_PAYER_POP.BA_NO
   AND T_DOCUMENT.CYCLE_SEQ_NO = T_CYC_PAYER_POP.CYCLE_SEQ_NO
   AND T_DOCUMENT.CYCLE_SEQ_RUN = T_CYC_PAYER_POP.CYCLE_SEQ_RUN
   AND T_DOCUMENT.DOC_PRODUCE_IND IN ('Y', 'E')
   AND T_CYC_PAYER_POP.CUSTOMER_KEY = 78
   AND T_CYC_PAYER_POP.PERIOD_KEY = 55
   AND T_CYC_PAYER_POP.QA_GROUP = 3
   AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925

檢視該sql的執行計劃
發現有嚴重的io問題，瓶頸就在於使用的primary key對應的index

Id		Operation	Name	Estimated Rows	Cost	Active Period  (56s)	Execs	Rows	Memory	Temp	IO Requests	CPU Activity	Wait Activity
.	0	SELECT STATEMENT	.	.	.	. . .	1	67	.	.		.	.
.	1	. NESTED LOOPS	.	.	.	. . .	1	67	.	.		.	.
.	2	.. NESTED LOOPS	.	1	2447	. . .	1	67	.	.		.	.
.	3	... NESTED LOOPS	.	1	2446	. . .	1	67	.	.		.	.
.	4	.... NESTED LOOPS	.	1	2445	. . .	1	9	.	.		.	.
.	5	.....PARTITION RANGE SINGLE	.	1	2444	. . .	1	9	.	.		.	.
.	6	...... TABLE ACCESS BY LOCAL INDEX ROWID	CYC_PAYER_POP	1	2444	. . .	1	9	.	.	11 (<0.1%)	.	.
->	7	.......INDEX FULL SCAN	CYC_PAYER_POP_PK	1	2444	. .	1	793	.	.	. 23172 (95%)	. 100%	. 100%

這個問題很值得深究，完全可以使用如下的方式來驗證。我嘗試使用pk的Hint，另外不加任何hint，看錶查詢的時候會不會使用index
--&gt使用hint強制走主鍵查詢
Plan hash value: 3105767292
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                 | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                      |     1 |    12 |  2501   (1)| 00:00:31 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                      |     1 |    12 |  2501   (1)| 00:00:31 |   171 |   171 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP      |     1 |    12 |  2501   (1)| 00:00:31 |   171 |   171 |
|*  3 |    INDEX FULL SCAN                 | T_CYC_PAYER_POP_PK   |   541 |       |  2444   (1)| 00:00:30 |   171 |   171 |
---------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T_CYC_PAYER_POP"."QA_GROUP"=3)
   3 - access("T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55 AND
              "T_CYC_PAYER_POP"."CUSTOMER_KEY"=78)
       filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925 AND
              "T_CYC_PAYER_POP"."PERIOD_KEY"=55)
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      31395  consistent gets
          0  physical reads
          0  redo size
        910  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed

--&gt來看看不使用hint之後，發生了什麼
select  T_CYC_PAYER_POP.CUSTOMER_KEY,
        T_CYC_PAYER_POP.PERIOD_KEY,
        T_CYC_PAYER_POP.QA_GROUP ,
    T_CYC_PAYER_POP.CYCLE_SEQ_NO 
    from T_CYC_PAYER_POP where  T_CYC_PAYER_POP.CUSTOMER_KEY = 78
   AND T_CYC_PAYER_POP.PERIOD_KEY = 55
   AND T_CYC_PAYER_POP.QA_GROUP = 3
   AND T_CYC_PAYER_POP.CYCLE_SEQ_NO = 2925 
    /

Execution Plan
----------------------------------------------------------
Plan hash value: 23637115
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name                  | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                       |     1 |    12 |     2   (0)| 00:00:01 |       |       |
|   1 |  PARTITION RANGE SINGLE            |                       |     1 |    12 |     2   (0)| 00:00:01 |   171 |   171 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| T_CYC_PAYER_POP     |     1 |    12 |     2   (0)| 00:00:01 |   171 |   171 |
|*  3 |    INDEX RANGE SCAN                | T_CYC_PAYER_POP_5IX |   535 |       |     1   (0)| 00:00:01 |   171 |   171 |
----------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T_CYC_PAYER_POP"."CUSTOMER_KEY"=78 AND "T_CYC_PAYER_POP"."PERIOD_KEY"=55)
   3 - access("T_CYC_PAYER_POP"."QA_GROUP"=3 AND "T_CYC_PAYER_POP"."CYCLE_SEQ_NO"=2925)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
        951  consistent gets
        644  physical reads
         80  redo size
        910  bytes sent via SQL*Net to client
        520  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          8  rows processed
啟用了index range scan,而且從執行計劃和統計資訊來看，明顯要比全索引掃描效率高得多。
可以看到使用index range scan之後，先查詢了索引列的資訊，然後無法走索引過濾了其他的條件。根據目前的資料情況，這個效率要比全索引效率還高的多。


以下是做了hint的改動之後，統計資訊的情況，可以看到明顯的改善。對於這個Hint的細節需要和客戶做更多的確認，畢竟對於調優不能越調越差，穩定和高效才是關鍵。
Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
       1178  consistent gets
        756  physical reads
          0  redo size
       3229  bytes sent via SQL*Net to client
        553  bytes received via SQL*Net from client
          5  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
         58  rows processed