生产环境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 (	.	.
->	7	.......INDEX FULL SCAN	CYC_PAYER_POP_PK	1	2444	. .	1	793	.	.	. 23172 (95%)	. 100%	. 100%

这个问题很值得深究，完全可以使用如下的方式来验证。我尝试使用pk的Hint，另外不加任何hint，看表查询的时候会不会使用index
-->使用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

-->来看看不使用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